Transform nested XML using XSLT – Etlworks Support

Overview

In the ETL transformation, when the Source (FROM) is a nested XML object, you can use an XSLT to transform it to any desired Format.

Example

Let's assume you are receiving a source XML from a web service and loading it into a database. Similarly, let's assume that your source XML is a nested data object, like the one below:

<?xml version="1.0" encoding="UTF-8"?>
<GoodsReceivedIntoBond>
     <GoodsReceivedIntoBondHeader WarehouseNo="CTN 12" CustomerCode="Cust1" 
     DocumentNo="00123" DocumentDate="2017-02-08" NumberOfLine="3" DocumentXRef="12345">
          <GoodsReceivedIntoBondLines>
               <StockCode>B3B4</StockCode>
               <StockQuantity>60.0000</StockQuantity>
          </GoodsReceivedIntoBondLines>
          <GoodsReceivedIntoBondLines>
               <StockCode>B5GT</StockCode>
               <StockQuantity>60.0000</StockQuantity>
          </GoodsReceivedIntoBondLines>
          <GoodsReceivedIntoBondLines>
               <StockCode>B121W</StockCode>
               <StockQuantity>360.0000</StockQuantity>
          </GoodsReceivedIntoBondLines>
     </GoodsReceivedIntoBondHeader>
     <GoodsReceivedIntoBondHeader WarehouseNo="CTN 13" CustomerCode="Cust2" 
     DocumentNo="00456" DocumentDate="2017-02-08" NumberOfLine="2" DocumentXRef="6789">
          <GoodsReceivedIntoBondLines>
               <StockCode>B7C7</StockCode>
               <StockQuantity>16.0000</StockQuantity>
          </GoodsReceivedIntoBondLines>
          <GoodsReceivedIntoBondLines>
               <StockCode>B12345</StockCode>
               <StockQuantity>16.0000</StockQuantity>
          </GoodsReceivedIntoBondLines>
     </GoodsReceivedIntoBondHeader>
</GoodsReceivedIntoBond>

Most databases store data in flat tables with rows and columns. So, ideally, you would like to denormalize a nested data object, like the one above, and transform it into a flat table:

In Etlworks, all you need to do to accomplish this is to use an XML Format together with an XSL stylesheet.

Process

Step 1. Create an XSL stylesheet which transforms a nested-source XML, like the one above, into a denormalized flat XML, like what is shown below:

<?xml version="1.0" encoding="UTF-8"?>
<result>
    <ROW>
       <WarehouseNo>WN</WarehouseNo> 
       <CustomerCode>CC</CustomerCode> 
       <DocumentNo>DN</DocumentNo>
       <DocumentDate>DD</DocumentDate> 
       <NumberOfLine>NOL</NumberOfLine> 
       <DocumentXRef>XREF</DocumentXRef>
       <StockCode>SC</StockCode>
       <StockQuantity>SQ</StockQuantity>
    </ROW>
    <ROW>
       .....
    </ROW> 
</result>

For this solution, we will be using the following XSL stylesheet:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" exclude-result-prefixes="xs">
    <xsl:output method="xml" encoding="UTF-8" indent="yes"/>
    <xsl:template match="/">
        <xsl:variable name="var1_initial" select="."/>
        <result>
            <xsl:for-each select="GoodsReceivedIntoBond/GoodsReceivedIntoBondHeader">
                <xsl:variable name="var2_cur" select="."/>
                <xsl:for-each select="GoodsReceivedIntoBondLines">
                    <xsl:variable name="var3_cur" select="."/>
                    <ROW>
                        <WarehouseNo>
                            <xsl:value-of select="$var2_cur/@WarehouseNo"/>
                        </WarehouseNo>
                        <CustomerCode>
                            <xsl:value-of select="$var2_cur/@CustomerCode"/>
                        </CustomerCode>
                        <DocumentNo>
                            <xsl:value-of select="number($var2_cur/@DocumentNo)"/>
                        </DocumentNo>
                        <DocumentDate>
                            <xsl:value-of select="$var2_cur/@DocumentDate"/>
                        </DocumentDate>
                        <NumberOfLine>
                            <xsl:value-of select="number($var2_cur/@NumberOfLine)"/>
                        </NumberOfLine>
                        <DocumentXRef>
                            <xsl:value-of select="number($var2_cur/@DocumentXRef)"/>
                        </DocumentXRef>
                        <StockCode>
                            <xsl:value-of select="StockCode"/>
                        </StockCode>
                        <StockQuantity>
                            <xsl:value-of select="number(StockQuantity)"/>
                        </StockQuantity>
                    </ROW>
                </xsl:for-each>
            </xsl:for-each>
        </result>
    </xsl:template>
</xsl:stylesheet>

Note the <ROW>...</ROW> tag in the stylesheet.

Step 2. Create an XML Format in Etlworks, which uses the XSL stylesheet above.

Row Tag: ROW, in our case.
XPath Query or XSL Style Sheet or Preprocessor: XSL stylesheet above, in our case.

Step 3. Create source-to-destination Flow and use the Format created in Step 2 as your source (FROM) Format.

Using Source Query

Alternatively, instead of using XML format with XSL Style Sheet, you can enter the XSL in the Source Query. It will produce the same end result.

Transform multiple XML files using XSLT

Read how to create a flow to transform multiple XML files by applying the XSL style sheet.

Test an XSL Transformation in Explorer

You can use the Etlworks Explorer to quickly test an XSL transformation against an XML document and preview the results.

Step 1. Open Explorer.

Step 2. Select and expand the connection that contains the XML document.

Step 3. If necessary, assign the appropriate XML format to the connection.

Step 4. Click the Develop SQL (pencil) icon.

Step 5. Enter or paste the XSL transformation. The script must begin with <xsl:.

Step 6. Click the Execute SQL (>) icon.

Step 7. Review the transformation results displayed in the grid at the bottom of the screen.

Articles in this section