"Group by" problem

dani1 · October 30th, 2008, 08:50 AM

Hi!

I need to convert some flat structure to nested one.

The source file is:

<root>
<Invoice>
<InvType>0</InvType>
<InvNum>111</InvNum>
</Invoice>
<Delivery>
<DeliveryType>1</DeliveryType>
<DeliveryNum>111</DeliveryNum>
<Amount>100.00</Amount>
<InvNum>111</InvNum>
</Delivery>
<Line>
<LineNum>1</LineNum>
<LineType>ww</LineType>
<DeliveryNum>111</DeliveryNum>
</Line>
<Line>
<LineNum>2</LineNum>
<LineType>ww</LineType>
<DeliveryNum>111</DeliveryNum>
</Line>
<Delivery>
<DeliveryType>1</DeliveryType>
<DeliveryNum>222</DeliveryNum>
<Amount>200.00</Amount>
<InvNum>111</InvNum>
</Delivery>
<Line>
<LineNum>1</LineNum>
<LineType>uu</LineType>
<DeliveryNum>333</DeliveryNum>
</Line>

</root>

The target must be:

<root>
<Invoice>
<InvType>0</InvType>
<InvNum>111</InvNum>
<Delivery>
<DeliveryType>1</DeliveryType>
<DeliveryNum>111</DeliveryNum>
<Amount>100.00</Amount>
<InvNum>111</InvNum>
<Line>
<LineNum>1</LineNum>
<LineType>ww</LineType>
<DeliveryNum>111</DeliveryNum>
</Line>
<Line>
<LineNum>2</LineNum>
<LineType>ww</LineType>
<DeliveryNum>111</DeliveryNum>
</Line>
</Delivery>
<Delivery>
<DeliveryType>1</DeliveryType>
<DeliveryNum>222</DeliveryNum>
<Amount>200.00</Amount>
<InvNum>111</InvNum>
<Line>
<LineNum>1</LineNum>
<LineType>uu</LineType>
<DeliveryNum>333</DeliveryNum>
</Line>
</Delivery>
</Invoice>

</root>

Here is my code:

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

<xsl:template match="/">
<root>
<xsl:for-each-group select="root/Invoice" group-by="InvNum">
<Invoice>
<InvType>
<xsl:value-of select="InvType"/>
</InvType>
<InvNum>
<xsl:value-of select="InvNum"/>
</InvNum>

<xsl:for-each select="../Delivery[InvNum=current-grouping-key()]">
<Delivery>
<DeliveryNum>
<xsl:value-of select="DeliveryNum"/>
</DeliveryNum>
<DeliveryType>
<xsl:value-of select="DeliveryType"/>
</DeliveryType>
<Amount>
<xsl:value-of select="Amount"/>
</Amount>

<xsl:for-each select="../Line[DeliveryNum=../Delivery/DeliveryNum]">
<Line>
<LineNum>
<xsl:value-of select="LineNum"/>
</LineNum>
<LineType>
<xsl:value-of select="LineType"/>
</LineType>
<DeliveryNum>
<xsl:value-of select="DeliveryNum"/>
</DeliveryNum>
</Line>
</xsl:for-each>
</Delivery>
</xsl:for-each>
</Invoice>
</xsl:for-each-group>
</root>
</xsl:template>
</xsl:stylesheet>

Here is the result. Each delivery has the same Line, instead of lines that related to those delivery.

<root>
<Invoice>
<InvType>0</InvType>
<InvNum>111</InvNum>
<Delivery>
<DeliveryNum>111</DeliveryNum>
<DeliveryType>1</DeliveryType>
<Amount>100.00</Amount>
<Line>
<LineNum>1</LineNum>
<LineType>ww</LineType>
<DeliveryNum>111</DeliveryNum>
</Line>
</Delivery>
<Delivery>
<DeliveryNum>222</DeliveryNum>
<DeliveryType>1</DeliveryType>
<Amount>200.00</Amount>
<Line>
<LineNum>1</LineNum>
<LineType>ww</LineType>
<DeliveryNum>111</DeliveryNum>
</Line>
</Delivery>
</Invoice>
</root>

Where is the problem?

Martin Honnen · October 30th, 2008, 12:31 PM

I don't understand your sample data completely, as in your target you have

Code:

<Delivery>
<DeliveryType>1</DeliveryType>
<DeliveryNum>222</DeliveryNum>
<Amount>200.00</Amount>
<InvNum>111</InvNum>
<Line>
<LineNum>1</LineNum>
<LineType>uu</LineType>
<DeliveryNum>333</DeliveryNum>
</Line>
</Delivery>

Why do you want to insert a 'Line' element with a 'DeliveryNum' child element with value '333' into a 'Delivery' element with 'DeliveryNum' having the value '222'?

Other than that I think you simply want a stylesheet as follows:

Code:

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

  <xsl:output method="xml" indent="yes"/>

  <xsl:template match="root">
    <xsl:copy>
      <xsl:apply-templates select="Invoice"/>
    </xsl:copy>
  </xsl:template>

  <xsl:template match="Invoice">
    <xsl:copy>
      <xsl:apply-templates select="InvType | InvNum"/>
      <xsl:apply-templates select="/root/Delivery[InvNum = current()/InvNum]"/>
    </xsl:copy>
  </xsl:template>

  <xsl:template match="Delivery">
    <xsl:copy>
      <xsl:apply-templates select="*"/>
      <xsl:apply-templates select="/root/Line[DeliveryNum = current()/DeliveryNum]"/>
    </xsl:copy>
  </xsl:template>

  <xsl:template match="*">
    <xsl:copy>
      <xsl:apply-templates/>
    </xsl:copy>
  </xsl:template>

</xsl:stylesheet>

--
Martin Honnen
Microsoft MVP - XML

dani1 · October 30th, 2008, 03:21 PM

Quote:

quote:Originally posted by Martin Honnen

Why do you want to insert a 'Line' element with a 'DeliveryNum' child element with value '333' into a 'Delivery' element with 'DeliveryNum' having the value '222'?

You are right. I didn't pay attention to this mistake.

Quote:

quote:Originally posted by Martin Honnen

Code:

<xsl:stylesheet
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  version="2.0">
  
  <xsl:output method="xml" indent="yes"/>
  
  <xsl:template match="root">
    <xsl:copy>
      <xsl:apply-templates select="Invoice"/>
    </xsl:copy>
  </xsl:template>
  
  <xsl:template match="Invoice">
    <xsl:copy>
      <xsl:apply-templates select="InvType | InvNum"/>
      <xsl:apply-templates select="/root/Delivery[InvNum = current()/InvNum]"/>
    </xsl:copy>
  </xsl:template>
  
  <xsl:template match="Delivery">
    <xsl:copy>
      <xsl:apply-templates select="*"/>
      <xsl:apply-templates select="/root/Line[DeliveryNum = current()/DeliveryNum]"/>
    </xsl:copy>
  </xsl:template>
  
  <xsl:template match="*">
    <xsl:copy>
      <xsl:apply-templates/>
    </xsl:copy>
  </xsl:template>
  
</xsl:stylesheet>

--
Martin Honnen
Microsoft MVP - XML

That's it! Martin, thank you very very much!