Flatten XML using XSLT but based on nesting level

Question

I'm new to XSLT and I'm trying to write some XSLT that will flatten any given XML such that a new line occurs whenever the nesting level changes. My input can be any XML document, with any number of nested levels so the structure isn't known to the XSLT. Due to the tools available to me, my solution has to use XSLT version 1.0.

For example.

<?xml version="1.0"?>
<ROWSET>
  <ROW>
    <CUSTOMER_ID>0</CUSTOMER_ID>
    <NAME>Default Company</NAME>
    <BONUSES>
      <BONUSES_ROW>
        <BONUS_ID>21</BONUS_ID>
        <DESCRIPTION>Performance Bonus</DESCRIPTION>
      </BONUSES_ROW>
      <BONUSES_ROW>
        <BONUS_ID>26</BONUS_ID>
        <DESCRIPTION>Special Bonus</DESCRIPTION>
      </BONUSES_ROW>
    </BONUSES>
  </ROW>
  <ROW>
    <CUSTOMER_ID>1</CUSTOMER_ID>
    <NAME>Dealer 1</NAME>
    <BONUSES>
      <BONUSES_ROW>
        <BONUS_ID>27</BONUS_ID>
        <DESCRIPTION>June Bonus</DESCRIPTION>
        <BONUS_VALUES>
          <BONUS_VALUES_ROW>
            <VALUE>10</VALUE>
            <PERCENT>N</PERCENT>
          </BONUS_VALUES_ROW>
          <BONUS_VALUES_ROW>
            <VALUE>11</VALUE>
            <PERCENT>Y</PERCENT>
          </BONUS_VALUES_ROW>
        </BONUS_VALUES>
      </BONUSES_ROW>
    </BONUSES>
  </ROW>
</ROWSET>

needs to becomes....

0, Default Company
21, Performance Bonus
26, Special Bonus
1, Dealer 1
27, June Bonus
10, N
11, Y

The XSLT I've written so far is...

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="text" encoding="iso-8859-1"/>
  <xsl:strip-space elements="*" />
  <xsl:template match="/*/child::*">
    <xsl:apply-templates select="*"/> 
  </xsl:template>
   <xsl:template match="*">
     <xsl:value-of select="text()" />
     <xsl:if test="position()!= last()"><xsl:text>,</xsl:text></xsl:if>
     <xsl:if test="position()= last()"><xsl:text>&#xD;</xsl:text></xsl:if>     
     <xsl:apply-templates select="./child::*"/>          
   </xsl:template>
</xsl:stylesheet>

but my output just isn't correct, with gaps and unnecessary data.

0,Default Company,
,21,Performance Bonus

26,Special Bonus
1,Dealer 1,

27,June Bonus,
,10,N

11,Y

It seems there needs to be a check as whether or not a node can contain text, but I'm stuck and could do with an XSLT expert's help.

Daniel Haley · Accepted Answer · 2013-06-30 02:10:12Z

2

You can test to see if an element has text by doing: *[text()]

Example...

XML Input

<ROWSET>
    <ROW>
        <CUSTOMER_ID>0</CUSTOMER_ID>
        <NAME>Default Company</NAME>
        <BONUSES>
            <BONUSES_ROW>
                <BONUS_ID>21</BONUS_ID>
                <DESCRIPTION>Performance Bonus</DESCRIPTION>
            </BONUSES_ROW>
            <BONUSES_ROW>
                <BONUS_ID>26</BONUS_ID>
                <DESCRIPTION>Special Bonus</DESCRIPTION>
            </BONUSES_ROW>
        </BONUSES>
    </ROW>
    <ROW>
        <CUSTOMER_ID>1</CUSTOMER_ID>
        <NAME>Dealer 1</NAME>
        <BONUSES>
            <BONUSES_ROW>
                <BONUS_ID>27</BONUS_ID>
                <DESCRIPTION>June Bonus</DESCRIPTION>
                <BONUS_VALUES>
                    <BONUS_VALUES_ROW>
                        <VALUE>10</VALUE>
                        <PERCENT>N</PERCENT>
                    </BONUS_VALUES_ROW>
                    <BONUS_VALUES_ROW>
                        <VALUE>11</VALUE>
                        <PERCENT>Y</PERCENT>
                    </BONUS_VALUES_ROW>
                </BONUS_VALUES>
            </BONUSES_ROW>
        </BONUSES>
    </ROW>
</ROWSET>

XSLT 1.0

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

    <xsl:template match="*[text()]">
        <xsl:if test="position() > 1">
            <xsl:text>, </xsl:text>
        </xsl:if>
        <xsl:value-of select="."/>
        <xsl:if test="not(following-sibling::*[text()])">
            <xsl:text>&#xA;</xsl:text>          
        </xsl:if>
    </xsl:template>

</xsl:stylesheet>

Text Output

0, Default Company
21, Performance Bonus
26, Special Bonus
1, Dealer 1
27, June Bonus
10, N
11, Y

Also, take a look at Built-in Template Rules to see what makes this stylesheet work with only one template.

EDIT

This stylesheet will also output a comma for empty elements:

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

    <xsl:template match="*[text() or not(*)]">
        <xsl:if test="position() > 1">
            <xsl:text>, </xsl:text>
        </xsl:if>
        <xsl:value-of select="."/>
        <xsl:if test="not(following-sibling::*[text() or not(*)])">
            <xsl:text>&#xA;</xsl:text>          
        </xsl:if>
    </xsl:template>

</xsl:stylesheet>

edited Jun 30, 2013 at 2:10

answered Jun 28, 2013 at 18:36

Daniel Haley

53.1k7 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

paulzip Over a year ago

Thanks Daniel, that's a very elegant solution and achieves exactly what I wanted. I read the Built-in Template Rules link thanks. One more question, if I wanted to still output a comma for empty nodes that could contain text but don't, how would I do it? e.g. <ID>1</ID><NAME>Jo Smith</NAME><JOB_TITLE/><DOB>1980-12-01<DOB> => 1, Jo Smith, , 1980-12-01

Daniel Haley Over a year ago

@user2532750 - I think the only way to do that is to assume that any element that does not have a child could contain text. The way to test that is not(*) or not(node()). I've added an updated stylesheet as an example.

Daniel Haley Over a year ago

I used * instead of node() because if an element contains only comments/processing instructions, you most likely want to treat it as empty.

Manoj · Accepted Answer · 2013-06-29 10:18:46Z

0

Check or validate your XML.

In your XML, Start Node <ROWSET> does not end with </ROWSET>

If  <ROWSET> node ends with </ROWSET> then, following XSL code will work for your expectation output

<xsl:template match="ROWSET">
    <xsl:for-each select="ROW">
        <xsl:value-of select="CUSTOMER_ID"/>, <xsl:value-of select="NAME"/><xsl:text>&#10;</xsl:text>

        <xsl:for-each select="BONUSES/BONUSES_ROW">
            <xsl:value-of select="BONUS_ID"/>, <xsl:value-of select="DESCRIPTION"/><xsl:text>&#10;</xsl:text>
            <xsl:variable name="cnt" select="count(BONUS_VALUES/BONUS_VALUES_ROW)"/>
            <xsl:if test="$cnt &gt; 0">
                <xsl:for-each select="BONUS_VALUES/BONUS_VALUES_ROW">
                    <xsl:value-of select="VALUE"/>, <xsl:value-of select="PERCENT"/><xsl:text>&#10;</xsl:text>
                </xsl:for-each>
            </xsl:if>
        </xsl:for-each>
    </xsl:for-each>
</xsl:template>

edited Jun 29, 2013 at 10:18

answered Jun 29, 2013 at 9:54

Manoj

3292 gold badges6 silver badges15 bronze badges

1 Comment

paulzip Over a year ago

Thanks, but I did ask for a generic solution, that would work with any XML.

Collectives™ on Stack Overflow

Flatten XML using XSLT but based on nesting level

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related