Search code examples
xslt

XSLT 1.0 template How to retrieve the values from a second occurrence after logic to skip second occurrence altogether

I have the following XML:

<DocumentElement>
<Sheet1>
<FIELD_1>HD</FIELD_1>
<FIELD_2>2024</FIELD_2>
<FIELD_3>90655</FIELD_3>
<FIELD_13>667</FIELD_13>
</Sheet1>
<Sheet1>
<FIELD_1>DT</FIELD_1>
<FIELD_2>496</FIELD_2>
<FIELD_13>0339618701316335</FIELD_13>
<FIELD_22>DMax</FIELD_22>
<FIELD_30>8.41</FIELD_30>
<FIELD_65>0.16</FIELD_65>
</Sheet1>
<Sheet1>
<FIELD_1>DT</FIELD_1>
<FIELD_2>496</FIELD_2>
<FIELD_13>0339618701316335</FIELD_13>
<FIELD_22>Sign</FIELD_22>
<FIELD_30>2.50</FIELD_30>
</Sheet1>
</DocumentElement>

The following stylesheet is creating the XML that follows:

<xsl:stylesheet xmlns:xsl='http://www.w3.org/1999/XSL/Transform' xmlns:msxsl='urn:schemas-microsoft-com:xslt' xmlns:var='urn:var' xmlns:user='urn:user' exclude-result-prefixes='msxsl var user'  version='1.0'>
  <xsl:output method="xml" indent="yes"/>  
  <xsl:template match="/DocumentElement">
    <DocumentElement>
      <xsl:choose>
        <xsl:when test="normalize-space(/DocumentElement/Sheet1[1]/FIELD_1) = 'HD' and normalize-space(/DocumentElement/Sheet1[2]/FIELD_1) = 'DT'">
          <xsl:apply-templates select="Sheet1[normalize-space(FIELD_1) = 'HD' and normalize-space(FIELD_2) != 'CusId' and normalize-space(FIELD_2) != 'CusID']" />
        </xsl:when>
        <xsl:otherwise>
          <xsl:apply-templates select="Sheet1[number(FIELD_2) = FIELD_2 and normalize-space(FIELD_1) != 'DT']" />
        </xsl:otherwise>

      </xsl:choose>
    </DocumentElement>
  </xsl:template>
  <xsl:template priority="1"  match="Sheet1[normalize-space(FIELD_1) = 'HD']">
    <Invoice>
      <Ships>
        <xsl:variable name="prevC" select="count(preceding-sibling::*[normalize-space(FIELD_1) = 'DT' and FIELD_2 != 'CustomerId' and FIELD_2 != 'TrackingNbr'])"/>
        <xsl:variable name="newC" select="count(following-sibling::*[normalize-space(FIELD_1) = 'HD'][1]/preceding-sibling::*[normalize-space(FIELD_1) = 'DT' and FIELD_2 != 'CustomerId' and FIELD_2 != 'TrackingNbr'])"/>
        <xsl:variable name="pos" select="count(following-sibling::*[ normalize-space(FIELD_1) = 'HD'][1])" />
        <xsl:apply-templates select="following-sibling::*[normalize-space(FIELD_1) = 'DT'][position() &lt; ($newC - $prevC + 1) or $pos = 0]"   >
          <xsl:with-param name="data" select="FIELD_3" />
        </xsl:apply-templates>
      </Ships>
    </Invoice>
  </xsl:template>
  <xsl:template priority="1" match="Sheet1[normalize-space(FIELD_1) = 'DT']" >
    <xsl:param name="scac"/>
    
      <Ship>
        <Lines>
          <Line>
    <xsl:attribute name="ReferenceNumber" >
    <xsl:value-of select="normalize-space(FIELD_13)"/>
    </xsl:attribute>

            <LineCharge >
              <xsl:attribute name="Charge" >
                <xsl:choose>
                  <xsl:when test="normalize-space(FIELD_22) = 'Sign'">
                    <xsl:value-of select="'TS5'"/>
                  </xsl:when>
                  <xsl:otherwise>
                    <xsl:value-of select="'TS0'"/>
                  </xsl:otherwise>
                </xsl:choose>
              </xsl:attribute>
              <xsl:attribute name="Amount" >
                <xsl:value-of select="format-number(translate(FIELD_30,'$,()',''),'###0.00')"/>
              </xsl:attribute>
            </LineCharge>
            
          </Line>
        </Lines>
      </Ship>
  </xsl:template>
</xsl:stylesheet>

XML OUTPUT:

<DocumentElement>
<Invoice>
<Ships>
<Ship>
<Lines>
<Line ReferenceNumber="0339618701316335">
<LineCharge Charge="TS0" Amount="8.41"/>
<LineCharge Charge="TS2" Amount="0.16"/>
</Line>
</Lines>
</Ship>
<Ship>
<Lines>
<Line ReferenceNumber="0339618701316335">
<LineCharge Charge="TS0" Amount="2.50"/>
</Line>
</Lines>
</Ship>
</Ships>
</Invoice>
</DocumentElement>

The first goal: Create one Ship element whenever there are multiple Sheet1 elements with identical values in FIELD_13. The following if statement was added above in the code to prevent the second Ship element from being captured:

<xsl:if test="not(preceding-sibling::*[normalize-space(FIELD_1) = 'DT' and FIELD_13 = current()/FIELD_13])">

NEW OUTPUT shows only one Ship element, which is correct:

<DocumentElement>
<Invoice>
<Ships>
<Ship>
<Lines>
<Line ReferenceNumber="0339618701316335">
<LineCharge Charge="TS0" Amount="8.41"/>
</Line>
</Lines>
</Ship>
</Ships>
</Invoice>
</DocumentElement>

The new issue is that the second LineCharge element is now skipped.

<LineCharge Charge="TS0" Amount="2.50"/>

I still need the 2nd LineCharge element that was nested in the second Ship element, but this time, as a sub element in the first Ship element, as seen below:

<DocumentElement>
<Invoice>
<Ships>
<Ship>
<Lines>
<Line ReferenceNumber="0339618701316335">
<LineCharge Charge="TS0" Amount="8.41"/>
<LineCharge Charge="TS5" Amount="2.50"/>
</Line>
</Lines>
</Ship>
</Ships>
</Invoice>
</DocumentElement>

How do I achieve this final XML?

I appreciate any assistance with this.

Solution

  • Here is an example of Muenchian grouping that may get you started:

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

<xsl:key name="key1" match="Sheet1[FIELD_1='DT']" use="FIELD_13"/>

<xsl:template match="/DocumentElement">
    <DocumentElement>
        <Invoice>
            <Ships>
                <!-- group by FIELD_13 -->
                <xsl:for-each select="Sheet1[FIELD_1='DT'][generate-id()=generate-id(key('key1', FIELD_13)[1])]">
                    <Ship>
                        <Lines>
                            <Line ReferenceNumber="{FIELD_13}">
                                <!-- process current group -->
                                <xsl:for-each select="key('key1', FIELD_13)">
                                    <LineCharge Charge="" Amount="{FIELD_30}"/>
                                </xsl:for-each>
                            </Line>
                        </Lines>
                    </Ship>
                </xsl:for-each>
            </Ships>
        </Invoice>
    </DocumentElement>
</xsl:template>

</xsl:stylesheet>

    With XML input of:

    <DocumentElement>
  <Sheet1>
    <FIELD_1>HD</FIELD_1>
    <FIELD_13>100</FIELD_13>
  </Sheet1>
  <Sheet1>
    <FIELD_1>DT</FIELD_1>
    <FIELD_13>101</FIELD_13>
    <FIELD_30>8.41</FIELD_30>
  </Sheet1>
  <Sheet1>
    <FIELD_1>DT</FIELD_1>
    <FIELD_13>101</FIELD_13>
    <FIELD_30>2.50</FIELD_30>
  </Sheet1>
  <Sheet1>
    <FIELD_1>DT</FIELD_1>
    <FIELD_13>102</FIELD_13>
    <FIELD_30>5.62</FIELD_30>
  </Sheet1>
  <Sheet1>
    <FIELD_1>DT</FIELD_1>
    <FIELD_13>103</FIELD_13>
    <FIELD_30>4.59</FIELD_30>
  </Sheet1>
  <Sheet1>
    <FIELD_1>DT</FIELD_1>
    <FIELD_13>103</FIELD_13>
    <FIELD_30>9.97</FIELD_30>
  </Sheet1>
</DocumentElement>

    you will get:

    <?xml version="1.0"?>
<DocumentElement>
  <Invoice>
    <Ships>
      <Ship>
        <Lines>
          <Line ReferenceNumber="101">
            <LineCharge Charge="" Amount="8.41"/>
            <LineCharge Charge="" Amount="2.50"/>
          </Line>
        </Lines>
      </Ship>
      <Ship>
        <Lines>
          <Line ReferenceNumber="102">
            <LineCharge Charge="" Amount="5.62"/>
          </Line>
        </Lines>
      </Ship>
      <Ship>
        <Lines>
          <Line ReferenceNumber="103">
            <LineCharge Charge="" Amount="4.59"/>
            <LineCharge Charge="" Amount="9.97"/>
          </Line>
        </Lines>
      </Ship>
    </Ships>
  </Invoice>
</DocumentElement>