Renumber invoice LineNumber tags and replace NaN with 0 using xslt

Question

Sorry to bug you again but I am struggling getting off the ground again with XSLT. In all of the searches I have done, I cannot see answers to these isues. I wonder if you learned XSLT people could assist me please..

I receive invoices in XML that have 3 issues that I need to deal with:

1. Issue 1 is that we get credit notes and some LineNumber tags are 0 (and if two have 0 then our import fails as the lines table has a unique index on invoice / credit ref and line number (which is fine)).

2. Issue 2 is that a new supplier adds 10000 and x 10 to line numbers they add after our initial order so if we have 3 lines ordered and they add two, the line numbers on the invoice are 1,2,3 10040 and 10050. Our (crazy) receiving table has a max value of 255 for line numbers.

3. Issue 3 is that some values occasionally have NaN in the InvoicedQuantity line level tag and our table has decimal type.

I have no interest in preserving line numbers so to deal with these issues I wish to:

1. Change the value of tags InvoicedQuantity and other affected tags to 0 if NaN
2. Reset the LineNumber tags from 1 to max in physical order

Here is a sample XML file (chopped down to only contain forced issues)

<?xml version="1.0" encoding="UTF-8"?>
<Invoice>
    <InvoiceHeader>
        <InvoiceReferences>
            <InvoiceReference>ABC123</InvoiceReference>
            <InvoiceDate>2020-03-11</InvoiceDate>
        </InvoiceReferences>
        <CostCentreCode>H020</CostCentreCode>
    </InvoiceHeader>
    <InvoiceDetail>
        <InvoiceLine>
            <LineNumber>0</LineNumber>
            <SuppliersProductCode>A0</SuppliersProductCode>
            <BuyersProductCode>ABC120</BuyersProductCode>
            <ProductDescription>Product Z</ProductDescription>
            <InvoicedQuantity UnitOfMeasure="EA">2</InvoicedQuantity>
            <PackSize>1</PackSize>
            <UnitValueExclVAT>5</UnitValueExclVAT>
            <LineValueExclVAT>10</LineValueExclVAT>
            <VATCode>Z</VATCode>
            <VATRate>0.00</VATRate>
        </InvoiceLine>
        <InvoiceLine>
            <LineNumber>1</LineNumber>
            <SuppliersProductCode>A1</SuppliersProductCode>
            <BuyersProductCode>ABC123</BuyersProductCode>
            <ProductDescription>Product A</ProductDescription>
            <InvoicedQuantity UnitOfMeasure="EA">2</InvoicedQuantity>
            <PackSize>1</PackSize>
            <UnitValueExclVAT>7.45</UnitValueExclVAT>
            <LineValueExclVAT>18.70</LineValueExclVAT>
            <VATCode>Z</VATCode>
            <VATRate>0.00</VATRate>
        </InvoiceLine>
        <InvoiceLine>
            <LineNumber>10020</LineNumber>
            <SuppliersProductCode>B1</SuppliersProductCode>
            <BuyersProductCode>ABC1456</BuyersProductCode>
            <ProductDescription>Product B</ProductDescription>
            <InvoicedQuantity UnitOfMeasure="EA">NaN</InvoicedQuantity>
            <PackSize>1</PackSize>
            <UnitValueExclVAT>7.45</UnitValueExclVAT>
            <LineValueExclVAT>NaN</LineValueExclVAT>
            <VATCode>Z</VATCode>
            <VATRate>0.00</VATRate>
        </InvoiceLine>
    </InvoiceDetail>
    <InvoiceTrailer>
    </InvoiceTrailer>
</Invoice>

Answer 1

Here's a modified identity transform, with special handling for LineNumber and InvoicedQuantity, hope it helps.

<xsl:stylesheet version="3.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >

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

    <xsl:template match='InvoiceDetail'>
        <xsl:element name='InvoiceDetail'>
            <xsl:for-each select='InvoiceLine'>
                <xsl:copy>
                    <!-- Create a sequential line # -->
                    <xsl:element name='LineNumber'>
                        <xsl:value-of select='position()'/>
                    </xsl:element>

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

                </xsl:copy>
            </xsl:for-each>
        </xsl:element>
    </xsl:template>

    <!-- Templates to handle our special cases -->
    <xsl:template match='LineNumber'>
        <!-- Nothing, get rid of the existing line #s -->
    </xsl:template>

    <xsl:template match='InvoicedQuantity'>
        <xsl:element name='InvoicedQuantity'>
            <xsl:apply-templates select="@*"/>
            <!-- Convert NaN to 1 -->
            <xsl:if test='(.="NaN")'>1</xsl:if>
            <xsl:if test='not(.="NaN")'><xsl:value-of select='.'/></xsl:if>
        </xsl:element>
    </xsl:template>
</xsl:stylesheet>