XSLT sort using multiple columns in XML

Question

My XML looks like -

    <?xml version='1.0' encoding='UTF-8'?>
<root>
    <row>
        <col1>Plan ID</col1>
        <col2>472064672</col2>
        <col3>18007</col3>
        <col4>1</col4>
        <col5>MARGARET</col5>
        <col6>PRESLEY</col6>
        <col7>20161216</col7>
        <col8/>
        <col9/>
        <col10>Pre-Tax </col10>
        <col11/>
        <col12>750</col12>
        <col13/>
        <col14/>
        <col15/>
        <col16/>
        <col17/>
        <col18/>
        <col19/>
        <col20/>
        <col21/>
        <col22/>
        <col23/>
        <col24/>
        <col25/>
        <col26/>
        <col27/>
        <col28> </col28>
        <col29/>
        <col30>20180501</col30>
        <col31>2</col31>
        <col32/>
        <col33/>
        <col34/>
    </row>
    <row>
        <col1>Plan ID</col1>
        <col2>472064672</col2>
        <col3>18007</col3>
        <col4>1</col4>
        <col5>MARGARET</col5>
        <col6>PRESLEY</col6>
        <col7>20161216</col7>
        <col8/>
        <col9/>
        <col10>Pre-Tax Defrl%</col10>
        <col11>0</col11>
        <col12/>
        <col13/>
        <col14/>
        <col15/>
        <col16/>
        <col17/>
        <col18/>
        <col19/>
        <col20/>
        <col21/>
        <col22/>
        <col23/>
        <col24/>
        <col25/>
        <col26/>
        <col27/>
        <col28> </col28>
        <col29/>
        <col30>20180501</col30>
        <col31>2</col31>
        <col32/>
        <col33/>
        <col34/>
    </row>
    <row>
        <col1>Plan ID</col1>
        <col2>214196103</col2>
        <col3/>
        <col4>1</col4>
        <col5>PETER</col5>
        <col6>JOHNSON</col6>
        <col7/>
        <col8/>
        <col9/>
        <col10>Pre-Tax </col10>
        <col11/>
        <col12>0</col12>
        <col13/>
        <col14/>
        <col15/>
        <col16/>
        <col17/>
        <col18/>
        <col19/>
        <col20/>
        <col21/>
        <col22/>
        <col23/>
        <col24/>
        <col25/>
        <col26/>
        <col27/>
        <col28> </col28>
        <col29/>
        <col30>20180502</col30>
        <col31>2</col31>
        <col32/>
        <col33/>
        <col34/>
    </row>
    <row>
        <col1>Plan ID</col1>
        <col2>214196103</col2>
        <col3/>
        <col4>1</col4>
        <col5>PETER</col5>
        <col6>JOHNSON</col6>
        <col7/>
        <col8/>
        <col9/>
        <col10>Pre-Tax Defrl%</col10>
        <col11>16.29</col11>
        <col12/>
        <col13/>
        <col14/>
        <col15/>
        <col16/>
        <col17/>
        <col18/>
        <col19/>
        <col20/>
        <col21/>
        <col22/>
        <col23/>
        <col24/>
        <col25/>
        <col26/>
        <col27/>
        <col28> </col28>
        <col29/>
        <col30>20180502</col30>
        <col31>2</col31>
        <col32/>
        <col33/>
        <col34/>
    </row>
</root>

I want to sort this XML such that for each group of col2 value, the xml should be sorted such that if col11 has 0 value and col12 is blank, then the row which has col11 or col12 as 0 comes first and then the ones with a non zero value in col11 or col12.

So my output will look like -

    <root>
      <row>
        <col1>666919</col1>
        <col2>214196103</col2>
        <col3/>
        <col4>1</col4>
        <col5>PETER</col5>
        <col6>JOHNSON</col6>
        <col7/>
        <col8/>
        <col9/>
        <col10>Pre-Tax </col10>
        <col11/>
        <col12>0</col12>
        <col13/>
        <col14/>
        <col15/>
        <col16/>
        <col17/>
        <col18/>
        <col19/>
        <col20/>
        <col21/>
        <col22/>
        <col23/>
        <col24/>
        <col25/>
        <col26/>
        <col27/>
        <col28> </col28>
        <col29/>
        <col30>20180502</col30>
        <col31>2</col31>
        <col32/>
        <col33/>
        <col34/>
    </row>
    <row>
        <col1>666919</col1>
        <col2>214196103</col2>
        <col3/>
        <col4>1</col4>
        <col5>PETER</col5>
        <col6>JOHNSON</col6>
        <col7/>
        <col8/>
        <col9/>
        <col10>Pre-Tax Defrl%</col10>
        <col11>16.29</col11>
        <col12/>
        <col13/>
        <col14/>
        <col15/>
        <col16/>
        <col17/>
        <col18/>
        <col19/>
        <col20/>
        <col21/>
        <col22/>
        <col23/>
        <col24/>
        <col25/>
        <col26/>
        <col27/>
        <col28> </col28>
        <col29/>
        <col30>20180502</col30>
        <col31>2</col31>
        <col32/>
        <col33/>
        <col34/>
    </row>
    <row>
        <col1>666919</col1>
        <col2>472064672</col2>
        <col3>18007</col3>
        <col4>1</col4>
        <col5>MARGARET</col5>
        <col6>PRESLEY</col6>
        <col7>20161216</col7>
        <col8/>
        <col9/>
        <col10>Pre-Tax Defrl%</col10>
        <col11>0</col11>
        <col12/>
        <col13/>
        <col14/>
        <col15/>
        <col16/>
        <col17/>
        <col18/>
        <col19/>
        <col20/>
        <col21/>
        <col22/>
        <col23/>
        <col24/>
        <col25/>
        <col26/>
        <col27/>
        <col28> </col28>
        <col29/>
        <col30>20180501</col30>
        <col31>2</col31>
        <col32/>
        <col33/>
        <col34/>
    </row>
    <row>
        <col1>666919</col1>
        <col2>472064672</col2>
        <col3>18007</col3>
        <col4>1</col4>
        <col5>MARGARET</col5>
        <col6>PRESLEY</col6>
        <col7>20161216</col7>
        <col8/>
        <col9/>
        <col10>Pre-Tax </col10>
        <col11/>
        <col12>750</col12>
        <col13/>
        <col14/>
        <col15/>
        <col16/>
        <col17/>
        <col18/>
        <col19/>
        <col20/>
        <col21/>
        <col22/>
        <col23/>
        <col24/>
        <col25/>
        <col26/>
        <col27/>
        <col28> </col28>
        <col29/>
        <col30>20180501</col30>
        <col31>2</col31>
        <col32/>
        <col33/>
        <col34/>
    </row>
</root>

OP asks for multiple sort keys, not for sorting in general. May be have a look at xml.com/pub/a/2002/07/03/transform.html — Adrian W
– Adrian W, Commented May 31, 2018 at 21:09
Thanks Adrain, but I already looked at this post and was not able to find anything that would work for my case — VJain
– VJain, Commented Jun 1, 2018 at 4:35

Tim C · Accepted Answer · 2018-06-01 08:49:28Z

0

Try this XSLT. This checks if col11 is zero or empty then the boolean expression will be false, meaning it gets sorted first. And similarly for col12

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:output method="xml" />

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

  <xsl:template match="root">
    <xsl:copy>
        <xsl:apply-templates select="row">
            <xsl:sort select="col2" />
            <xsl:sort select="boolean(number(col11))" />
            <xsl:sort select="boolean(number(col12))" />
        </xsl:apply-templates>
    </xsl:copy>
  </xsl:template>
</xsl:stylesheet>

answered Jun 1, 2018 at 8:49

Tim C

70.7k14 gold badges77 silver badges94 bronze badges

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

Collectives™ on Stack Overflow

XSLT sort using multiple columns in XML

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related