Group XML Elements based on element values and attributes using XSLT 2.0

Question

I would like to iterate through XML elemets grouping the data by a common element value. The input XML structure is:

<?xml version="1.0" encoding="utf-8"?>
<Data>
<output outputType="CORRECTION">
    <row nodeName="ORDER_HEADER" tableName="ORDER_HEADER">
        <column columnName="ORDER_NUMBER">00001</column>
        <column columnName="Country">England</column>
        <column columnName="City">London</column>
    </row>
    <row nodeName="ORDER_HEADER" tableName="ORDER_HEADER">
        <column columnName="ORDER_NUMBER">00002</column>
        <column columnName="Country">England</column>
        <column columnName="City">Birmingham</column>
    </row>
    <row nodeName="ORDER_DETAIL" tableName="ORDER_DETAIL">
        <column columnName="ORDER_NUMBER">00001</column>
        <column columnName="Book">Gone with the wind</column>
        <column columnName="Qty">2</column>
    </row>
    <row nodeName="ORDER_DETAIL" tableName="ORDER_DETAIL">
        <column columnName="ORDER_NUMBER">00001</column>
        <column columnName="Book">Pride and Prejudice</column>
        <column columnName="Qty">3</column>
    </row>
    <row nodeName="ORDER_DETAIL" tableName="ORDER_DETAIL">
        <column columnName="ORDER_NUMBER">00002</column>
        <column columnName="Book">Jane Eyre</column>
        <column columnName="Qty">1</column>
    </row>
</output>
</Data>

I am trying to create the following output:

<Transmission xmlns:xs="http://www.w3.org/2001/XMLSchema"
          xmlns="http://www.w3.org/1999/xhtml">
    <OrderNumber>00001</OrderNumber>
    <Country>England</Country>
    <City>London</CITY>
    <Book>Gone with the wind</Book>
    <QTY>2</QTY>
    <Book>Pride and Prejudice</Book>
    <QTY>3</QTY>
    <OrderNumber>00002</OrderNumber>
    <Country>England</Country>
    <City>Birmingham</CITY>
    <Book>Jane Eyre</Book>
    <QTY>1</QTY>
</Transmission>

Here is the XSL I have so far:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="2.0"     xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns="http://www.w3.org/1999/xhtml">
<xsl:output method="xml"  indent="yes" omit-xml-declaration="yes"/>
<xsl:template match="/Data/output[@outputType='CORRECTION']">
    <Transmission>
        <xsl:for-each select="row[@tableName='ORDER_HEADER']">
            <OrderNumber>
                <xsl:value-of select="column[@columnName='ORDER_NUMBER']" />
            </OrderNumber>
            <Country>
                <xsl:value-of select="column[@columnName='Country']" />
            </Country>
            <City>
                <xsl:value-of select="column[@columnName='City']" />
            </City>
        </xsl:for-each>
    </Transmission>
</xsl:template>
</xsl:stylesheet>

I can output the details from each order header but can't see how to select and group the correct order details. Any help would be much appreciated.

Thanks

Tim C · Accepted Answer · 2015-07-09 21:06:27Z

You can use a key to look up the order details (which works in both XSLT 1.0 and XSLT 2.0)

<xsl:key name="detail" match="row[@nodeName='ORDER_DETAIL']" use="column[@columnName='ORDER_NUMBER']" />

Then, to get the order details for a given order number, you can use the key like so:

<xsl:for-each select="key('detail', column[@columnName='ORDER_NUMBER'])">

Try this XSLT

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml"  indent="yes" omit-xml-declaration="yes"/>
<xsl:key name="detail" match="row[@nodeName='ORDER_DETAIL']" use="column[@columnName='ORDER_NUMBER']" />
<xsl:template match="/Data/output[@outputType='CORRECTION']">
    <Transmission>
        <xsl:for-each select="row[@tableName='ORDER_HEADER']">
            <xsl:variable name="orderNumber" select="column[@columnName='ORDER_NUMBER']" />
            <OrderNumber>
                <xsl:value-of select="$orderNumber" />
            </OrderNumber>
            <Country>
                <xsl:value-of select="column[@columnName='Country']" />
            </Country>
            <City>
                <xsl:value-of select="column[@columnName='City']" />
            </City>
            <xsl:apply-templates select="key('detail', $orderNumber)" />
        </xsl:for-each>
    </Transmission>
</xsl:template>

<xsl:template match="row">
    <Book>
        <xsl:value-of select="column[@columnName='Book']" />
    </Book>
    <QTY>
        <xsl:value-of select="column[@columnName='Qty']" />
    </QTY>
</xsl:template>
</xsl:stylesheet>

Note, I switched to use xsl:apply-templates instead, just to cut-down on too much nested code. I also used a variable, to avoid some repetition of getting the order number.

As an aside, is that really the XML output you want? It would probably be more logical to wrap each order in a containing parent element like so, otherwise extracting the books for an order becomes a bit more hard work.

<Transmission>
 <Order>
   <OrderNumber>00001</OrderNumber>
   <Country>England</Country>
   <City>London</City>
   <Book>Gone with the wind</Book>
   <QTY>2</QTY>
   <Book>Pride and Prejudice</Book>
   <QTY>3</QTY>
 </Order>
 <Order>
   <OrderNumber>00002</OrderNumber>
   <Country>England</Country>
   <City>Birmingham</City>
   <Book>Jane Eyre</Book>
   <QTY>1</QTY>
 </Order>
</Transmission>

Perfect! Thanks very much for a clear and concise solution. This has helped enormously.

Collectives™ on Stack Overflow

Group XML Elements based on element values and attributes using XSLT 2.0

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related