XSLT combine multiple nodes into single node -

<rowset>  <row>   <location_long_desc>sydney office</location_long_desc>   <location_code>sydney</location_code>   <daypart_long_desc>peak night</daypart_long_desc>   <daypart_code>peanig</daypart_code>   <w_20050703_dlr>30849.3</w_20050703_dlr>   <w_20050703_spots>9</w_20050703_spots>   <w_20050710_dlr>16.35</w_20050710_dlr>   <w_20050710_spots>19</w_20050710_spots>  </row> </rowset> 

so, have xml need convert w_ nodes new single node. using xsl

<?xml version="1.0"?>   <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/xsl/transform">   <xsl:output method="xml" indent="yes"/>   <xsl:template match="node()">     <xsl:copy>       <xsl:apply-templates select="node()"/>     </xsl:copy>   </xsl:template>   <xsl:template match="*">     <xsl:variable name="tmp" select="local-name()"/>     <xsl:choose>       <xsl:when test="starts-with($tmp, 'w_') , ends-with($tmp, '_dlr')">     <xsl:if test="text() != ''">           <xsl:element name="expenditure">             <xsl:element name="period">               <xsl:value-of select="substring($tmp,3,8)"/>             </xsl:element>             <xsl:element name="value">               <xsl:apply-templates select="node()"/>             </xsl:element>             <xsl:element name="spots">               <xsl:apply-templates select="//rowset/row/w_20050703_spots/text()"/>             </xsl:element>           </xsl:element>     </xsl:if>     </xsl:when>     <xsl:otherwise>         <xsl:element name="{$tmp}">           <xsl:apply-templates select="node()"/>         </xsl:element>       </xsl:otherwise>     </xsl:choose>   </xsl:template> </xsl:stylesheet> 

i can there have couple of issues.

1. i need combine w_????????dlr , w????????_spots individual nodes
2. i can't work out how use variable in xpath statement, or maybe i'm miles off should be.

again, i'm still getting grips all, please gentle ;-)

tia

edit: 02/12/2010 12:00

ok,

just little further question, depending on database level switch (which forgot about), spots node may or may not exist.

so still need output though shouldn't following-sibling call following-sibling isn't valid _spots node.

example:

<rowset>  <row>   <location_long_desc>sydney office</location_long_desc>   <location_code>sydney</location_code>   <daypart_long_desc>peak night</daypart_long_desc>   <daypart_code>peanig</daypart_code>   <w_20050703_dlr>30849.3</w_20050703_dlr>   <w_20050710_dlr>16.35</w_20050710_dlr>  </row> </rowset> 

just know, i'm calling via oracle package

-- 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     dbms_xmlgen.setxslt(v_qryctx, v_booking_export_xsl);  elsif in_export_type = cnst_export_expenditure     dbms_xmlgen.setxslt(v_qryctx, v_expenditure_export_xsl);  end if; 

this transformation:

<xsl:stylesheet version="1.0"  xmlns:xsl="http://www.w3.org/1999/xsl/transform">  <xsl:output omit-xml-declaration="yes" indent="yes"/>  <xsl:strip-space elements="*"/>   <xsl:template match="node()|@*">      <xsl:copy>        <xsl:apply-templates select="node()|@*"/>      </xsl:copy>  </xsl:template>   <xsl:template match=  "*[starts-with(name(),'w_')   ,    substring-after(substring-after(name(),'_'),'_')='dlr'   ,    text()    ]">   <expenditure>     <period>       <xsl:value-of select="substring(name(),3,8)"/>     </period>     <value>       <xsl:apply-templates/>     </value>       <xsl:apply-templates mode="extract" select=       "following-sibling::*[1]         [starts-with(name(),'w_')        ,         substring-after(substring-after(name(),'_'),'_')='spots'          ]        "/>   </expenditure>  </xsl:template>   <xsl:template mode="extract" match=   "*[starts-with(name(),'w_')   ,    substring-after(substring-after(name(),'_'),'_')='spots'     ]   ">    <spots><xsl:value-of select="."/></spots>   </xsl:template>    <xsl:template match=   "*[starts-with(name(),'w_')   ,    substring-after(substring-after(name(),'_'),'_')='spots'     ]   "/> </xsl:stylesheet> 

when applied on provided source xml document:

<rowset>  <row>   <location_long_desc>sydney office</location_long_desc>   <location_code>sydney</location_code>   <daypart_long_desc>peak night</daypart_long_desc>   <daypart_code>peanig</daypart_code>   <w_20050703_dlr>30849.3</w_20050703_dlr>   <w_20050703_spots>9</w_20050703_spots>   <w_20050710_dlr>16.35</w_20050710_dlr>   <w_20050710_spots>19</w_20050710_spots>  </row> </rowset> 

produces wanted, correct result:

<rowset>    <row>       <location_long_desc>sydney office</location_long_desc>       <location_code>sydney</location_code>       <daypart_long_desc>peak night</daypart_long_desc>       <daypart_code>peanig</daypart_code>       <expenditure>          <period>20050703</period>          <value>30849.3</value>          <spots>9</spots>       </expenditure>       <expenditure>          <period>20050710</period>          <value>16.35</value>          <spots>19</spots>       </expenditure>    </row> </rowset> 

when applied on second provided xml document, requested in update op:

<rowset>  <row>   <location_long_desc>sydney office</location_long_desc>   <location_code>sydney</location_code>   <daypart_long_desc>peak night</daypart_long_desc>   <daypart_code>peanig</daypart_code>   <w_20050703_dlr>30849.3</w_20050703_dlr>   <w_20050710_dlr>16.35</w_20050710_dlr>   <w_20050710_spots>19</w_20050710_spots>  </row> </rowset> 

again wanted, correct result (no <spot> element generated if immediate sibling isn't w_nnnnnnnn_spots) produced:

<rowset>    <row>       <location_long_desc>sydney office</location_long_desc>       <location_code>sydney</location_code>       <daypart_long_desc>peak night</daypart_long_desc>       <daypart_code>peanig</daypart_code>       <expenditure>          <period>20050703</period>          <value>30849.3</value>       </expenditure>       <expenditure>          <period>20050710</period>          <value>16.35</value>          <spots>19</spots>       </expenditure>    </row> </rowset> 

do note:

1. the use of identity rule copy node "as-is".

2. the overriding of identity template w_nnnnnnnn_dlr elements.

3. the overriding of identity template empty template matching w_nnnnnnnn_spots elements.

4. the use of standard xpath functions: name(), starts-with() , substring-after()

5. the function ends-with() available in xpath 2.0 (xslt 2.0) , isn't used in xslt 1.0 solution.