2

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

kapil.murarkar
  • 247
  • 1
  • 3
  • 6

3 Answers3

1

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
Dimitre Novatchev
  • 240,661
  • 26
  • 293
  • 431
  • Thank you Dimitre Novatchev. This gives the solution perfectly . However, I have not completely understood the source code, but I will work upon the understanding. – kapil.murarkar Dec 26 '11 at 16:37
  • Hello , I wish to extend the xml . I want to enhance the requirement such that if there are siblings then I should use separator as ",". For all other cases the separator shuld be "|" or "newline". – kapil.murarkar Dec 31 '11 at 18:44
  • @kapil.murarkar: You may ask a new question. Adding new requirements within a comment isn't a better practice than asking a question. Your initial question was answered completely 8 days ago and it would be appropriate to consider accepting the best answer. – Dimitre Novatchev Dec 31 '11 at 18:48
0

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

collapsar
  • 17,010
  • 4
  • 35
  • 61
  • Thank you very much . The solution works perfectly fine. I will study the code and understand as I am beginner. Will write back in case of any queries. – kapil.murarkar Dec 26 '11 at 16:48
  • you're welcome. i shall be glad to answer an open questions. bets regards, carsten – collapsar Dec 26 '11 at 23:43
  • Hello , I wish to extend the xml . I want to enhance the requirement such that "only" if there are siblings then I should use separator as ",". For all other cases the separator should be "|" or "newline". The current solutions seems to hard-code the level .i.e. the grandchildren of Line is considered . However of there are great grandchildren or great-great-granchilderen ... till maybe 7 generations of Line then how do i come across a generic solution. – kapil.murarkar Dec 31 '11 at 18:51
0

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>
Martin Honnen
  • 160,499
  • 6
  • 90
  • 110