When the Source (FROM
) is a nested XML object, you can use an XSLT to transform it to any desired Format.
Let's assume that 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 the Etlworks Integrator, all you need to do to accomplish this is to use an XML Format together with an XSL stylesheet.
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 the Etlworks Integrator, 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.
Comments
0 comments
Please sign in to leave a comment.