XSLT: populating strings from multiple nodes --> specific nodes and number of nodes can vary. How to deal with this?

Question

We are importing Peppol vendor invoices into our ERP-system by converting then into to another xml format using xslt. An example of a such an invoice is here:Github: OpenPEPPOL / peppol-bis-invoice-3

I want to give my accountant full freedom regarding which fields to be populated in each ERP-vendor invoice journal field. I want to give them a choice from a predefined set of nodes to use, for example:

/Invoice/ID /Invoice/IssueDate 
/Invoice/AccountingSupplierParty/Party/PartyName/Name 
/Invoice/InvoiceLine/InvoicedQuantity
/Invoice/InvoiceLine/Item/Name

I import specific vendor information from the ERP-system to use in the transformation process, for example to look up the vendor account:

<vendors>
    <vendor>
        <administration>YIT</administration>
        <FISCALCODE>04705810150</FISCALCODE>
        <accountNumber>20003</accountNumber>
        <Offset_LedgerAccount>67123</Offset_LedgerAccount>
        <name>A.Manzoni&amp;C. Spa</name>
    </vendor>
<vendors>

I lookup this information in the vendor xml table using the xslt below:

<xsl:variable name="LegalEntity">  
<xsl:choose>
<xsl:when test="contains(cac:AccountingCustomerParty/cac:Party/cac:PartyName/cbc:Name, 'Italia')">YIT</xsl:when>
<xsl:otherwise>'UNKNOWN LegalEntity'</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<xsl:variable name="current-vendor-name" select="cac:AccountingSupplierParty/cac:Party/cac:PartyName/cbc:Name"/>
<xsl:variable name="vendor-data" select="document('Vendors.xml')/vendors/vendor[name=$current-vendor-name and administration=$LegalEntity]"/>

The output of the transformation (when applied to the example input file in the link) is currently like below:

<?xml version="1.0" encoding="utf-8"?>
<PurchaseInvoices_version_1.0>
   <PurchaseInvoice>
      <LegalEntity>YIT</LegalEntity>
      <SupplierAccountNum>20202</SupplierAccountNum>
      <SupplierBankAccount>IBAN32423940</SupplierBankAccount>
      <SupplierName>SupplierTradingName Ltd.</SupplierName>
      <SupplierCity>London</SupplierCity>
      <CurrencyCode>EUR</CurrencyCode>
      <AmountExclTax>1325</AmountExclTax>
      <AmountInclTax>1656.25</AmountInclTax>
      <TaxAmount>331.25</TaxAmount>
      <InvoiceId>Snippet1</InvoiceId>
      <InvoiceDate>13-11-2017</InvoiceDate>
      <PaymentNote>Payment within 10 days, 2% discount</PaymentNote>
      <TaxCode>S</TaxCode>
      <Lines>
         <Line>
            <Item>|Description of item</Item>
            <Quantity>7</Quantity>
            <UnitPrice>400</UnitPrice>
            <LineAmount>2800</LineAmount>
            <AmountIncl>3500</AmountIncl>
            <AmountExcl>2800</AmountExcl>
            <TaxAmount>700</TaxAmount>
            <TaxCode>S</TaxCode>
            <Description>Description of item|2017-11-13</Description>
            <Unit>pcs</Unit>
         </Line>
         <Line>
            <Item>|Description 2</Item>
            <Quantity>-3</Quantity>
            <UnitPrice>500</UnitPrice>
            <LineAmount>-1500</LineAmount>
            <AmountIncl>-1875</AmountIncl>
            <AmountExcl>-1500</AmountExcl>
            <TaxAmount>-375</TaxAmount>
            <TaxCode>S</TaxCode>
            <Description>Description 2|2017-11-13</Description>
            <Unit>pcs</Unit>
         </Line>
      </Lines>
   </PurchaseInvoice>
</PurchaseInvoices_version_1.0>

I want to create more freedom regarding which text (nodes) from the input xml to use in the Description field as well as the order in which to show them. This should be defined per vendor on the vendor card and imported into the vendors.xml file of which a sample is shown above.

For example for vendor A the description could be a concatenation of all nodes:

/Invoice/ID /Invoice/IssueDate 
/Invoice/InvoiceLine/Item/Name
/Invoice/InvoiceLine/InvoicedQuantity
/Invoice/AccountingSupplierParty/Party/PartyName/Name

While for vendor B the description could be only:

/Invoice/InvoiceLine/Item/Name

The question is how to proceed from here.

My current idea is to create a description based on fixed number of variables:

<Description><xsl:value-of select="$A"/><xsl:value-of select="$B"/><xsl:value-of select="$C"/><xsl:value-of select="$D"/></Description>

And put the correct nodes in each variable based on some kind of input on Vendor card which is imported into the vendors.xml file and used in the xslt script.

Your question isn't clear, to me at least. Can you provide an example of your input XML, and an example of what you're trying to extract from it? — Conal Tuohy, Oct 10 '22 at 12:14
I can but my problem is not so much technical but more procedural. To put it simple: I want to combine multiple nodes to a string whereby the order of the nodes can vary. Which nodes and which order it should use does need to come from an external source (ERP-system). You actually helped me very well some time ago by suggesting to use an external xml file: https://stackoverflow.com/questions/73137322/xslt-with-lots-of-whens-which-are-different-per-company-how-to-manage/73138186?noredirect=1#comment129222348_73138186 — Kees Netelvrees, Oct 10 '22 at 12:44
I think I understand that for each vendor you have a code which specifies how to build a `description` (and presumably other output fields, too, otherwise the code would not need to start with `D` I guess?) from some input fields. The code specifies a list of references to fields in your input, by numeric index. But I'm still not clear: is there a`Name` field in your input XML? Is it always the first among its sibling elements? And does a `DueDate` element always follow it? An actual example would make this all clear. — Conal Tuohy, Oct 10 '22 at 13:00
I added a sample of a peppol invoice to the end of the opening post. I process it with Powershell, I am using xslt v1.0 (I think). What I do is take multiple nodes from this invoice and form it to a description field. However, which nodes to use and in which order can vary. I want to put something on the vendor card to indicate this. This is extracted every 15 minutes to sql database and formed into a vendors.xml that the xslt is using as input. the xslt script is then reading this vendor specific field to understand which nodes to use and in which order to use a description. — Kees Netelvrees, Oct 10 '22 at 14:05
I am afraid this is still clear as mud. Please provide a simplified example showing input, expected output and the required logic. Speaking *very* generally, you could probably use the extracted substring in a *predicate* to get the desired element's value by number, rather than referencing it by its name. But at this point, that's just a wild guess. — michael.hor257k, Oct 10 '22 at 15:27
P.S. I suspect your processor's version could be very significant here. If you don't know for sure, find out - see here how: https://stackoverflow.com/a/25245033/3016153 — michael.hor257k, Oct 10 '22 at 15:30
Please give us an example output ``: should it contain child elements? Or should it just be an element containing a single text node formed by concatenating the input elements? Presumably you'd want a separator between each piece of text? A line break? BTW if this is the XSLT processor you're using then it's XSLT version 1.0 alright: https://learn.microsoft.com/en-us/dotnet/api/system.xml.xsl.xslcompiledtransform?view=net-6.0 — Conal Tuohy, Oct 10 '22 at 22:56
I revised the text. I hope it is more clear now. I really appreciate your time spent on this. — Kees Netelvrees, Oct 11 '22 at 09:24
I use powershell to apply xslt. It is my understanding that Powershel l only supports xslt 1.0 by default. It is my understanding that v2.0 requires to load third party processors. — Kees Netelvrees, Oct 11 '22 at 16:13
PowerShell is not an XSLT processor. It very likely uses a Microsoft processor, but if you want to be sure, follow the instructions in my earlier comment. — michael.hor257k, Oct 11 '22 at 16:52

michael.hor257k · Accepted Answer · 2022-10-11T12:44:07.223

Going with your original idea of using a numeric code for the order of the values to be contained in the description, consider the following, much simplified, example.

XML

<Invoice>
    <ID>001</ID>
    <DueDate>2002-01-01</DueDate>
    <LineItem>
        <Name>Widget</Name>
        <Quantity>5</Quantity>
        <Description>Description of widget</Description>
    </LineItem>
    <LineItem>
        <Name>Gadget</Name>
        <Quantity>17</Quantity>
        <Description>Description of gadget</Description>
    </LineItem>
</Invoice>

XSLT 1.0

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

<xsl:param name="reorder">030102</xsl:param>

<xsl:template match="/Invoice">
    <PurchaseInvoice Id="{ID}">
        <!-- ... -->
        <xsl:for-each select="LineItem">
            <Line>
                <!-- ... -->
                <Description>
                    <xsl:call-template name="reorder">
                        <xsl:with-param name="nodes" select="../DueDate | Name | Description"/>
                        <xsl:with-param name="order" select="$reorder"/>
                    </xsl:call-template>
                </Description>
            </Line>
        </xsl:for-each>
    </PurchaseInvoice>
</xsl:template>

<xsl:template name="reorder">
    <xsl:param name="nodes" select="/.."/>
    <xsl:param name="order"/>
    <xsl:value-of select="$nodes[number(substring($order, 1 , 2))]"/>
    <xsl:if test="string-length($order) > 2">
        <xsl:text> | </xsl:text>
        <!-- recursive call -->
        <xsl:call-template name="reorder">
            <xsl:with-param name="nodes" select="$nodes"/>
            <xsl:with-param name="order" select="substring($order, 3)"/>
        </xsl:call-template>
    </xsl:if>
</xsl:template>

</xsl:stylesheet>

Result

<?xml version="1.0" encoding="UTF-8"?>
<PurchaseInvoice Id="001">
  <Line>
    <Description>Description of widget | 2002-01-01 | Widget</Description>
  </Line>
  <Line>
    <Description>Description of gadget | 2002-01-01 | Gadget</Description>
  </Line>
</PurchaseInvoice>

To keep this simple, you must assign the numeric codes to the values listed in the nodes parameter in the order in which they appear in the input XML.

In this example the code is a global parameter; in your implementation you will want to retrieve it from the vendors document.

Added:

If you prefer, you can use descriptive strings instead of numeric codes to select the order. But then the stylesheet becomes more complex:

XSLT 1.0

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

<xsl:param name="reorder">description|due date|name</xsl:param>

<xsl:template match="/Invoice">
    <PurchaseInvoice Id="{ID}">
        <!-- ... -->
        <xsl:for-each select="LineItem">
            <Line>
                <!-- ... -->
                <Description>
                    <xsl:call-template name="reorder">
                        <xsl:with-param name="order" select="$reorder"/>
                    </xsl:call-template>
                </Description>
            </Line>
        </xsl:for-each>
    </PurchaseInvoice>
</xsl:template>

<xsl:template name="reorder">
    <xsl:param name="order"/>
    <xsl:param name="delimiter" select="'|'"/>
    <xsl:variable name="token" select="substring-before(concat($order, $delimiter), $delimiter)" />
    <xsl:choose>
        <xsl:when test="$token='due date'">
            <xsl:value-of select="../DueDate"/>
        </xsl:when>
        <xsl:when test="$token='name'">
            <xsl:value-of select="Name"/>
        </xsl:when>
        <xsl:when test="$token='description'">
            <xsl:value-of select="Description"/>
        </xsl:when>
    </xsl:choose>
    <xsl:if test="contains($order, $delimiter)">
        <xsl:text> | </xsl:text>
        <!-- recursive call -->
        <xsl:call-template name="reorder">
            <xsl:with-param name="order" select="substring-after($order, $delimiter)"/>
        </xsl:call-template>
    </xsl:if>
</xsl:template>

</xsl:stylesheet>

This looks perfect, thank you. This is indeed what I was looking for! I will try to implement this in what I have already. — Kees Netelvrees, Oct 11 '22 at 16:30
Hi Michael, I tried to reproduce the result with Oxygen but I only get 030102 as result. If I try it with freeformatter.com/xsl-transformer.html then it works perfectly, also with Powershell it works perfect. The script is too complicated for me too understand where it goes wrong. Do you have any idea? I use a paid Oxygen Developer to work on the xlst script. — Kees Netelvrees, Oct 12 '22 at 06:40
Hi Michael, I tried to reproduce the result with Oxygen but I only get 030102 as result. If I try it with freeformatter.com/xsl-transformer.html then it works perfectly. When I apply it my full xslt script, it also works perfectly. I really appreciate it! — Kees Netelvrees, Oct 12 '22 at 07:05
If I replace/mix nodes with variables I get the error "Cannot process a result tree fragment as a node-set under XSLT 1.0". With some variables it works, with some it doesn't. How can I create the same using variables instead of nodes? — Kees Netelvrees, Oct 21 '22 at 07:09
I am afraid I don't know what you mean by "replace/mix nodes with variables". I suggest you post a new question and include a [mcve] of your current problem. — michael.hor257k, Oct 21 '22 at 10:32
May I ask another question: your example assumes that I am always going to use all nodes (../DueDate, Name and Description in your example). However, both the nodes to use as well as the order may vary. This leads to the problem that if I use order 0102 then the result is "2002-01-01 | Widget" but if DueDate does not exist hen order 0102 gives "Widget | Description of widget" as result. Do you know how I can overcome this? — Kees Netelvrees, Oct 21 '22 at 14:09
I believe the only way to overcome this is to use names instead of numbers. The stylesheet is not clairvoyant; it cannot know that there is a "missing" node in the input XML. — michael.hor257k, Oct 21 '22 at 15:02
Do note that you can continue to use numeric codes, if you so prefer. You just need to map them into node references, as shown in my 2nd stylesheet. — michael.hor257k, Oct 21 '22 at 15:27

XSLT: populating strings from multiple nodes --> specific nodes and number of nodes can vary. How to deal with this?

1 Answers1

Added: