Articles in this section

See more

Use XSLT when reading and writing XML documents

  • Updated
Follow

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:

denormalized.png

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 or XSL Style Sheet or Preprocessor: XSL stylesheet above, in our case.

xml-with-xsl.png

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:

flat-dataset.png

To simplify, we will be using the same columns in the source as we will need in the destination:

columns.png

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:

  1. Uncheck XML Document.
  2. Set Start Element Name to Transaction.
  3. Set Row Tag to InvoiceLine.

xml-with-xslt.png

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>

xslt.png

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>

Related articles

Comments

0 comments

Please sign in to leave a comment.