If there are multiple values in the node,then conc

Question

I am new to xslt programming and need a solution to a problem. I wish to transform xml file to csv text file. I will import this csv into an excel sheet.

In input xml file, If there are multiple values in the node,then concatenate these into a single string.

Input xml is as below.

    <?xml version="1.0" encoding="ISO-8859-1"?>

    <?xml-stylesheet type="text/xsl" href="ForumCsv.xsl"?>

    <Inventory>
      <Line>
       <LineNumber>line</LineNumber>
       <Description>desc</Description>
       <Matrix>quan</Matrix>
       <Matrix>quan1</Matrix> <!-- added -->
       <Date>date</Date>
      </Line>
      <Line>
       <LineNumber>1</LineNumber>
       <Description>Oak chairs</Description>
       <Matrix>5</Matrix>
       <Matrix>20</Matrix> <!-- added -->
       <Matrix>16</Matrix> <!-- added -->
       <Date>31 Dec 2004</Date>
      </Line>
      <Line>
       <LineNumber>2</LineNumber>
       <Description>Dining tables</Description>
       <Matrix>
        <SubComp>100</SubComp>
        <SubComp>300</SubComp>
        </Matrix>
       <Date>31 Dec 2004</Date>
      </Line>
      <Line>
       <LineNumber>3</LineNumber>
       <Description>Folding chairs</Description>
       <Matrix>4</Matrix>
       <Date>29 Dec 2004</Date>
      </Line>
      <Line>
       <LineNumber>4</LineNumber>
       <Description>Couch</Description>
       <Matrix>1</Matrix>
       <Date>31 Dec 2004</Date>
      </Line>
     </Inventory>

Expected Output is as below.

line|desc|quan,quan1|date
1|Oak chairs|5,20,16| Dec 2004
2|Dining tables|100,300|31 Dec 2004
3|Folding chairs|4|29 Dec 2004

The source code that I have written is given below.

    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="text" indent="yes" encoding="ISO-8859-1" omit-xml-declaration="yes"/>
    <xsl:strip-space  elements="*"/>

    <xsl:template name="Newline"><xsl:text>
    </xsl:text></xsl:template>

    <xsl:template match="Inventory">
      <xsl:apply-templates select="Line"/>
    </xsl:template>

    <xsl:template match="Line">
      <xsl:for-each select="*">  
        <!-- THIS IS WHERE I need help. I aim to put a test condition where I wish to identify sibling nodes .
             If sibling nodes are found then dont use '|', but use ';'
             Also I want to paramterize the delimiter

        <xsl:test ????? > 
           <xsl:value-of select="concat(substring(';',1,position()-1),.)"/> 
        </xsl:template>
        -->
       <xsl:value-of select="."/>      
        <xsl:if test="position() != last()">
        <xsl:value-of select="'|'"/>
       </xsl:if>
     </xsl:for-each>
      <xsl:text>&#xd;&#xa;</xsl:text>
     </xsl:template>

    </xsl:stylesheet>

The separators are "|" and ",". But I would like to paramaterize them.

Also the code should be generic. If more than one element is added the output should still be the same i.e. "|" or "," delimited. No hard coding of nodes

Answer 1

Here is an XSLT 2.0 solution you can use with XSLT 2.0 processors like Saxon 9 or AltovaXML or XQSharp:

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

  <xsl:param name="lf" select="'&#10;'"/>
  <xsl:param name="is" select="','"/>
  <xsl:param name="cs" select="'|'"/>

  <xsl:output method="text"/>
  <xsl:strip-space elements="*"/>

  <xsl:template match="/*/*">
    <xsl:if test="position() gt 1">
      <xsl:value-of select="$lf"/>
    </xsl:if>
    <xsl:for-each-group select="*" group-adjacent="node-name(.)">
      <xsl:if test="position() gt 1">
        <xsl:value-of select="$cs"/>
      </xsl:if>
      <xsl:value-of select="current-group()/descendant-or-self::*[not(*)]" separator="{$is}"/>
    </xsl:for-each-group>
  </xsl:template>

</xsl:stylesheet>

Answer 2

try this one:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" indent="yes" encoding="ISO-8859-1" omit-xml-declaration="yes"/>
<xsl:strip-space  elements="*"/>

<xsl:param name="csvsep" select="'|'"/>
<xsl:param name="datasep" select="','"/>


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

<xsl:template name="Newline"><xsl:text>
</xsl:text></xsl:template>


<xsl:template match="Line/*">
    <xsl:value-of select="concat(., $csvsep)"/>
</xsl:template>
<xsl:template match="Line/*[./*]">
    <xsl:apply-templates select="./*"/>
    <xsl:value-of select="$csvsep"/>
</xsl:template>

<xsl:template match="Line/*[position()=last()]">
    <xsl:value-of select="."/>
    <xsl:text>&#xd;&#xa;</xsl:text>
</xsl:template>
<xsl:template match="Line/*[position()=last() and ./*]">
    <xsl:apply-templates select="./*"/>
    <xsl:text>&#xd;&#xa;</xsl:text>
</xsl:template>

<xsl:template match="Line/*[name(./following-sibling::*)=name(.)]">
    <xsl:value-of select="concat(., $datasep)"/>      
</xsl:template>


<xsl:template match="Line/*/*">
    <xsl:value-of select="concat(., $datasep)"/>
</xsl:template>

<xsl:template match="Line/*/*[position()=last()]">
    <xsl:value-of select="."/>      
</xsl:template>



</xsl:stylesheet>

you may specify the separators as parameters to your stylesheet, they are preset to defaults .

basically all templates match against children or grandchildren of of the Line element. Sibling grandchildren are assumed to be part of the same csv field. The last element on any level is treated specially. note that the order of the templates is significant.

this solution works with xslt 1.0 processors.

hope this helps, carsten

Answer 3

This is a complete, shor and simple (push style, no explicit conditional instructions) XSLT 1.0 solution:

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

 <xsl:param name="pSubItemSeparator" select="','"/>
 <xsl:param name="pItemSeparator" select="'|'"/>

 <xsl:template match="Line">
  <xsl:apply-templates/>
  <xsl:text>&#xA;</xsl:text>
 </xsl:template>

 <xsl:template match="Line/*[1]">
  <xsl:value-of select="."/>
 </xsl:template>

 <xsl:template match=
  "Line/*[position() >1]
  |
   Line/*/*[1]
  ">
  <xsl:value-of select="concat($pItemSeparator, .)"/>
 </xsl:template>

  <xsl:template priority="2" match=
   "Line/*[name()
          =
           name(preceding-sibling::*[1])]
   |
    Line/*/*[position() > 1]
   ">
  <xsl:value-of select="concat($pSubItemSeparator, .)"/>
 </xsl:template>

 <xsl:template match="Line/*[*]">
  <xsl:apply-templates/>
 </xsl:template>
</xsl:stylesheet>

when applied on the provided XML document:

<Inventory>
    <Line>
        <LineNumber>line</LineNumber>
        <Description>desc</Description>
        <Matrix>quan</Matrix>
        <Matrix>quan1</Matrix>
        <!-- added -->
        <Date>date</Date>
    </Line>
    <Line>
        <LineNumber>1</LineNumber>
        <Description>Oak chairs</Description>
        <Matrix>5</Matrix>
        <Matrix>20</Matrix>
        <!-- added -->
        <Matrix>16</Matrix>
        <!-- added -->
        <Date>31 Dec 2004</Date>
    </Line>
    <Line>
        <LineNumber>2</LineNumber>
        <Description>Dining tables</Description>
        <Matrix>
            <SubComp>100</SubComp>
            <SubComp>300</SubComp>
        </Matrix>
        <Date>31 Dec 2004</Date>
    </Line>
    <Line>
        <LineNumber>3</LineNumber>
        <Description>Folding chairs</Description>
        <Matrix>4</Matrix>
        <Date>29 Dec 2004</Date>
    </Line>
    <Line>
        <LineNumber>4</LineNumber>
        <Description>Couch</Description>
        <Matrix>1</Matrix>
        <Date>31 Dec 2004</Date>
    </Line>
</Inventory>

the wanted, correct result is produced:

line|desc|quan,quan1|date
1|Oak chairs|5,20,16|31 Dec 2004
2|Dining tables|100,300|31 Dec 2004
3|Folding chairs|4|29 Dec 2004
4|Couch|1|31 Dec 2004