transforming one xml file with another xml file - now with some looping needs

[davidmhollis]

I originally posted back in Feb. 2011 about needing to transform one xml file with another xml file.. thinking it would be a straight one for one transformation... but of course, it can't remain that easy! I now have to take multiple line items from the input file based on an invoice number and use those to transform my output purchase order with the correct line items.

So taking the input file... something like this

<EInvoice>

<EID>
<EInvoiceData>
<InvoiceDate>1070108</InvoiceDate>
<CustomerNumber>4638501</CustomerNumber>
<InvoiceNumber>59925</InvoiceNumber>
<LineItem>100</LineItem>
<XRef>LPS3015-472MLC</XRef>
<CustPartNumber>3644882</CustPartNumber>
<ShipQty>3000.000</ShipQty>
<TotalTaxAmt>.00</TotalTaxAmt>
<UnitPrice>.2500000</UnitPrice>
<CustOrdNumber>22852462</CustOrdNumber>
<Terms>90</Terms>
<Total>750.00</Total>
</EInvoiceData>
</EID>

<EID>
<EInvoiceData>
<InvoiceDate>1070108</InvoiceDate>
<CustomerNumber>4638501</CustomerNumber>
<InvoiceNumber>59925</InvoiceNumber>
<LineItem>200</LineItem>
<XRef>LSS3014-401MLC</XRef>
<CustPartNumber>3643482</CustPartNumber>
<ShipQty>3000.000</ShipQty>
<TotalTaxAmt>.00</TotalTaxAmt>
<UnitPrice>.2500000</UnitPrice>
<CustOrdNumber>22852462</CustOrdNumber>
<Terms>90</Terms>
<Total>750.00</Total>
</EInvoiceData>
</EID>

<EID>
<EInvoiceData>
<InvoiceDate>2011-04-05</InvoiceDate>
<CustomerNumber>4638501</CustomerNumber>
<OrderNumber>1065143</OrderNumber>
<InvoiceNumber>164403</InvoiceNumber>
<LineItem>100</LineItem>
<XRef>DA2415-AL</XRef>
<CustPartNumber>3647976</CustPartNumber>
<ShipQty>1000.000</ShipQty>
<TotalTaxAmt>.00</TotalTaxAmt>
<UnitPrice>4.0000000</UnitPrice>
<CustOrdNumber>22751100</CustOrdNumber>
<Terms>90</Terms>
<Total>4000.00</Total>
</EInvoiceData>
</EID>

</EInvoice>

It will have multiple xml segments of invoice data... some from the same invoice, some from other invoices. The xml segments from the same invoice will be in order together... so I have been looping through the whole xml file looking for common invoice number and grabbing all line items associated with an invoice number. The tricky part is taking that information and using xsl to transform the output, to include those loops on the output purchase order end. Should I be able to add a for-each select to loop through each line item as long as it has the same invoice number associated with it? Can I use that inside the template match statement?

I do this template match and apply template select sequence for each tag on in the output file I want to transform with data from the input file.

<xsl:template match="/">
			<xsl:apply-templates select="EInvoiceData"/>
		</xsl:template>
	 
		<xsl:template match="EInvoiceData" >
			<tns:InvoiceNotification>
				<tns:Invoice>
					<tns:BillFrom>
						<xsl:apply-templates select="CustOrdNumber" />
					</tns:BillFrom>
				</tns:Invoice>
			</tns:InvoiceNotification>			
		</xsl:template>
	 
		<xsl:template match="CustOrdNumber" >
			<tns:PurchaseOrderNumber>
				<dp:Identifier>
					<xsl:value-of 	select="." />
				</dp:Identifier>
			</tns:PurchaseOrderNumber>
		</xsl:template>

Here's what I would like to see on the output end...some of the tags are static... but the line items from the input file need to loop from <InvoiceLineItem> to </InvoiceLineItem> in the output file.

output for each line item segment... values supplied from my static template and from the input file tags associated with each output tag - this whole xml segment will need to be transformed and in place for each line item associated with an invoice:

- <tns:InvoiceLineItem>
<tns:LineNumber>100</tns:LineNumber>
- <tns:OrderStatus>
- <tns:PurchaseOrder>
- <tns:ProductLineItem>
<tns:CountryOfOrigin>US</tns:CountryOfOrigin>
<tns:LineNumber>100</tns:LineNumber>
- <tns:ProductDescription>
- <updi:ProductIdentification>
<updi:ProductName>LPS3015-472MLC</updi:ProductName>
<ulc:Identifier>3644882</ulc:Identifier>
</updi:ProductIdentification>
</tns:ProductDescription>
<sha:ProductQuantity>3000.000</sha:ProductQuantity>
<ucr:Currency>USD</ucr:Currency>
<uuom:UnitOfMeasure>PIE</uuom:UnitOfMeasure>
- <tns:UnitPrice>
<ume:Amount>.2500000</ume:Amount>
<ucr:Currency>USD</ucr:Currency>
</tns:UnitPrice>
</tns:ProductLineItem>
- <tns:PurchaseOrderNumber>
<dp:Identifier>22852462</dp:Identifier>
<dp:Line>100</dp:Line>
</tns:PurchaseOrderNumber>
</tns:PurchaseOrder>
</tns:OrderStatus>
- <tns:PreTaxAmount>
- <ume:FinancialAmount>
<ume:Amount>750.00</ume:Amount>
<ucr:Currency>USD</ucr:Currency>
</ume:FinancialAmount>
- <tns:TotalLineItemAmount>
- <ume:FinancialAmount>
<ume:Amount>750.00</ume:Amount>
<ucr:Currency>USD</ucr:Currency>
</ume:FinancialAmount>
</tns:TotalLineItemAmount>
</tns:InvoiceLineItem>

[davidmhollis]

I think I've got something going here... but I could use a little help tweaking it. Can someone have a look at this code and see if I'm on the right track by using if test=(preceding-sibling::fieldname) for my loop?

thanks!

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="xml" indent="no" encoding="UTF-8"/>
<xsl:strip-space elements="*"/>

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

 

<xsl:template match="EInvoiceData">


     <xsl:for-each select="EInvoiceData">
      <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
      </xsl:copy>
     </xsl:for-each>

    
     <xsl:for-each select="InvoiceDate">
      <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
      </xsl:copy>
     </xsl:for-each>
      
     <xsl:for-each select="CustomerNumber">
      <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
      </xsl:copy>
     </xsl:for-each>
     
     <xsl:for-each select="OrderNumber">
      <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
      </xsl:copy>
     </xsl:for-each>
     
     <xsl:for-each select="InvoiceNumber">
      <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
      </xsl:copy>
     </xsl:for-each>
     
     <xsl:for-each select="LineItem">
     <xsl:copy>
     <xsl:apply-templates select="@*|node()"/>
     </xsl:copy>
     </xsl:for-each>
  
    <xsl:for-each select="XRef">
    <xsl:copy>
     <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
    </xsl:for-each>
  
    <xsl:for-each select="CustPartNumber">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
      </xsl:copy>
    </xsl:for-each>

    <xsl:for-each select="ShipQty">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
      </xsl:copy>
    </xsl:for-each>


    <xsl:for-each select="TotalTaxAmt">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
      </xsl:copy>
    </xsl:for-each>


    <xsl:for-each select="UnitPrice">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
      </xsl:copy>
    </xsl:for-each>


    <xsl:for-each select="CustOrdNumber">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
      </xsl:copy>
    </xsl:for-each>

    
    <xsl:for-each select="Terms">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
      </xsl:copy>
    </xsl:for-each>

    
    <xsl:for-each select="Total">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
      </xsl:copy>
    </xsl:for-each>
    
    </xsl:template>

   <xsl:template match="/">
   <xsl:apply-templates select="/EinvoiceData/InvoiceNumber" />
 
    <xsl:if test="(preceding::InvoiceNumber)">
    <p><xsl:call-template name="loop" /></p>
    </xsl:if>
    </xsl:template>
  

 




    <xsl:template name="loop" match="/">

    <xsl:for-each select="LineItem">
     <xsl:copy>
     <xsl:apply-templates select="@*|node()"/>
     </xsl:copy>
     </xsl:for-each>
  
    <xsl:for-each select="XRef">
    <xsl:copy>
     <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
    </xsl:for-each>
  
    <xsl:for-each select="CustPartNumber">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
      </xsl:copy>
    </xsl:for-each>

    <xsl:for-each select="ShipQty">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
      </xsl:copy>
    </xsl:for-each>


    <xsl:for-each select="TotalTaxAmt">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
      </xsl:copy>
    </xsl:for-each>


    <xsl:for-each select="UnitPrice">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
      </xsl:copy>
    </xsl:for-each>


    <xsl:for-each select="CustOrdNumber">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
      </xsl:copy>
    </xsl:for-each>

    
    <xsl:for-each select="Terms">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
      </xsl:copy>
    </xsl:for-each>

    
    <xsl:for-each select="Total">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
      </xsl:copy>
    </xsl:for-each>

    </xsl:template>

    




</xsl:stylesheet>

[xml_looser]

looks like a bad style from xsl
xsl is not a programming language

Please put in xml code disturb the smily
Many users have no desire to try to replace the smily

Please also be original and result-xml xml
when describing such a sum is generated to show how the sums are added together

[davidmhollis]

I'm attempting to loop through an input invoice file to get all the line items by invoice number ( grouping line items and quantity under matching invoice number )

I hope this clears up what I'm trying to do

I'm running this batch file:

msxsl.exe einvoice.xml loop.xsl -o output.xml

Input einvoice.xml

<EInvoiceXml>
<EInvoice>
<InvoiceNumber>1</InvoiceNumber>
<LineNumber>100</LineNumber>
<Quantity>2</Quantity>
<Total>9</Total>
</EInvoice>
<EInvoice>
<InvoiceNumber>1</InvoiceNumber>
<LineNumber>200</LineNumber>
<Quantity>3</Quantity>
<Total>9</Total>
</EInvoice>
<EInvoice>
<InvoiceNumber>2</InvoiceNumber>
<LineNumber>100</LineNumber>
<Quantity>4</Quantity>
<Total>9</Total>
</EInvoice>
</EInvoiceXml>

File to be transformed output.xml:

<InvoiceNotification>
<Invoice>
<PONumber>?<PONumber>
<LineItem>?<LineItem>
<ShipQty>?<ShipQty>
<TotalAmt>?<TotalAmt>
</Invoice>
</InvoiceNotification>

Needed result:

<InvoiceNotification>
<Invoice>
<PONumber>1<PONumber>
<LineItem>100<LineItem>
<ShipQty>2<ShipQty>
<LineItem>200<LineItem>
<ShipQty>3<ShipQty>
<PONumber>2<PONumber>
<LineItem>100<LineItem>
<ShipQty>4<ShipQty>
<TotalAmt>9<TotalAmt>
</Invoice>
</InvoiceNotification>

XSL:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 
<xsl:output encoding='utf-8'/>

<xsl:template match="/">

    <xsl:apply-templates select="EInvoice"/>

 </xsl:template>
 

 <xsl:template match="EInvoice" >

<InvoiceNotification>
 
 <Invoice>

  <PONumber><xsl:apply-templates select="InvoiceNumber"/><PONumber>

  <LineItem><xsl:apply-templates select="LineNumber"/><LineItem>

  <xsl:template match="/">

   <xsl:apply-templates select="/Einvoice/InvoiceNumber" />

     <xsl:if test="(preceding::InvoiceNumber)">

    <p><xsl:call-template name="loop" /></p>

    </xsl:if>
 
   </xsl:template>
  
  


  <ShipQty><xsl:apply-templates select="Quantity"/><ShipQty>

  <TotalAmt><xsl:apply-templates select="Total"/><TotalAmt>

 </Invoice>

</InvoiceNotification>


</xsl:template>

<xsl:template name="loop" match="/">

     <xsl:for-each select="LineItem">

     <xsl:copy> 

     <xsl:apply-templates select="@*|node()"/> 

     </xsl:copy> 

    </xsl:for-each>

   
    <xsl:for-each select="ShipQty">

     <xsl:copy> 

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

    </xsl:for-each>

</xsl:template>


</xsl:stylesheet>

[xml_looser]

I have tried to understand what you want to filter

do not understand why in front of 200 LineItem no PONumber

using xsl as a template form

If a node is found write down what should happen that is a template
then apply-template to address the template

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:output indent="yes" method="xml"/>
	<xsl:template match="/">
        <!-- root template allways use in xsl -->
		<InvoiceNotification>
			<xsl:apply-templates select="EInvoiceXml"/>
		</InvoiceNotification>
	</xsl:template>
	<xsl:template match="EInvoiceXml">
		<Invoice>
			<xsl:apply-templates select="EInvoice"/>
			<TotalAmt>
                        <!-- on the same level use //(child:: funktion ) to select all Quantity -->
				<xsl:value-of select="sum(//Quantity)"/>
			</TotalAmt>
		</Invoice>
	</xsl:template>
	<xsl:template match="EInvoice">
		<PONumber>
			<xsl:value-of select="position()"/>
		</PONumber>
		<LineItem>
			<xsl:value-of select="LineNumber"/>
		</LineItem>
		<ShipQty>
			<xsl:value-of select="Quantity"/>
		</ShipQty>
	</xsl:template>
</xsl:stylesheet>

result

<?xml version='1.0' ?>
<InvoiceNotification>
  <Invoice>
    <PONumber>1</PONumber>
    <LineItem>100</LineItem>
    <ShipQty>2</ShipQty>
    <PONumber>2</PONumber>
    <LineItem>200</LineItem>
    <ShipQty>3</ShipQty>
    <PONumber>3</PONumber>
    <LineItem>100</LineItem>
    <ShipQty>4</ShipQty>
    <TotalAmt>9</TotalAmt>
  </Invoice>
</InvoiceNotification>

[davidmhollis]

Hi looser...

The reason why there is no POnumber in front of line item 200, is because it belongs on the same PO as line item 100, due to the fact that they came from the same invoice (1). See what I'm getting at here? If the line items come from the same invoice, then they should appear as line items under the same PO on the output notification. I'm not worried about summing the quantity, as that is applied elsewhere... I just need the line items to line up correctly if they are from the same invoice.
Like this:

<InvoiceNotification>
<Invoice>

<PONumber>1<PONumber>
<LineItem>100<LineItem>
<ShipQty>2<ShipQty> < Line item segments from input invoice 1
<LineItem>200<LineItem>
<ShipQty>3<ShipQty>

<PONumber>2<PONumber>
<LineItem>100<LineItem> < Line item segments from input invoice 2
<ShipQty>4<ShipQty>

<TotalAmt>9<TotalAmt>
</Invoice>
</InvoiceNotification>

I hope that is a little more helpful. Below is the adjusted xsl I'm trying to get to work.

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 
<xsl:output encoding='utf-8'/>

<xsl:template match="/">

    <xsl:apply-templates select="EInvoiceXml"/>

 </xsl:template>
 

 <xsl:template match="EInvoice" >

<InvoiceNotification>
 
 <Invoice>

  <PONumber><xsl:apply-templates select="InvoiceNumber"/></PONumber>

  <LineItem><xsl:apply-templates select="LineNumber"/></LineItem>
   
<xsl:template match="/">

   <xsl:apply-templates select="/Einvoice/InvoiceNumber" />

     <xsl:if test="(preceding::InvoiceNumber)">

    <p><xsl:call-template name="loop" /></p>

    </xsl:if>
 
   </xsl:template>

  
<xsl:template name="loop" match="/">

     <xsl:for-each select="LineItem">

     <xsl:copy> 

     <xsl:apply-templates select="@*|node()"/>

     </xsl:copy> 

    </xsl:for-each>

   </xsl:template>
   
  
  
  <ShipQty><xsl:apply-templates select="Quantity"/></ShipQty>
 
  <TotalAmt><xsl:apply-templates select="Total"/></TotalAmt>

 </Invoice>

</InvoiceNotification>

[xml_looser]

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:output indent="yes" method="xml"/>
	<xsl:key match="LineNumber" name="only" use="."/>
	<xsl:template match="/">
		<InvoiceNotification>
			<xsl:apply-templates select="EInvoiceXml"/>
		</InvoiceNotification>
	</xsl:template>
	<xsl:template match="EInvoiceXml">
		<Invoice>
			<xsl:apply-templates select="EInvoice"/>
			<TotalAmt>
				<xsl:value-of select="sum(//Quantity)"/>
			</TotalAmt>
		</Invoice>
	</xsl:template>
	<xsl:template match="EInvoice">
		<xsl:variable select="LineNumber" name="test"/>
		<xsl:if test="count(key('only',LineNumber)) &gt; 1">
			<PONumber>
				<xsl:value-of select="count(preceding-sibling::EInvoice/LineNumber[.=$test])+1"/>
			</PONumber>
		</xsl:if>
		<LineItem>
			<xsl:value-of select="LineNumber"/>
		</LineItem>
		<ShipQty>
			<xsl:value-of select="Quantity"/>
		</ShipQty>
	</xsl:template>
</xsl:stylesheet>

xml to test

<EInvoiceXml>
	<EInvoice>
		<InvoiceNumber>1</InvoiceNumber>
		<LineNumber>100</LineNumber>
		<Quantity>2</Quantity>
		<Total>9</Total>
	</EInvoice>
	<EInvoice>
		<InvoiceNumber>1</InvoiceNumber>
		<LineNumber>200</LineNumber>
		<Quantity>3</Quantity>
		<Total>9</Total>
	</EInvoice>
	<EInvoice>
		<InvoiceNumber>2</InvoiceNumber>
		<LineNumber>100</LineNumber>
		<Quantity>4</Quantity>
		<Total>9</Total>
	</EInvoice>
	<EInvoice>
		<InvoiceNumber>2</InvoiceNumber>
		<LineNumber>100</LineNumber>
		<Quantity>4</Quantity>
		<Total>9</Total>
	</EInvoice>
	<EInvoice>
		<InvoiceNumber>2</InvoiceNumber>
		<LineNumber>100</LineNumber>
		<Quantity>4</Quantity>
		<Total>9</Total>
	</EInvoice>
	<EInvoice>
		<InvoiceNumber>2</InvoiceNumber>
		<LineNumber>200</LineNumber>
		<Quantity>4</Quantity>
		<Total>9</Total>
	</EInvoice>
	<EInvoice>
		<InvoiceNumber>2</InvoiceNumber>
		<LineNumber>400</LineNumber>
		<Quantity>4</Quantity>
		<Total>9</Total>
	</EInvoice>
</EInvoiceXml>

result

<?xml version='1.0' ?>
<InvoiceNotification>
  <Invoice>
    <PONumber>1</PONumber>
    <LineItem>100</LineItem>
    <ShipQty>2</ShipQty>
    <PONumber>1</PONumber>
    <LineItem>200</LineItem>
    <ShipQty>3</ShipQty>
    <PONumber>2</PONumber>
    <LineItem>100</LineItem>
    <ShipQty>4</ShipQty>
    <PONumber>3</PONumber>
    <LineItem>100</LineItem>
    <ShipQty>4</ShipQty>
    <PONumber>4</PONumber>
    <LineItem>100</LineItem>
    <ShipQty>4</ShipQty>
    <PONumber>2</PONumber>
    <LineItem>200</LineItem>
    <ShipQty>4</ShipQty>
    <LineItem>400</LineItem>
    <ShipQty>4</ShipQty>
    <TotalAmt>25</TotalAmt>
  </Invoice>

[davidmhollis]

Thanks Looser... I used your sequence in the larger, more complicated file and it works great. I had to do a little adjusting for namespaces and change how my input file is built... but your stylesheet with a count to test was exactly what I needed to get me started.

Thanks!