XSLT combines multiple node s into one node

Question

<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, I have this XML, now I need to convert the W_ nodes to the new single node. Using this 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_') and 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 almost get there, but I have a couple of questions.

• I need to combine W _ ???????? Dlr and W ???????? _ Spots in separate nodes, but
• I can’t figure out how to use the variable in the xpath statement, or maybe I'm a few miles from where I should be.

Again, I still understand this, so please be careful :-)

TIA

EDIT: 12/02/2010 12:00

Good,

Another question, depending on the database level switch (which I completely forgot about), the Spots node may or may not exist.

So I still need to output, although it should not have the next class call, where the next-brother is not a 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 to let you know, I call it all through the Oracle package

 -- 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; 

Answer 1

This conversion is :

 <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_') and substring-after(substring-after(name(),'_'),'_')='Dlr' and 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_') and substring-after(substring-after(name(),'_'),'_')='Spots' ] "/> </Expenditure> </xsl:template> <xsl:template mode="extract" match= "*[starts-with(name(),'W_') and substring-after(substring-after(name(),'_'),'_')='Spots' ] "> <Spots><xsl:value-of select="."/></Spots> </xsl:template> <xsl:template match= "*[starts-with(name(),'W_') and substring-after(substring-after(name(),'_'),'_')='Spots' ] "/> </xsl:stylesheet> 

when applied to the provided XML source 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> 

creates the desired, 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 in the second provided XML document requested in the OP update:

 <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 the desired, correct result (the <Spot> element is generated if an immediate sibling W_nnnnnnnn_Spots ):

 <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> 

Please note :

• Using an identification rule to copy any node "as-is".

• Overriding the identity template for W_nnnnnnnn_Dlr elements W_nnnnnnnn_Dlr .

• Overriding the identity template with an empty template matching W_nnnnnnnn_Spots elements.

• Using the standard XPath functions : name() , starts-with() and substring-after()

• The ends-with() function is only available in XPath 2.0 (XSLT 2.0) and isn't used in this XSLT 1.0 solution.

Answer 2

I would start with a template like

 <xsl:template match="Row/*[starts-with(name(), 'W_') and ends-with(name(), '_Dlr')]"> 

which should more closely match the element you want to match. Regarding the selection of the adjacent element <W_${DATE}_Spots> sibling ... why not use the following-sibling XPath axis with the corresponding row?

 <xsl:template match="Row/*[starts-with(local-name(), 'W_') and ends-with(local-name(), '_Dlr')]"> <xsl:variable name="date" select="substring(local_name(),3,8)"/> ... <xsl:apply-templates select="following-sibling::*[local-name() == concat('W_', concat($date, '_Spots'))]"/> ... </xsl:template> <xsl:template match="Row/*[starts-with(local-name(), 'W_') and ends-with(local-name(), '_Spots')]"> <xsl:variable name="date" select="substring(local_name(),3,8)"/> ... </xsl:template> 

By the way, this looks like an endless wait loop:

 <xsl:template match="*"> ... <xsl:apply-templates select="node()"/> ... </xsl:template> 

I am sure there are several errors in my answer, but in any case this should be useful to some extent.

Answer 3

Here is an answer similar to Demetrius. I already wrote this, so I thought that I would go further and publish it ...

XSLT (2.0)

 <?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output indent="yes"/> <xsl:strip-space elements="*"/> <xsl:template match="node()|@*"> <xsl:choose> <xsl:when test="starts-with(local-name(), 'W_') and ends-with(local-name(), '_Dlr')"> <xsl:variable name="period" select="substring(local-name(),3,8)"/> <Expenditure> <Period><xsl:value-of select="$period"/></Period> <Value><xsl:apply-templates/></Value> <Spots><xsl:value-of select="following-sibling::*[starts-with(local-name(), 'W_') and ends-with(local-name(),concat($period,'_Spots'))]"/></Spots> </Expenditure> </xsl:when> <xsl:when test="ends-with(local-name(), '_Spots')"/> <xsl:otherwise> <xsl:copy> <xsl:apply-templates select="node()|@*"/> </xsl:copy> </xsl:otherwise> </xsl:choose> </xsl:template> </xsl:stylesheet> 

Also, if you are using XSLT 2.0 (I assumed that since you used ends-with() ), you can use tokenize() to capture fragments of the name.

Example:

 <xsl:variable name="period" select="tokenize(local-name(),'_')[2]"/>