title: XSLT combine multiple nodes into single node
body: <pre><code>&lt;RowSet&gt;
   &lt;Row&gt;
    &lt;Location_Long_Desc&gt;Sydney Office&lt;/Location_Long_Desc&gt;
    &lt;Location_Code&gt;SYDNEY&lt;/Location_Code&gt;
    &lt;Daypart_Long_Desc&gt;Peak Night&lt;/Daypart_Long_Desc&gt;
    &lt;Daypart_Code&gt;PEANIG&lt;/Daypart_Code&gt;
    &lt;W_20050703_Dlr&gt;30849.3&lt;/W_20050703_Dlr&gt;
    &lt;W_20050703_Spots&gt;9&lt;/W_20050703_Spots&gt;
    &lt;W_20050710_Dlr&gt;16.35&lt;/W_20050710_Dlr&gt;
    &lt;W_20050710_Spots&gt;19&lt;/W_20050710_Spots&gt;
   &lt;/Row&gt;
  &lt;/RowSet&gt;
  </code></pre>
  
  <p>So, I have this XML now what I need to convert the W_ nodes into a new single node. Using this XSL</p>
  
  <pre><code>&lt;?xml version="1.0"?&gt;
    &lt;xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt;
    &lt;xsl:output method="xml" indent="yes"/&gt;
    &lt;xsl:template match="node()"&gt;
      &lt;xsl:copy&gt;
        &lt;xsl:apply-templates select="node()"/&gt;
      &lt;/xsl:copy&gt;
    &lt;/xsl:template&gt;
    &lt;xsl:template match="*"&gt;
      &lt;xsl:variable name="tmp" select="local-name()"/&gt;
      &lt;xsl:choose&gt;
        &lt;xsl:when test="starts-with($tmp, 'W_') and ends-with($tmp, '_Dlr')"&gt;
      &lt;xsl:if test="text() != ''"&gt;
            &lt;xsl:element name="Expenditure"&gt;
              &lt;xsl:element name="Period"&gt;
                &lt;xsl:value-of select="substring($tmp,3,8)"/&gt;
              &lt;/xsl:element&gt;
              &lt;xsl:element name="Value"&gt;
                &lt;xsl:apply-templates select="node()"/&gt;
              &lt;/xsl:element&gt;
              &lt;xsl:element name="Spots"&gt;
                &lt;xsl:apply-templates select="//RowSet/Row/W_20050703_Spots/text()"/&gt;
              &lt;/xsl:element&gt;
            &lt;/xsl:element&gt;
      &lt;/xsl:if&gt;
      &lt;/xsl:when&gt;
      &lt;xsl:otherwise&gt;
          &lt;xsl:element name="{$tmp}"&gt;
            &lt;xsl:apply-templates select="node()"/&gt;
          &lt;/xsl:element&gt;
        &lt;/xsl:otherwise&gt;
      &lt;/xsl:choose&gt;
    &lt;/xsl:template&gt;
  &lt;/xsl:stylesheet&gt;
  </code></pre>
  
  <p>I can almost get there but I have a couple of issues. </p>
  
  <ol>
  <li>I need to combine the W_????????<em>Dlr and W</em>????????_Spots into a individual nodes but </li>
  <li>I can't work out how to use a variable in an xpath statement, or maybe I'm miles off where I should be.</li>
  </ol>
  
  <p>Again, I'm still getting to grips with it all, so please be gentle ;-)</p>
  
  <p>TIA</p>
  
  <p>EDIT: 02/12/2010 12:00</p>
  
  <p>Ok,</p>
  
  <p>Just a little further question, depending on a database level switch (which I forgot all about), the Spots node may or may not exist.</p>
  
  <p>So I still need to output  though it shouldn't to the following-sibling call where the following-sibling isn't a valid _spots node.</p>
  
  <p>Example:</p>
  
  <pre><code>&lt;RowSet&gt;
   &lt;Row&gt;
    &lt;Location_Long_Desc&gt;Sydney Office&lt;/Location_Long_Desc&gt;
    &lt;Location_Code&gt;SYDNEY&lt;/Location_Code&gt;
    &lt;Daypart_Long_Desc&gt;Peak Night&lt;/Daypart_Long_Desc&gt;
    &lt;Daypart_Code&gt;PEANIG&lt;/Daypart_Code&gt;
    &lt;W_20050703_Dlr&gt;30849.3&lt;/W_20050703_Dlr&gt;
    &lt;W_20050710_Dlr&gt;16.35&lt;/W_20050710_Dlr&gt;
   &lt;/Row&gt;
  &lt;/RowSet&gt;
  </code></pre>
  
  <p>Just so you know, I'm calling all this via an Oracle package</p>
  
  <pre><code>-- get the query context;
  v_qryctx := dbms_xmlgen.newcontext(in_sql_query);
  
  dbms_xmlgen.setnullhandling(v_qryctx, 2);
  dbms_xmlgen.setrowsettag(v_qryctx, 'RowSet');
  dbms_xmlgen.setrowtag(v_qryctx, 'Row');
  
  IF in_export_type = cnst_export_booking
  THEN
      dbms_xmlgen.setxslt(v_qryctx, v_booking_export_xsl);
  
  ELSIF in_export_type = cnst_export_expenditure
  THEN
      dbms_xmlgen.setxslt(v_qryctx, v_expenditure_export_xsl);
  
  END IF;
  </code></pre>