Overview
In the Etlworks Integrator, you can read and write XML documents by using XSLT.
Use XSLT when reading XML documents
You have received a source XML from a web service and load it into a database. 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:
Process
In the Etlworks Integrator, all you need to do to denormalize a nested data object and transform it into a flat table is to use an XML Format and an XSL stylesheet by following the steps below:
Step 1. Create an XSL stylesheet that transforms a nested-source XML, like the one above, into a denormalized flat XML, like the one 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.
Notice the parameters below:
Row Tag
:ROW
, in our case.XPath Query
orXSL Style Sheet or Preprocessor
: XSL stylesheet above, in our case.
Step 3. Use the Format created in Step 2 as your source (FROM
) Format.
Use XSLT when writing XML documents
You need to create an XML document like the one below and your source is a flat-file, or data in one or comes from multiple database tables.
<Transaction>
<Invoice>AR</Invoice>
<InvoiceNo>654058</InvoiceNo>
<InvoiceType>RI</InvoiceType>
<AccountNo>1601001223</AccountNo>
<InvoiceDate>20160429</InvoiceDate>
<BranchId>64</BranchId>
<Department>15</Department>
<FileRef>F21557</FileRef>
<InvoiceTotal>13469.02</InvoiceTotal>
<InvoiceLine>
<BillingCode>G37T</BillingCode>
<TransactionDesc>NEX6636-RFC</TransactionDesc>
<Amount>642.84</Amount>
<Vat>90</Vat>
</InvoiceLine>
<InvoiceLine>
<BillingCode>G37T</BillingCode>
<TransactionDesc>NEX308707-RFC</TransactionDesc>
<Amount>60.97</Amount>
<Vat>8.53</Vat>
</InvoiceLine>
<InvoiceLine>
<BillingCode>G37T</BillingCode>
<TransactionDesc>NEX307851-RFC</TransactionDesc>
<Amount>233.52</Amount>
<Vat>32.69</Vat>
</InvoiceLine>
</Transaction>
To simplify this example, we will not be explaining how to select data from the database or read a flat-file. Our source datasets will look like the one below:
To simplify, we will be using the same columns in the source as we will need in the destination:
Of course, if the columns are different, you can use any of the Mapping techniques available in the Etlworks Integrator by following the steps below:
Process
Step 1. Create a source Connection.
Step 2. Create a source Format if needed. This step is optional if we are going to read data from a database.
Step 3. Create a destination Connection.
Step 4. Create an XML Format for the destination. When creating a Format:
- Uncheck
XML Document
. - Set
Start Element Name
toTransaction
. - Set
Row Tag
toInvoiceLine
.
Step 5. Create a Flow where the source (FROM
) is a Connection, and (optionally) create a Format using the source created in Steps 1-2, and choices for the destination (TO
) to match the Connection and Format created in Steps 3-4.
Step 6. Execute the Flow and check the output. It should look like this:
<Transaction>
<InvoiceLine>
<Invoice>AR</Invoice>
<InvoiceNo>654058</InvoiceNo>
<InvoiceType>RI</InvoiceType>
<AccountNo>1601001223</AccountNo>
<InvoiceDate>20160429</InvoiceDate>
<BranchId>64</BranchId>
<Department>15</Department>
<FileRef>F21557</FileRef>
<InvoiceTotal>13469.02</InvoiceTotal>
<BillingCode>G37T</BillingCode>
<TransactionDesc>NEX6636-RFC</TransactionDesc>
<Amount>642.84</Amount>
<Vat>90.0</Vat>
</InvoiceLine>
<InvoiceLine>
<Invoice>AR</Invoice>
<InvoiceNo>654058</InvoiceNo>
<InvoiceType>RI</InvoiceType>
<AccountNo>1601001223</AccountNo>
<InvoiceDate>20160429</InvoiceDate>
<BranchId>64</BranchId>
<Department>15</Department>
<FileRef>F21557</FileRef>
<InvoiceTotal>13469.02</InvoiceTotal>
<BillingCode>G37T</BillingCode>
<TransactionDesc>NEX308707-RFC</TransactionDesc>
<Amount>60.97</Amount>
<Vat>8.53</Vat>
</InvoiceLine>
<InvoiceLine>
<Invoice>AR</Invoice>
<InvoiceNo>654058</InvoiceNo>
<InvoiceType>RI</InvoiceType>
<AccountNo>1601001223</AccountNo>
<InvoiceDate>20160429</InvoiceDate>
<BranchId>64</BranchId>
<Department>15</Department>
<FileRef>F21557</FileRef>
<InvoiceTotal>13469.02</InvoiceTotal>
<BillingCode>G37T</BillingCode>
<TransactionDesc>NEX307851-RFC</TransactionDesc>
<Amount>233.52</Amount>
<Vat>32.69</Vat>
</InvoiceLine>
</Transaction>
As you can see, we are almost there, but not quite. We still need to move the Invoice...
fields outside of the <InvoiceLine>
block. We will be using an XLT transformation to reformat the XML into the desired Format.
Step 7. Modify the XML Format created in Step 4 by adding the following XSL style sheet:
<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" omit-xml-declaration="yes"/>
<xsl:template match="/">
<xsl:variable name="var1_initial" select="."/>
<Transaction>
<Invoice><xsl:value-of select="Transaction/InvoiceLine/Invoice"/></Invoice>
<InvoiceNo><xsl:value-of select="Transaction/InvoiceLine/InvoiceNo"/></InvoiceNo>
<InvoiceType><xsl:value-of select="Transaction/InvoiceLine/InvoiceType"/></InvoiceType>
<AccountNo><xsl:value-of select="Transaction/InvoiceLine/AccountNo"/></AccountNo>
<InvoiceDate><xsl:value-of select="Transaction/InvoiceLine/InvoiceDate"/></InvoiceDate>
<BranchId><xsl:value-of select="Transaction/InvoiceLine/BranchId"/></BranchId>
<Department><xsl:value-of select="Transaction/InvoiceLine/Department"/></Department>
<FileRef><xsl:value-of select="Transaction/InvoiceLine/FileRef"/></FileRef>
<InvoiceTotal><xsl:value-of select="Transaction/InvoiceLine/InvoiceTotal"/></InvoiceTotal>
<xsl:for-each select="Transaction/InvoiceLine">
<xsl:variable name="var2_cur" select="."/>
<InvoiceLine>
<BillingCode>
<xsl:value-of select="BillingCode"/>
</BillingCode>
<TransactionDesc>
<xsl:value-of select="TransactionDesc"/>
</TransactionDesc>
<Amount>
<xsl:value-of select="number(Amount)"/>
</Amount>
<Vat>
<xsl:value-of select="number(Vat)"/>
</Vat>
</InvoiceLine>
</xsl:for-each>
</Transaction>
</xsl:template>
</xsl:stylesheet>
Step 8. Execute the Flow again. This time, the output will look like the following which is exactly what we need:
<Transaction>
<Invoice>AR</Invoice>
<InvoiceNo>654058</InvoiceNo>
<InvoiceType>RI</InvoiceType>
<AccountNo>1601001223</AccountNo>
<InvoiceDate>20160429</InvoiceDate>
<BranchId>64</BranchId>
<Department>15</Department>
<FileRef>F21557</FileRef>
<InvoiceTotal>13469.02</InvoiceTotal>
<InvoiceLine>
<BillingCode>G37T</BillingCode>
<TransactionDesc>NEX6636-RFC</TransactionDesc>
<Amount>642.84</Amount>
<Vat>90</Vat>
</InvoiceLine>
<InvoiceLine>
<BillingCode>G37T</BillingCode>
<TransactionDesc>NEX308707-RFC</TransactionDesc>
<Amount>60.97</Amount>
<Vat>8.53</Vat>
</InvoiceLine>
<InvoiceLine>
<BillingCode>G37T</BillingCode>
<TransactionDesc>NEX307851-RFC</TransactionDesc>
<Amount>233.52</Amount>
<Vat>32.69</Vat>
</InvoiceLine>
</Transaction>
Comments
0 comments
Please sign in to leave a comment.