Getting distinct values

I need some help to transform some XML that is derived from a recordset 
where each record is a Record element. My output XML needs to have one 
Record element for each distinct Id value and within this element I want a 
ColValuesA element that contains a distinct list of all ColA values and a 
ColValuesB element that contains a distinct list of all ColB values.

Recordset used to generate Input XML:

Id    ColA    ColB
1    A1        B1
1    A1        B2
1    A1        B3
1    A2        B1
1    A2        B2
1    A2        B3
2 ...more data...


Input XML:
<Root>
    <Record Id="1">
        <ColA>A1</ColA>
        <ColB>B1</ColB>
    </Record>
 ...and repeated for each record...
</Root>


Output XML required:
<Transformed>
    <Record Id="1">
        <ColValuesA>
            <ColA>A1</ColA>
            <ColA>A2</ColA>
        </ColValuesA>
        <ColValuesB>
            <ColB>B1</ColB>
            <ColB>B2</ColB>
            <ColB>B3</ColB>
        </ColValuesB>
 </Record>
 <Record Id="2">
    ...more...
 </Record>
</Transformed> 

chris fellows wrote:

> Output XML required:
> <Transformed>
>     <Record Id="1">
>         <ColValuesA>
>             <ColA>A1</ColA>
>             <ColA>A2</ColA>
>         </ColValuesA>
>         <ColValuesB>
>             <ColB>B1</ColB>
>             <ColB>B2</ColB>
>             <ColB>B3</ColB>
>         </ColValuesB>
>  </Record>
>  <Record Id="2">
>     ...more...
>  </Record>
> </Transformed> 

Here is an XSLT 2.0 stylesheet that uses xsl:for-each-group and the 
distinct-values function:

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

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

   <xsl:template match="Root">
     <Tranformed>
       <xsl:for-each-group select="Record"
group-by="@Id">
         <Record Id="{current-grouping-key()}">
           <ColValuesA>
             <xsl:for-each
select="distinct-values(current-group()/ColA)">
               <ColA>
                 <xsl:value-of select="."/>
               </ColA>
             </xsl:for-each>
           </ColValuesA>
           <ColValuesB>
             <xsl:for-each
select="distinct-values(current-group()/ColB)">
               <ColB>
                 <xsl:value-of select="."/>
               </ColB>
             </xsl:for-each>
           </ColValuesB>
         </Record>
       </xsl:for-each-group>
     </Tranformed>
   </xsl:template>

</xsl:stylesheet>

You can use XSLT 2.0 with Saxon 9 <URL:http://saxon.sourceforge.net/>, 
with Gestalt <URL:http://gestalt.sourceforge.net/> and with AltovaXML 
tools <URL:http://www.altova.com/altovaxml.html>

If you can't use XSLT 2.0 nevertheless then here is an XSLT 1.0 solution:

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

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

   <xsl:key name="by-id" match="Record"
use="@Id"/>

   <xsl:key name="by-a" match="ColA"
use="concat(../@Id, '_', .)"/>

   <xsl:key name="by-b" match="ColB"
use="concat(../@Id, '_', .)"/>

   <xsl:template match="Root">
     <Transformed>
       <xsl:for-each select="Record[generate-id() = 
generate-id(key('by-id', @Id)[1])]">
         <Record Id="{@Id}">
           <ColValuesA>
             <xsl:for-each
               select="key('by-id', @Id)/ColA[generate-id() = 
generate-id(key('by-a', concat(../@Id, '_', .))[1])]">
                 <ColA>
                   <xsl:value-of select="."/>
                 </ColA>
             </xsl:for-each>
           </ColValuesA>
           <ColValuesB>
             <xsl:for-each
               select="key('by-id', @Id)/ColB[generate-id() = 
generate-id(key('by-b', concat(../@Id, '_', .))[1])]">
                 <ColB>
                   <xsl:value-of select="."/>
                 </ColB>
             </xsl:for-each>
           </ColValuesB>
         </Record>
       </xsl:for-each>
     </Transformed>
   </xsl:template>

</xsl:stylesheet>


-- 

	Martin Honnen --- MVP XML

Użytkownik "chris fellows" <chrisfellows@nospam.co.uk> napisał w
wiadomości 
news:eShewdZkIHA.1168@TK2MSFTNGP02.phx.gbl...
>I need some help to transform some XML that is derived from a recordset 
>where each record is a Record element. My output XML needs to have one 
>Record element for each distinct Id value and within this element I want a 
>ColValuesA element that contains a distinct list of all ColA values and a 
>ColValuesB element that contains a distinct list of all ColB values.

In XSLT you can use "attributes rule" - I mean in one element you can
have 
only one attribute with certain name, so first transform your column data to 
elements' attributtes names and then you will be able count or read that 
attributtes.
For example: if you have values: B1, B2, B3, B1, B2, B3 and that values 
transform to node's attributtes names you would have:

<node B1="" B2="" B3=""/> because it is not
possible to have a few 
attributtes with the same name (<node B1="" B2=""
B3="" B1="" B2="" B3="").

It is one way to get distinct values in xml via xslt transformation
-- 
td