Looping a translation file for each xml line

0
votes

As may be evident my knowledge of XML and XSLT is rather limited. Please can someone assist me with a translation file which will allow for each ItemNumber/Sales Order in the below XML to be passed through so that the second XML is generated for each item. In other words, I need to generate multiple output xmls (one for each sales order). At the moment I am only getting one output xml for the last line item /sales order on the initial xml.

Here is the xml that I need to translate:

<?xml version="1.0" encoding="Windows-1252"?>
<postsalesorderssct Language='05' Language2='EN' CssStyle='' DecFormat='1' DateFormat='01' Role='01' Version='6.1.009' OperatorPrimaryRole='   '>
    <Item>
        <Key>
            <SalesOrder>197588</SalesOrder>
            <SourceWarehouse>A3</SourceWarehouse>
            <TargetWarehouse>PV</TargetWarehouse>
            <CustomerPoNumber/>
        </Key>
        <ItemNumber>000001</ItemNumber>
    </Item>
    <Item>
        <Key>
            <SalesOrder>197589</SalesOrder>
            <SourceWarehouse>A3</SourceWarehouse>
            <TargetWarehouse>HI</TargetWarehouse>
            <CustomerPoNumber/>
        </Key>
        <ItemNumber>000002</ItemNumber>
    </Item>
    <Item>
        <Key>
            <SalesOrder>197590</SalesOrder>
            <SourceWarehouse>A3</SourceWarehouse>
            <TargetWarehouse>WS</TargetWarehouse>
            <CustomerPoNumber/>
        </Key>
        <ItemNumber>000003</ItemNumber>
    </Item>
    <Item>
        <Key>
            <SalesOrder>197591</SalesOrder>
            <SourceWarehouse>A4</SourceWarehouse>
            <TargetWarehouse>HI</TargetWarehouse>
            <CustomerPoNumber/>
        </Key>
        <ItemNumber>000004</ItemNumber>
    </Item>
    <Item>
        <Key>
            <SalesOrder>197592</SalesOrder>
            <SourceWarehouse>A4</SourceWarehouse>
            <TargetWarehouse>PV</TargetWarehouse>
            <CustomerPoNumber/>
        </Key>
        <ItemNumber>000005</ItemNumber>
    </Item>
    <Item>
        <Key>
            <SalesOrder>197593</SalesOrder>
            <SourceWarehouse>A4</SourceWarehouse>
            <TargetWarehouse>WS</TargetWarehouse>
            <CustomerPoNumber/>
        </Key>
        <ItemNumber>000006</ItemNumber>
    </Item>
    <StatusOfItems>
        <ItemsProcessed>000006</ItemsProcessed>
        <ItemsInvalid>000000</ItemsInvalid>
    </StatusOfItems>
</postsalesorderssct>

Here is the translation file I am currently using:

    <?xml version="1.0" ?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="xml" encoding="Windows-1252" omit-xml-declaration="yes" />
<xsl:template match="/">
<Query>
 <Key>
  <xsl:for-each select = "postsalesorderssct/Item/Key">
       <SalesOrder><xsl:value-of select="SalesOrder"/></SalesOrder>
  </xsl:for-each>
 </Key>
</Query>
</xsl:template>
</xsl:stylesheet>

Here is the required output for each of the ItemNumbers on the initial xml. In other words I need one of these generated for each Sales Order number:

<Query xmlns:xsd="http://www.w3.org/2001/XMLSchema-instance" xsd:noNamespaceSchemaLocation="SORQRY.XSD">
    <Key>
        <SalesOrder>197588</SalesOrder>
        <Invoice/>
    </Key>
    <Option>
        <IncludeStockedLines>Y</IncludeStockedLines>
        <IncludeNonStockedLines>Y</IncludeNonStockedLines>
        <IncludeFreightLines>Y</IncludeFreightLines>
        <IncludeMiscLines>Y</IncludeMiscLines>
        <IncludeCommentLines>Y</IncludeCommentLines>
        <IncludeCompletedLines>Y</IncludeCompletedLines>
        <IncludeSerials>N</IncludeSerials>
        <IncludeLots>Y</IncludeLots>
        <IncludeBins>Y</IncludeBins>
        <IncludeAttachedItems>N</IncludeAttachedItems>
        <IncludeCustomForms>Y</IncludeCustomForms>
        <IncludeDetailLineCustomForms>Y</IncludeDetailLineCustomForms>
        <XslStylesheet/>
    </Option>
</Query>

Thanking you in anticipation.

2
xmlxslt
Your XSLT does not match your input XML. For example in your XSLT you use postsororderstatus/Item/Order, but in the input XML the root is <postsalesorderssct> so no match there. Also You loop through each <Order>, but this element is also not in your input XML. When I look to the desired output this also doesn't match. So what do you want? Or please correct your question! - Mark Veenstra
Which version of XSLT are you using? In XSLT 1.0 there is now way of doing this without using extension elements (if you have access to exslt, you could use <exsl:document>). In XSLT 2.0 you can use <xsl:result-document> to generate multiple output files. - Pablo Pozo
My apologies Mark I had multiple files open and copied the wrong one. I will adjust the question accordingly. - Devon Trew

2 Answers

1
votes

If you can use XSLT 2.0, the following should work...

XML Input

<postsalesorderssct Language='05' Language2='EN' CssStyle='' DecFormat='1' DateFormat='01' Role='01' Version='6.1.009' OperatorPrimaryRole='   '>
    <Item>
        <Key>
            <SalesOrder>197588</SalesOrder>
            <SourceWarehouse>A3</SourceWarehouse>
            <TargetWarehouse>PV</TargetWarehouse>
            <CustomerPoNumber/>
        </Key>
        <ItemNumber>000001</ItemNumber>
    </Item>
    <Item>
        <Key>
            <SalesOrder>197589</SalesOrder>
            <SourceWarehouse>A3</SourceWarehouse>
            <TargetWarehouse>HI</TargetWarehouse>
            <CustomerPoNumber/>
        </Key>
        <ItemNumber>000002</ItemNumber>
    </Item>
    <Item>
        <Key>
            <SalesOrder>197590</SalesOrder>
            <SourceWarehouse>A3</SourceWarehouse>
            <TargetWarehouse>WS</TargetWarehouse>
            <CustomerPoNumber/>
        </Key>
        <ItemNumber>000003</ItemNumber>
    </Item>
    <Item>
        <Key>
            <SalesOrder>197591</SalesOrder>
            <SourceWarehouse>A4</SourceWarehouse>
            <TargetWarehouse>HI</TargetWarehouse>
            <CustomerPoNumber/>
        </Key>
        <ItemNumber>000004</ItemNumber>
    </Item>
    <Item>
        <Key>
            <SalesOrder>197592</SalesOrder>
            <SourceWarehouse>A4</SourceWarehouse>
            <TargetWarehouse>PV</TargetWarehouse>
            <CustomerPoNumber/>
        </Key>
        <ItemNumber>000005</ItemNumber>
    </Item>
    <Item>
        <Key>
            <SalesOrder>197593</SalesOrder>
            <SourceWarehouse>A4</SourceWarehouse>
            <TargetWarehouse>WS</TargetWarehouse>
            <CustomerPoNumber/>
        </Key>
        <ItemNumber>000006</ItemNumber>
    </Item>
    <StatusOfItems>
        <ItemsProcessed>000006</ItemsProcessed>
        <ItemsInvalid>000000</ItemsInvalid>
    </StatusOfItems>
</postsalesorderssct>

XSLT 2.0

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output indent="yes"/>
    <xsl:strip-space elements="*"/>

    <xsl:template match="/postsalesorderssct/Item">
        <xsl:result-document href="{Key/SalesOrder}.xml">
            <Query xmlns:xsd="http://www.w3.org/2001/XMLSchema-instance" xsd:noNamespaceSchemaLocation="SORQRY.XSD">
                <Key>
                    <xsl:copy-of select="Key/SalesOrder"/>
                    <Invoice/>
                </Key>
                <Option>
                    <IncludeStockedLines>Y</IncludeStockedLines>
                    <IncludeNonStockedLines>Y</IncludeNonStockedLines>
                    <IncludeFreightLines>Y</IncludeFreightLines>
                    <IncludeMiscLines>Y</IncludeMiscLines>
                    <IncludeCommentLines>Y</IncludeCommentLines>
                    <IncludeCompletedLines>Y</IncludeCompletedLines>
                    <IncludeSerials>N</IncludeSerials>
                    <IncludeLots>Y</IncludeLots>
                    <IncludeBins>Y</IncludeBins>
                    <IncludeAttachedItems>N</IncludeAttachedItems>
                    <IncludeCustomForms>Y</IncludeCustomForms>
                    <IncludeDetailLineCustomForms>Y</IncludeDetailLineCustomForms>
                    <XslStylesheet/>
                </Option>
            </Query>        
        </xsl:result-document>      
    </xsl:template>

</xsl:stylesheet>

This will give you an individual file for each Item. The name of the file will be the SalesOrder.

A couple of examples...

197588.xml

<Query xmlns:xsd="http://www.w3.org/2001/XMLSchema-instance"
       xsd:noNamespaceSchemaLocation="SORQRY.XSD">
   <Key>
      <SalesOrder>197588</SalesOrder>
      <Invoice/>
   </Key>
   <Option>
      <IncludeStockedLines>Y</IncludeStockedLines>
      <IncludeNonStockedLines>Y</IncludeNonStockedLines>
      <IncludeFreightLines>Y</IncludeFreightLines>
      <IncludeMiscLines>Y</IncludeMiscLines>
      <IncludeCommentLines>Y</IncludeCommentLines>
      <IncludeCompletedLines>Y</IncludeCompletedLines>
      <IncludeSerials>N</IncludeSerials>
      <IncludeLots>Y</IncludeLots>
      <IncludeBins>Y</IncludeBins>
      <IncludeAttachedItems>N</IncludeAttachedItems>
      <IncludeCustomForms>Y</IncludeCustomForms>
      <IncludeDetailLineCustomForms>Y</IncludeDetailLineCustomForms>
      <XslStylesheet/>
   </Option>
</Query>

197592.xml

<Query xmlns:xsd="http://www.w3.org/2001/XMLSchema-instance"
       xsd:noNamespaceSchemaLocation="SORQRY.XSD">
   <Key>
      <SalesOrder>197592</SalesOrder>
      <Invoice/>
   </Key>
   <Option>
      <IncludeStockedLines>Y</IncludeStockedLines>
      <IncludeNonStockedLines>Y</IncludeNonStockedLines>
      <IncludeFreightLines>Y</IncludeFreightLines>
      <IncludeMiscLines>Y</IncludeMiscLines>
      <IncludeCommentLines>Y</IncludeCommentLines>
      <IncludeCompletedLines>Y</IncludeCompletedLines>
      <IncludeSerials>N</IncludeSerials>
      <IncludeLots>Y</IncludeLots>
      <IncludeBins>Y</IncludeBins>
      <IncludeAttachedItems>N</IncludeAttachedItems>
      <IncludeCustomForms>Y</IncludeCustomForms>
      <IncludeDetailLineCustomForms>Y</IncludeDetailLineCustomForms>
      <XslStylesheet/>
   </Option>
</Query>
1
votes

Achieving this with XSLT 2.0 is fairly straightforward, as Daniel demonstrated. Unfortunately, with pure XSLT 1.0, it's impossible, as far as I can tell, but you can achieve the same result if you're willing to work a bit harder.

(I wouldn't call this an elegant solution by any means, but it's the first method I can come up with for solving this problem without XSLT 2.0.)

I don't know which XSLT processor you're using, so I'll demonstrate with xsltproc.

You could first use a stylesheet like this to create an XML file that contains all the <Query> elements (call it with xsltproc allQuery.xsl input.xml):

    <!-- allQuery.xsl -->

    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"
      xmlns:xsd="http://www.w3.org/2001/XMLSchema-instance">
      <xsl:output method="xml" encoding="Windows-1252" omit-xml-declaration="yes" indent="yes"/>

      <xsl:template match="/">
        <Queries>
            <xsl:apply-templates select="postsalesorderssct/Item/Key/SalesOrder"/>
        </Queries>
      </xsl:template>

      <xsl:template match="SalesOrder">
        <xsl:variable name="id" select="."/>
          <Query xsd:noNamespaceSchemaLocation="SORQRY.XSD">
            <Key>
              <xsl:copy>
                <xsl:apply-templates/>
              </xsl:copy>
              <Invoice/>
            </Key>
            <Option>
                <IncludeStockedLines>Y</IncludeStockedLines>
                <IncludeNonStockedLines>Y</IncludeNonStockedLines>
                <IncludeFreightLines>Y</IncludeFreightLines>
                <IncludeMiscLines>Y</IncludeMiscLines>
                <IncludeCommentLines>Y</IncludeCommentLines>
                <IncludeCompletedLines>Y</IncludeCompletedLines>
                <IncludeSerials>N</IncludeSerials>
                <IncludeLots>Y</IncludeLots>
                <IncludeBins>Y</IncludeBins>
                <IncludeAttachedItems>N</IncludeAttachedItems>
                <IncludeCustomForms>Y</IncludeCustomForms>
                <IncludeDetailLineCustomForms>Y</IncludeDetailLineCustomForms>
                <XslStylesheet/>
            </Option>
          </Query>
      </xsl:template>
    </xsl:stylesheet>

Then you could have another simple stylesheet like this:

<!-- extractQuery.xsl -->

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"
  xmlns:xsd="http://www.w3.org/2001/XMLSchema-instance">

  <xsl:output method="xml" encoding="Windows-1252" omit-xml-declaration="yes" indent="yes"/>

  <!-- The sales order number you pass in on the command line. -->
  <xsl:param name="salesOrderNo"/>

  <xsl:template match="/">
    <!--
    Only apply the <Query> element with a <SalesOrder> descendant that has 
    $salesOrderNo as its text content -- in the case of this example, 197593.
    --> 
    <xsl:apply-templates select="Queries/Query[descendant::SalesOrder[. = $salesOrderNo]]"/>
  </xsl:template>

  <!-- Identity template -->
  <xsl:template match="node() | @*">
    <xsl:copy>
      <xsl:apply-templates select="@* | node()"/>
    </xsl:copy>
  </xsl:template>

</xsl:stylesheet>

You can use this stylesheet with xsltproc like this, passing in the SalesOrder number of the <Query> element you want to get as a parameter to the stylesheet:

xsltproc --stringparam salesOrderNo 197593 extractQuery.xsl output.xml

This will produce the following output:

<Query xmlns:xsd="http://www.w3.org/2001/XMLSchema-instance" xsd:noNamespaceSchemaLocation="SORQRY.XSD">
  <Key>
    <SalesOrder>197593</SalesOrder>
    <Invoice/>
  </Key>
  <Option>
    <IncludeStockedLines>Y</IncludeStockedLines>
    <IncludeNonStockedLines>Y</IncludeNonStockedLines>
    <IncludeFreightLines>Y</IncludeFreightLines>
    <IncludeMiscLines>Y</IncludeMiscLines>
    <IncludeCommentLines>Y</IncludeCommentLines>
    <IncludeCompletedLines>Y</IncludeCompletedLines>
    <IncludeSerials>N</IncludeSerials>
    <IncludeLots>Y</IncludeLots>
    <IncludeBins>Y</IncludeBins>
    <IncludeAttachedItems>N</IncludeAttachedItems>
    <IncludeCustomForms>Y</IncludeCustomForms>
    <IncludeDetailLineCustomForms>Y</IncludeDetailLineCustomForms>
    <XslStylesheet/>
  </Option>
</Query>

You'd then need to call xsltproc once for every sales order number you want to create an XML file for. One way of doing this is to create yet another XSLT stylesheet:

<!-- getSalesOrderNos.xsl -->

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"
  xmlns:xsd="http://www.w3.org/2001/XMLSchema-instance">

  <xsl:output method="text" encoding="Windows-1252" omit-xml-declaration="yes"/>

  <xsl:template match="/">
    <xsl:apply-templates select="Queries/Query/Key/SalesOrder"/>
  </xsl:template>

  <xsl:template match="SalesOrder">
    <xsl:value-of select="."/><xsl:text>
</xsl:text>
  </xsl:template>

</xsl:stylesheet>

This will print out a newline-separated list of all sales order numbers. You can then use it with a Bash script like this:

xsltproc getSalesOrderNos.xsl output.xml | xargs -L1 -I no xsltproc \
-o SalesOrder-no.xml --stringparam salesOrderNo no extractQuery.xsl output.xml

In essence, this will print out a list of all the sales order numbers in output.xml and run extractQuery.xsl once for each number -- with the number passed in as a parameter to extractQuery.xsl -- and create a new file (called SalesOrder-<ordernumber>.xml) for the result of each transformation. As in:

<!-- SalesOrder-197588.xml -->

<Query xmlns:xsd="http://www.w3.org/2001/XMLSchema-instance" xsd:noNamespaceSchemaLocation="SORQRY.XSD">
  <Key>
    <SalesOrder>197588</SalesOrder>
    <Invoice/>
  </Key>
  <Option>
    <IncludeStockedLines>Y</IncludeStockedLines>
    <IncludeNonStockedLines>Y</IncludeNonStockedLines>
    <IncludeFreightLines>Y</IncludeFreightLines>
    <IncludeMiscLines>Y</IncludeMiscLines>
    <IncludeCommentLines>Y</IncludeCommentLines>
    <IncludeCompletedLines>Y</IncludeCompletedLines>
    <IncludeSerials>N</IncludeSerials>
    <IncludeLots>Y</IncludeLots>
    <IncludeBins>Y</IncludeBins>
    <IncludeAttachedItems>N</IncludeAttachedItems>
    <IncludeCustomForms>Y</IncludeCustomForms>
    <IncludeDetailLineCustomForms>Y</IncludeDetailLineCustomForms>
    <XslStylesheet/>
  </Option>
</Query>

As I said, it's not pretty by any means. Depending on what tools you have access to, you might simplify this process quite a bit (e.g. use something else than XSLT to extract the list of sales order numbers and proceed from there).