CSV to XML using XSLT-1.0 group by first 0 value-CodePudding

I would like to use the XSLT 1.0 engine to transform a CSV file to an XML document. The CSV file contains documents for each customer with a flexible amount of document lines. Each new document starts with 0.

CSV example:

<root>
0;15-01-2022;Customer1
1;Dual monitors;50
2;Laser mouse;10.50
0;21-1-2022;Customer5
1;Multi-jet printer;100
0;30-1-2022;Customer8
1;Goods returned;-200
2;Basic keyboard;300
</root>

Here's the result XML document I would like to get:

<Documents>
   <Document>
      <Header>
         <Customer>Customer1</Customer>
         <Date>15-01-2022</Date>
      </Header>
      <Lines>
         <Line>
            <LineNumber>1</LineNumber>
            <Price>50</Price>
            <Description>Dual monitors</Description>
         </Line>
         <Line>
            <LineNumber>2</LineNumber>
            <Price>10.50</Price>
            <Description>Laser mouse</Description>
         </Line>
      </Lines>
   </Document>
   <Document>
      <Header>
         <Customer>Customer5</Customer>
         <Date>21-1-2022</Date>
      </Header>
      <Lines>
         <Line>
            <LineNumber>1</LineNumber>
            <Price>100</Price>
            <Description>Multi-jet printer</Description>
         </Line>
      </Lines>
   </Document>
   <Document>
      <Header>
         <Customer>Customer8</Customer>
         <Date>30-1-2022</Date>
      </Header>
      <Lines>
         <Line>
            <LineNumber>1</LineNumber>
            <Price>-200</Price>
            <Description>Goods returned</Description>
         </Line>
         <Line>
            <LineNumber>2</LineNumber>
            <Price>300</Price>
            <Description>Basic keyboard</Description>
         </Line>
      </Lines>
   </Document>
</Documents>

I would like to group the document lines to the XML, but my problem is that I can’t find a working method in the for-each line section.

XSLT I tried:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:exsl="http://exslt.org/common" extension-element-prefixes="exsl">
    <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
    <xsl:key name="k1" match="row" use="cell[1]"/>
    <xsl:template match="/">
        <!-- tokenize csv -->
        <xsl:variable name="rows">
            <xsl:call-template name="tokenize">
                <xsl:with-param name="text" select="root"/>
            </xsl:call-template>
        </xsl:variable>
        <xsl:variable name="data">
            <xsl:for-each select="exsl:node-set($rows)/row[position() > 0]">
                <row>
                    <xsl:call-template name="tokenize">
                        <xsl:with-param name="text" select="."/>
                        <xsl:with-param name="delimiter" select="';'"/>
                        <xsl:with-param name="name" select="'cell'"/>
                    </xsl:call-template>
                </row>
            </xsl:for-each>
        </xsl:variable>
        <!-- output -->
        <Documents>
            <xsl:for-each select="exsl:node-set($data)/row[cell[1] = 0]">
                <Document>
                    <Header>
                        <Customer>
                            <xsl:value-of select="cell[3]"/>
                        </Customer>
                        <Date>
                            <xsl:value-of select="cell[2]"/>
                        </Date>
                    </Header>
                    <Lines>
                        <xsl:for-each select="exsl:node-set($data)/row[cell[1] > 0]">
                            <Line>
                                <LineNumber>
                                    <xsl:value-of select="cell[1]"/>
                                </LineNumber>
                                <Price>
                                    <xsl:value-of select="cell[3]"/>
                                </Price>
                                <Description>
                                    <xsl:value-of select="cell[2]"/>
                                </Description>
                            </Line>
                        </xsl:for-each>
                    </Lines>
                </Document>
            </xsl:for-each>
        </Documents>
    </xsl:template>
    <xsl:template name="tokenize">
        <xsl:param name="text"/>
        <xsl:param name="delimiter" select="'&#10;'"/>
        <xsl:param name="name" select="'row'"/>
        <xsl:variable name="token" select="substring-before(concat($text, $delimiter), $delimiter)"/>
        <xsl:if test="$token">
            <xsl:element name="{$name}">
                <xsl:value-of select="$token"/>
            </xsl:element>
        </xsl:if>
        <xsl:if test="contains($text, $delimiter)">
            <!-- recursive call -->
            <xsl:call-template name="tokenize">
                <xsl:with-param name="text" select="substring-after($text, $delimiter)"/>
                <xsl:with-param name="delimiter" select="$delimiter"/>
                <xsl:with-param name="name" select="$name"/>
            </xsl:call-template>
        </xsl:if>
    </xsl:template>
</xsl:stylesheet>

Unfortunately this template will not stop with the lines before the next "0" value. I don't need to sum the prices yet. If anyone has any ideas on how I could achieve this that would be greatly appreciated! Unfortunately I’m limited to the XSLT 1.0 version.

CodePudding user response：

How about:

XSLT 1.0

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

<xsl:key name="lines" match="row[not(starts-with(., '0;'))]" use="generate-id(preceding-sibling::row[starts-with(., '0;')][1])" />

<xsl:template match="/">
    <!-- tokenize csv -->
    <xsl:variable name="rows">
        <xsl:call-template name="tokenize">
            <xsl:with-param name="text" select="/root"/>
        </xsl:call-template>
    </xsl:variable>
    <!-- output -->
    <Documents>
        <xsl:for-each select="exsl:node-set($rows)/row[starts-with(., '0;')]"> 
            <Document>
                <Header>
                    <Customer>
                        <xsl:value-of select="substring-before(substring-after(., ';'), ';')"/>
                    </Customer>
                    <Date>
                        <xsl:value-of select="substring-after(substring-after(., ';'), ';')"/>
                    </Date>
                </Header>
                <Lines>
                    <xsl:for-each select="key('lines', generate-id())"> 
                        <Line>
                            <LineNumber>
                                <xsl:value-of select="substring-before(., ';')"/>
                            </LineNumber>
                            <Price>
                                <xsl:value-of select="substring-after(substring-after(., ';'), ';')"/>
                            </Price>
                            <Description>
                                <xsl:value-of select="substring-before(substring-after(., ';'), ';')"/>
                            </Description>
                        </Line>
                    </xsl:for-each>
                </Lines>
            </Document>
         </xsl:for-each>
    </Documents>    
</xsl:template>

<xsl:template name="tokenize">
    <xsl:param name="text"/>
    <xsl:param name="delimiter" select="'&#10;'"/>
    <xsl:variable name="token" select="substring-before(concat($text, $delimiter), $delimiter)" />
    <xsl:if test="$token">
        <row>
            <xsl:value-of select="$token"/>
        </row>
    </xsl:if>
    <xsl:if test="contains($text, $delimiter)">
        <!-- recursive call -->
        <xsl:call-template name="tokenize">
            <xsl:with-param name="text" select="substring-after($text, $delimiter)"/>
        </xsl:call-template>
    </xsl:if>
</xsl:template>

</xsl:stylesheet>

If you like, you could further tokenize each row to cells - but with only 3 cells per row it's not really necessary.