Articles in this section

Transform nested XML using XSLT

  • Updated
Follow

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:

denormalized.png

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.

xml-with-xsl.png

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.

mceclip0.png

Transform multiple XML files using XSLT

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

 

 

Related articles

Comments

0 comments

Please sign in to leave a comment.