0

Been spending too much time on this.."f$%" Hope you can share of your experience. I have the source flat XML arriving from external db in the following structure:

 <?xml version="1.0" encoding="utf-8"?>
    <ns:MT_ActualCosts xmlns:ns="http://percite:scmaster/actual_costs">
        <row>
            <EVENT_ID>106</EVENT_ID>
            <LINE_NUMBER>0</LINE_NUMBER>
            <INVOICE_NUMBER>9999</INVOICE_NUMBER>
            <INVOICE_DATE>2015-02-09 00:00:00.0</INVOICE_DATE>
            <CURRENCY_CODE>USD</CURRENCY_CODE>
            <TOTAL_AMOUNT_NET>0001</TOTAL_AMOUNT_NET>
            <RO_NUMBER>102808</RO_NUMBER>
        </row>
        <row>
            <EVENT_ID>106</EVENT_ID>
            <LINE_NUMBER>1</LINE_NUMBER>
            <INVOICE_NUMBER>24444</INVOICE_NUMBER>
            <PLANT>0003</PLANT>
            <ALLOCATION_AMOUNT>122</ALLOCATION_AMOUNT>
        </row>
        <row>
            <EVENT_ID>109</EVENT_ID>
            <LINE_NUMBER>0</LINE_NUMBER>
            <INVOICE_NUMBER>24458</INVOICE_NUMBER>
            <INVOICE_DATE>2015-02-09 00:00:00.0</INVOICE_DATE>
            <CURRENCY_CODE>USD</CURRENCY_CODE>
            <TOTAL_AMOUNT_NET>0011</TOTAL_AMOUNT_NET>
            <RO_NUMBER>102813</RO_NUMBER>
        </row>
        <row>
            <EVENT_ID>109</EVENT_ID>
            <LINE_NUMBER>1</LINE_NUMBER>
            <INVOICE_NUMBER>24458</INVOICE_NUMBER>
            <PLANT>0003</PLANT>
            <ALLOCATION_AMOUNT>11.1</ALLOCATION_AMOUNT>
        </row>
        <row>
            <EVENT_ID>108</EVENT_ID>
            <LINE_NUMBER>0</LINE_NUMBER>
            <INVOICE_NUMBER>24535</INVOICE_NUMBER>
            <INVOICE_DATE>2015-02-19 00:00:00.0</INVOICE_DATE>
            <CURRENCY_CODE>USD</CURRENCY_CODE>
            <TOTAL_AMOUNT_NET>11</TOTAL_AMOUNT_NET>
            <RO_NUMBER>102811</RO_NUMBER>
        </row>
        <row>
            <EVENT_ID>108</EVENT_ID>
            <LINE_NUMBER>1</LINE_NUMBER>
            <INVOICE_NUMBER>24535</INVOICE_NUMBER>
            <PLANT>0002</PLANT>
            <ALLOCATION_AMOUNT>11</ALLOCATION_AMOUNT>
        </row>
        <row>
            <EVENT_ID>171</EVENT_ID>
            <LINE_NUMBER>0</LINE_NUMBER>
            <INVOICE_NUMBER>24645</INVOICE_NUMBER>
            <INVOICE_DATE>2015-02-28 00:00:00.0</INVOICE_DATE>
            <CURRENCY_CODE>USD</CURRENCY_CODE>
            <TOTAL_AMOUNT_NET>999999</TOTAL_AMOUNT_NET>
            <RO_NUMBER>103063</RO_NUMBER>
        </row>
        <row>
            <EVENT_ID>171</EVENT_ID>
            <LINE_NUMBER>1</LINE_NUMBER>
            <INVOICE_NUMBER>24645</INVOICE_NUMBER>
            <PLANT>0001</PLANT>
            <ALLOCATION_AMOUNT>11.47</ALLOCATION_AMOUNT>
        </row>
        <row>
            <EVENT_ID>171</EVENT_ID>
            <LINE_NUMBER>2</LINE_NUMBER>
            <INVOICE_NUMBER>24645</INVOICE_NUMBER>
            <PLANT>0001</PLANT>
            <ALLOCATION_AMOUNT>11.53</ALLOCATION_AMOUNT>
        </row>
    </ns:MT_ActualCosts>


My requested Target Structure should be something like this:

[![enter image description here][1]][1]

I need to group under the Header segments RecordLine Segments of the same `EVENT_ID`.

Currently my XSLT can't create the needed structure.
this is my XSLT:

    <?xml version="1.0" encoding="UTF-8"?>
    <xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ns0="http://percite:scmaster/actual_costs">
    <xsl:output method="xml" indent="yes"/>

    <!-- xsi:noNamespaceSchemaLocation="\\palnt03\palramnet-redirect$\IL-Users\My-Documents\nimrod_g\SAP\Projects\ScMaster\Finance\ActualCosts\development\xsd"> -->
    <xsl:template match="/">
    <ns0:MT_ActualCostPreNormalized>
    <xsl:for-each select= "ns0:MT_ActualCosts/row">
        <xsl:if test="LINE_NUMBER=0">
            <Header>
            <EVENT_ID><xsl:value-of select="EVENT_ID"></xsl:value-of>
            </EVENT_ID>
            <LINE_NUMBER>
            <xsl:value-of select="LINE_NUMBER"/>
            </LINE_NUMBER>
            <INVOICE_NUMBER>
                    <xsl:value-of select="INVOICE_NUMBER"/>
                    </INVOICE_NUMBER>
                    <CURRENCY_CODE>
                    <xsl:value-of select="CURRENCY_CODE"/>
                    </CURRENCY_CODE>
                    <TOTAL_AMOUNT_NET>
                    <xsl:value-of select="TOTAL_AMOUNT_NET"/>
                    </TOTAL_AMOUNT_NET>
                    <RO_NUMBER>
                    <xsl:value-of select="RO_NUMBER"/>
                    </RO_NUMBER>
                    <PLANT>
                    <xsl:value-of select="PLANT"/>
                    </PLANT>
                    <ALLOCATION_AMOUNT>
                    <xsl:value-of select="ALLOCATION_AMOUNT"/>
                    </ALLOCATION_AMOUNT>
            </Header>
        </xsl:if>
                <xsl:if test="LINE_NUMBER!=0">
                        <RecordLine>
                        <EVENT_ID><xsl:value-of select="EVENT_ID"></xsl:value-of>
                        </EVENT_ID>
                        <LINE_NUMBER>
                        <xsl:value-of select="LINE_NUMBER"/>
                        </LINE_NUMBER>
                        <INVOICE_NUMBER>
                        <xsl:value-of select="INVOICE_NUMBER"/>
                        </INVOICE_NUMBER>
                        <CURRENCY_CODE>
                        <xsl:value-of select="CURRENCY_CODE"/>
                        </CURRENCY_CODE>
                        <TOTAL_AMOUNT_NET>
                        <xsl:value-of select="TOTAL_AMOUNT_NET"/>
                        </TOTAL_AMOUNT_NET>
                        <RO_NUMBER>
                        <xsl:value-of select="RO_NUMBER"/>
                        </RO_NUMBER>
                        <PLANT>
                        <xsl:value-of select="PLANT"/>
                        </PLANT>
                        <ALLOCATION_AMOUNT>
                        <xsl:value-of select="ALLOCATION_AMOUNT"/>
                        </ALLOCATION_AMOUNT>
                        </RecordLine>
                </xsl:if>
        </xsl:for-each>
    </ns0:MT_ActualCostPreNormalized>
    </xsl:template>
    </xsl:stylesheet> 


  [1]: https://i.sstatic.net/yklFq.jpg

After implemenitng most of the code provided by Daniel Haley it seems like the solution is very near. Follwoing Martin's request I am adding here the current XSLT used+the current result XML. I will try and clerify my rules for the XSLT program: 1.Each record from the source XML should be analyzed as followed: if LINE_NUMBER = 0 then this is a header record and should produce a segment**.(it shouldnt be added to the segment )** - if LINE_NUMBER is > 0 and its EVENT_ID equals to the row produced the segment the this is a RecordLine in the context of the same segment and should open a segment under the same ." Few problems can be seen in the result xml:

  1. The first 4 groups of Headers-RecordLines comes out nicley (EventIDs 100 to 104) .but on EVENT_ID 105 something went wrong. it can be see that the row with LINE_NUMBER=0 created a Header segment but also added the data twice inside a record line. this is wrong result.
  2. from this point on, all the records are getting messed up. Currently I can't say why this happened. I have added part of the source XML (as image) as comming from our testing db. I have added part of the result XML (as image)Hope these notes will clearify the issue.

Source XML part with event 105

Result XML part with event 105

Improve this question

2 Answers 2

Reset to default
2

As you write that you want to group elements I guess as a starting point you can use for-each-group instead of for-each:

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0"
xmlns:ns0="http://percite:scmaster/actual_costs">

    <xsl:output indent="yes"/>

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

    <xsl:template match="/*">
        <ns0:MT_ActualCostPreNormalized>
          <xsl:for-each-group select="row" group-by="EVENT_ID">
              <Header>
                  <xsl:apply-templates select="EVENT_ID, LINE_NUMBER, current-group()"/>
              </Header>
          </xsl:for-each-group>
        </ns0:MT_ActualCostPreNormalized>
    </xsl:template>

    <xsl:template match="row">
        <RecordLine>
            <xsl:apply-templates/>
        </RecordLine>
    </xsl:template>
</xsl:transform>

You can list further elements you want to have below Header in the apply-templates.

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

5 Comments

Hi Martin, thanks for your code. How can I eliminate rows with LINE_NUMBER=0 as they are representing header segments which I don’t want to include in my RecordLines. (to maintain data integrity)
If you don't want to group them at all change <xsl:for-each-group select="row" group-by="EVENT_ID"> to <xsl:for-each-group select="row[not(LINE_NUMBER = 0)]" group-by="EVENT_ID">. If you want to group them by EVENT_ID but don't want to include them in the Header element as RecordLine, then change <xsl:apply-templates select="EVENT_ID, LINE_NUMBER, current-group()"/> to <xsl:apply-templates select="EVENT_ID, LINE_NUMBER, current-group()[not(LINE_NUMBER = 0)]"/>
Hi,I believe the casue for the duplication is the <xsl:template match="row"> <RecordLine> <xsl:apply-templates/> </RecordLine> </xsl:template> it duplicates the LINE_NUMBER "0" that represents the headers into the RecordLine as well.
In that case you can change the inner <xsl:apply-templates/> to <xsl:apply-templates select="* except LINE_NUMBER"/> or <xsl:apply-templates select="* except LINE_NUMBER[. = 0]"/>. If you still have problems then consider to edit your question and show us the result you want as a code sample, I am afraid the screen shot does not suffice to tell us which elements you want to include and which not.
Martin, I tried my best ( at the fourm limitations) to add some mroe data on my case.hope this can help...
0

Since you're using XSLT 2.0, you can use xsl:for-each-group (see XSLT 2.0 grouping) to group by EVENT_ID.

Also, use an identity transform to handle all of the elements that don't need to change.

Example...

XML Input

<ns:MT_ActualCosts xmlns:ns="http://percite:scmaster/actual_costs">
    <row>
        <EVENT_ID>106</EVENT_ID>
        <LINE_NUMBER>0</LINE_NUMBER>
        <INVOICE_NUMBER>9999</INVOICE_NUMBER>
        <INVOICE_DATE>2015-02-09 00:00:00.0</INVOICE_DATE>
        <CURRENCY_CODE>USD</CURRENCY_CODE>
        <TOTAL_AMOUNT_NET>0001</TOTAL_AMOUNT_NET>
        <RO_NUMBER>102808</RO_NUMBER>
    </row>
    <row>
        <EVENT_ID>106</EVENT_ID>
        <LINE_NUMBER>1</LINE_NUMBER>
        <INVOICE_NUMBER>24444</INVOICE_NUMBER>
        <PLANT>0003</PLANT>
        <ALLOCATION_AMOUNT>122</ALLOCATION_AMOUNT>
    </row>
    <row>
        <EVENT_ID>109</EVENT_ID>
        <LINE_NUMBER>0</LINE_NUMBER>
        <INVOICE_NUMBER>24458</INVOICE_NUMBER>
        <INVOICE_DATE>2015-02-09 00:00:00.0</INVOICE_DATE>
        <CURRENCY_CODE>USD</CURRENCY_CODE>
        <TOTAL_AMOUNT_NET>0011</TOTAL_AMOUNT_NET>
        <RO_NUMBER>102813</RO_NUMBER>
    </row>
    <row>
        <EVENT_ID>109</EVENT_ID>
        <LINE_NUMBER>1</LINE_NUMBER>
        <INVOICE_NUMBER>24458</INVOICE_NUMBER>
        <PLANT>0003</PLANT>
        <ALLOCATION_AMOUNT>11.1</ALLOCATION_AMOUNT>
    </row>
    <row>
        <EVENT_ID>108</EVENT_ID>
        <LINE_NUMBER>0</LINE_NUMBER>
        <INVOICE_NUMBER>24535</INVOICE_NUMBER>
        <INVOICE_DATE>2015-02-19 00:00:00.0</INVOICE_DATE>
        <CURRENCY_CODE>USD</CURRENCY_CODE>
        <TOTAL_AMOUNT_NET>11</TOTAL_AMOUNT_NET>
        <RO_NUMBER>102811</RO_NUMBER>
    </row>
    <row>
        <EVENT_ID>108</EVENT_ID>
        <LINE_NUMBER>1</LINE_NUMBER>
        <INVOICE_NUMBER>24535</INVOICE_NUMBER>
        <PLANT>0002</PLANT>
        <ALLOCATION_AMOUNT>11</ALLOCATION_AMOUNT>
    </row>
    <row>
        <EVENT_ID>171</EVENT_ID>
        <LINE_NUMBER>0</LINE_NUMBER>
        <INVOICE_NUMBER>24645</INVOICE_NUMBER>
        <INVOICE_DATE>2015-02-28 00:00:00.0</INVOICE_DATE>
        <CURRENCY_CODE>USD</CURRENCY_CODE>
        <TOTAL_AMOUNT_NET>999999</TOTAL_AMOUNT_NET>
        <RO_NUMBER>103063</RO_NUMBER>
    </row>
    <row>
        <EVENT_ID>171</EVENT_ID>
        <LINE_NUMBER>1</LINE_NUMBER>
        <INVOICE_NUMBER>24645</INVOICE_NUMBER>
        <PLANT>0001</PLANT>
        <ALLOCATION_AMOUNT>11.47</ALLOCATION_AMOUNT>
    </row>
    <row>
        <EVENT_ID>171</EVENT_ID>
        <LINE_NUMBER>2</LINE_NUMBER>
        <INVOICE_NUMBER>24645</INVOICE_NUMBER>
        <PLANT>0001</PLANT>
        <ALLOCATION_AMOUNT>11.53</ALLOCATION_AMOUNT>
    </row>
</ns:MT_ActualCosts>

XSLT 2.0

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
  xmlns:ns="http://percite:scmaster/actual_costs">
  <xsl:output indent="yes"/>
  <xsl:strip-space elements="*"/>

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

  <xsl:template match="/*">
    <ns:MT_ActualCostPreNormalized>
      <!--Group rows by EVENT_ID.-->
      <xsl:for-each-group select="row" group-by="EVENT_ID">
        <!--Optional sort by EVENT_ID.-->
        <xsl:sort select="current-grouping-key()"/>
        <Header>
          <!--Apply templates to the children of row's with a LINE_NUMBER of 0 
          followed by row's that have a LINE_NUMBER that isn't equal to 0.-->
          <xsl:apply-templates select="current-group()[LINE_NUMBER=0]/*,
            current-group()[not(LINE_NUMBER=0)]">
            <!--Optional sort by LINE_NUMBER.-->
            <xsl:sort select="LINE_NUMBER"/>
          </xsl:apply-templates>
        </Header>
      </xsl:for-each-group>
    </ns:MT_ActualCostPreNormalized>
  </xsl:template>

  <!--Because of the apply-templates, the only row elements matched 
  should be row's with a LINE_NUMBER > 0.-->
  <xsl:template match="row">
    <RecordLine>
      <xsl:apply-templates/>
    </RecordLine>          
  </xsl:template>

</xsl:stylesheet>

XML Output

<ns:MT_ActualCostPreNormalized xmlns:ns="http://percite:scmaster/actual_costs">
   <Header>
      <EVENT_ID>106</EVENT_ID>
      <LINE_NUMBER>0</LINE_NUMBER>
      <INVOICE_NUMBER>9999</INVOICE_NUMBER>
      <INVOICE_DATE>2015-02-09 00:00:00.0</INVOICE_DATE>
      <CURRENCY_CODE>USD</CURRENCY_CODE>
      <TOTAL_AMOUNT_NET>0001</TOTAL_AMOUNT_NET>
      <RO_NUMBER>102808</RO_NUMBER>
      <RecordLine>
         <EVENT_ID>106</EVENT_ID>
         <LINE_NUMBER>1</LINE_NUMBER>
         <INVOICE_NUMBER>24444</INVOICE_NUMBER>
         <PLANT>0003</PLANT>
         <ALLOCATION_AMOUNT>122</ALLOCATION_AMOUNT>
      </RecordLine>
   </Header>
   <Header>
      <EVENT_ID>108</EVENT_ID>
      <LINE_NUMBER>0</LINE_NUMBER>
      <INVOICE_NUMBER>24535</INVOICE_NUMBER>
      <INVOICE_DATE>2015-02-19 00:00:00.0</INVOICE_DATE>
      <CURRENCY_CODE>USD</CURRENCY_CODE>
      <TOTAL_AMOUNT_NET>11</TOTAL_AMOUNT_NET>
      <RO_NUMBER>102811</RO_NUMBER>
      <RecordLine>
         <EVENT_ID>108</EVENT_ID>
         <LINE_NUMBER>1</LINE_NUMBER>
         <INVOICE_NUMBER>24535</INVOICE_NUMBER>
         <PLANT>0002</PLANT>
         <ALLOCATION_AMOUNT>11</ALLOCATION_AMOUNT>
      </RecordLine>
   </Header>
   <Header>
      <EVENT_ID>109</EVENT_ID>
      <LINE_NUMBER>0</LINE_NUMBER>
      <INVOICE_NUMBER>24458</INVOICE_NUMBER>
      <INVOICE_DATE>2015-02-09 00:00:00.0</INVOICE_DATE>
      <CURRENCY_CODE>USD</CURRENCY_CODE>
      <TOTAL_AMOUNT_NET>0011</TOTAL_AMOUNT_NET>
      <RO_NUMBER>102813</RO_NUMBER>
      <RecordLine>
         <EVENT_ID>109</EVENT_ID>
         <LINE_NUMBER>1</LINE_NUMBER>
         <INVOICE_NUMBER>24458</INVOICE_NUMBER>
         <PLANT>0003</PLANT>
         <ALLOCATION_AMOUNT>11.1</ALLOCATION_AMOUNT>
      </RecordLine>
   </Header>
   <Header>
      <EVENT_ID>171</EVENT_ID>
      <LINE_NUMBER>0</LINE_NUMBER>
      <INVOICE_NUMBER>24645</INVOICE_NUMBER>
      <INVOICE_DATE>2015-02-28 00:00:00.0</INVOICE_DATE>
      <CURRENCY_CODE>USD</CURRENCY_CODE>
      <TOTAL_AMOUNT_NET>999999</TOTAL_AMOUNT_NET>
      <RO_NUMBER>103063</RO_NUMBER>
      <RecordLine>
         <EVENT_ID>171</EVENT_ID>
         <LINE_NUMBER>1</LINE_NUMBER>
         <INVOICE_NUMBER>24645</INVOICE_NUMBER>
         <PLANT>0001</PLANT>
         <ALLOCATION_AMOUNT>11.47</ALLOCATION_AMOUNT>
      </RecordLine>
      <RecordLine>
         <EVENT_ID>171</EVENT_ID>
         <LINE_NUMBER>2</LINE_NUMBER>
         <INVOICE_NUMBER>24645</INVOICE_NUMBER>
         <PLANT>0001</PLANT>
         <ALLOCATION_AMOUNT>11.53</ALLOCATION_AMOUNT>
      </RecordLine>
   </Header>
</ns:MT_ActualCostPreNormalized>

Working Example

Improve this answer

1 Comment

Daniel,Thanks for your solution. Seems like somewhere down the result the data begins to scramble for some reason. look at EVENT_ID = 105.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.