Search code examples
xsltxslt-grouping

How to sort/select distinct in XSLT when creating a header/details XML

I have a file with a header record and one to many detail records. I need to create the same header record, along with the last detail record for a given policy number. How do I write the XSLT to both sort and select the first record?

I have tried placing a hard coded value in between the "for-each" statements within the XSLT to see where the code breaks. Anything placed after the second "for-each" doesn't display. I have searched high and low there to see if anyone can handle a header with multiple details, but I couldn't find an answer to this question.

This is what I'm receiving (simplified - there are a lot more fields):

<ns0:Root xmlns:ns0="http://CORE.BizTalk.Applications.Schemas.PolicyFF">
    <Header>
        <RecordType>RecordType</RecordType>
        <RecordID>RecordID</RecordID>
        <PolicyNumber>PolicyNumber</PolicyNumber>
    </Header>
    <Detail>
        <RecordType>POL</RecordType>
        <RecordID>1</RecordID>
        <PolicyNumber>ABC 0000018 00</PolicyNumber>
    </Detail>
    <Detail>
        <RecordType>POL</RecordType>
        <RecordID>2</RecordID>
        <PolicyNumber>DEF0000019</PolicyNumber>
    </Detail>
    <Detail>
        <RecordType>POL</RecordType>
        <RecordID>3</RecordID>
        <PolicyNumber>DEF0000019</PolicyNumber>
    </Detail>
    <Detail>
        <RecordType>POL</RecordType>
        <RecordID>3</RecordID>
        <PolicyNumber>ABC 0000018 00</PolicyNumber>
    </Detail>
</ns0:Root>

The output has the identical layout, but I only want to see this, since the RecordID is the last RecordID for both policies (sorted descending by RecordID for each policy):

<ns0:Root xmlns:ns0="http://CORE.BizTalk.Applications.Schemas.PolicyFF">
    <Header>
        <RecordType>RecordType</RecordType>
        <RecordID>RecordID</RecordID>
        <PolicyNumber>PolicyNumber</PolicyNumber>
    </Header>
    <Detail>
        <RecordType>POL</RecordType>
        <RecordID>3</RecordID>
        <PolicyNumber>DEF0000019</PolicyNumber>
    </Detail>
    <Detail>
        <RecordType>POL</RecordType>
        <RecordID>3</RecordID>
        <PolicyNumber>ABC 0000018 00</PolicyNumber>
    </Detail>
</ns0:Root>

This is the XSLT I'm using:

<?xml version="1.0" encoding="UTF-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" exclude-result-prefixes="msxsl var" version="1.0" xmlns:ns0="http://CORE.BizTalk.Applications.Schemas.PolicyFF">
    <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
    <xsl:key name="policy" match="Root/Detail" use="PolicyNumber" />
    <xsl:template match="/">
        <xsl:apply-templates select="/ns0:Root" />
    </xsl:template>
    <xsl:template match="/ns0:Root">
        <ns0:Root>
            <Header>
                <RecordType>
                    <xsl:value-of select="Header/RecordType/text()" />
                </RecordType>
                <RecordID>
                    <xsl:value-of select="Header/RecordID/text()" />
                </RecordID>
                <PolicyNumber>
                    <xsl:value-of select="Header/PolicyNumber/text()" />
                </PolicyNumber>
            </Header>
            <xsl:for-each select="Detail">
                <xsl:for-each select="Detail[generate-id() = generate-id(key('policy', PolicyNumber)[1])]">
                    <xsl:for-each select="key('policy', PolicyNumber)">
                        <xsl:sort order="descending" select="RecordID" data-type="text"/>
                        <xsl:if test="position() = 1">
                            <Detail>
                                <xsl:copy-of select="./*" />
                            </Detail>
                        </xsl:if>
                    </xsl:for-each>
                </xsl:for-each>
            </xsl:for-each>
        </ns0:Root>
    </xsl:template>
</xsl:stylesheet>

I am currently only getting the header record with the XSLT above.

@Martin-Honnen: I need to sort by RecordID descending. I took your first and second XSLTs and turned it into one, but I'm still only getting the header record. Suggestions?

<?xml version="1.0" encoding="UTF-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" exclude-result-prefixes="msxsl var" version="1.0" xmlns:ns0="http://CORE.BizTalk.Applications.PPPL.TrisuraReports.Schemas.PolicyFF">
    <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
    <xsl:key name="policy" match="Root/Detail" use="PolicyNumber" />
    <xsl:template match="@* | node()">
        <xsl:copy>
            <xsl:apply-templates select="@* | node()"/>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="Detail[not(generate-id() = generate-id(key('policy', PolicyNumber)[1]))]"/>

    <xsl:template match="Detail[generate-id() = generate-id(key('policy', PolicyNumber)[1])]">
        <xsl:for-each select="key('policy', PolicyNumber)">
            <xsl:sort select="RecordID" data-type="text" order="descending"/>
            <xsl:if test="position() = 1">
                <xsl:copy-of select="."/>
            </xsl:if>
        </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>
Solution

  • In XSLT 1, if "with the last detail record" means last in the input, I would simply use the identity transformation template plus a template preventing the copying of any Detail not being the last of its group defined by the key:

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

    <xsl:key name="policy" match="Detail" use="PolicyNumber" />

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

  <xsl:template match="Detail[not(generate-id() = generate-id(key('policy', PolicyNumber)[last()]))]"/>

</xsl:stylesheet>

    https://xsltfiddle.liberty-development.net/3NSSEuP

    If you need to sort by the RecordID then in one template for the first or last item of the group you can sort all items in the group and output the last in descending:

      <xsl:template match="Detail[not(generate-id() = generate-id(key('policy', PolicyNumber)[1]))]"/>

  <xsl:template match="Detail[generate-id() = generate-id(key('policy', PolicyNumber)[1])]">
      <xsl:for-each select="key('policy', PolicyNumber)">
          <xsl:sort select="RecordID" data-type="number" order="descending"/>
          <xsl:if test="position() = 1">
              <xsl:copy-of select="."/>
          </xsl:if>
      </xsl:for-each>
  </xsl:template>

    https://xsltfiddle.liberty-development.net/3NSSEuP/1 is the full online sample and has

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

    <xsl:key name="policy" match="Detail" use="PolicyNumber" />

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

  <xsl:template match="Detail[not(generate-id() = generate-id(key('policy', PolicyNumber)[1]))]"/>

  <xsl:template match="Detail[generate-id() = generate-id(key('policy', PolicyNumber)[1])]">
      <xsl:for-each select="key('policy', PolicyNumber)">
          <xsl:sort select="RecordID" data-type="number" order="descending"/>
          <xsl:if test="position() = 1">
              <xsl:copy-of select="."/>
          </xsl:if>
      </xsl:for-each>
  </xsl:template>

</xsl:stylesheet>