Articles in this section

See more

Use XSLT when reading and writing XML documents

  • Updated
Follow

Overview

In Etlworks, you can modify the source and the destination XML documents by using Extensible Stylesheet Language Transformation (XSLT). 

Use XSLT when reading XML documents

You have received a source XML from a web service and need to 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 Etlworks, 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 Etlworks, 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.

<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>

Your source is a flat file, database table, or dataset produced by executing an SQL query, which looks like the one below. To simplify this example, we will not be explaining how to select data from the database or read a flat file. 

flat-dataset.png

Here are the columns in the source:

columns.png

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 use an XLT transformation to reformat the XML into the expected 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.