Help working with distinct records in an xml doc

Discussion in 'XML' started by Chris Kettenbach, Aug 24, 2005.

  1. Good Morning,
    Sorry for xposting. Just need a liitle help.
    I have an xml file that's generated from a database. How do I select
    distinct values from a field in xslt and then loop through the records and
    produce output. Example

    <registrations>
    <registration>
    <company>Awesome Printers</company>
    <first>John</first>
    <last>Smith</last>
    </registration>
    <registration>
    <company>Awesome Printers</company>
    <first>Bob</first>
    <last>Roberts</last>
    </registration>
    <registration>
    <company>Awesome Printers</company>
    <first>John</first>
    <last>Johnson</last>
    </registration>
    <registration>
    <company>Other Company</company>
    <first>Tom</first>
    <last>Thomas</last>
    </registration>
    <registration>
    <company>Other Company</company>
    <first>Dan</first>
    <last>Daniels</last>
    </registration>
    </registrations>
    ++++++++++++++++++++++++++++++++++++++++++++++++
    I want the out put to be something like this

    <table>
    <tr>
    <td>Company Name</td>
    <td>Employees</td>
    </tr>
    <tr>
    <td>Awesome Printers</td>
    <td>John Smith<br/>
    Bob Roberts<br/>
    John Johnson<br/></td>
    </tr>
    <tr>
    <td>Other Company</td>
    <td>Tom Thomas<br/>
    Dan Daniels<br/></td>
    </tr>
    </table>

    Effectively writing unique company names once and then putting only the
    employees from that company into the employee table cell.

    Thanks for any advise.
    Regards,
    Chris
     
    Chris Kettenbach, Aug 24, 2005
    #1
    1. Advertising

  2. Chris Kettenbach

    Peter Flynn Guest

    Chris Kettenbach wrote:

    > Good Morning,
    > Sorry for xposting. Just need a liitle help.
    > I have an xml file that's generated from a database. How do I select
    > distinct values from a field


    XML doesn't have fields -- the database did but this isn't a database any
    more. In XML they're called elements (they've got a lot in common with
    fields but they ain't the same).

    > in xslt and then loop through the records and
    > produce output.


    It looks like you need to process the information grouped by company name,
    and there's a technique in XSLT 1.0 for doing this called Muenchian grouping
    (it won't be needed in XSLT 2 because that has a built-in group-processing
    command, but XSLT 2 is not a Recommendation yet).

    Check the XSLT FAQ for "Muenchian" to find examples.

    ///Peter

    [Group and followup corrected to comp.text.xml]

    > Example
    >
    > <registrations>
    > <registration>
    > <company>Awesome Printers</company>
    > <first>John</first>
    > <last>Smith</last>
    > </registration>
    > <registration>
    > <company>Awesome Printers</company>
    > <first>Bob</first>
    > <last>Roberts</last>
    > </registration>
    > <registration>
    > <company>Awesome Printers</company>
    > <first>John</first>
    > <last>Johnson</last>
    > </registration>
    > <registration>
    > <company>Other Company</company>
    > <first>Tom</first>
    > <last>Thomas</last>
    > </registration>
    > <registration>
    > <company>Other Company</company>
    > <first>Dan</first>
    > <last>Daniels</last>
    > </registration>
    > </registrations>
    > ++++++++++++++++++++++++++++++++++++++++++++++++
    > I want the out put to be something like this
    >
    > <table>
    > <tr>
    > <td>Company Name</td>
    > <td>Employees</td>
    > </tr>
    > <tr>
    > <td>Awesome Printers</td>
    > <td>John Smith<br/>
    > Bob Roberts<br/>
    > John Johnson<br/></td>
    > </tr>
    > <tr>
    > <td>Other Company</td>
    > <td>Tom Thomas<br/>
    > Dan Daniels<br/></td>
    > </tr>
    > </table>
    >
    > Effectively writing unique company names once and then putting only the
    > employees from that company into the employee table cell.


    If you scrolled down this far then you've been rewarded :)

    The trick is to use lookup keys to test the selected group of elements
    against, in order to pick only the first ocurrence in each group; then
    output the group header at that point; and only then go and process all the
    elements in the group to extract the detail from each of them.

    The syntax is tricky because it's very compact. It selects all registration
    elements, but subjects each of them to the test that [the number of nodes in
    the union of (the current node and the first node with the current company
    value returned by the lookup) is equal to 1] -- in effect meaning they are
    one and the same node, ie the first in their company name grouping. You'll
    probably need to read that 3 or 4 times -- it took me a week to grok it.

    <?xml version="1.0" encoding="iso-8859-1"?>
    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    version="1.0">

    <xsl:eek:utput method="html"/>

    <xsl:key name="entry" match="registration" use="company"/>

    <xsl:template match="/">
    <html>
    <head>
    <title>Registrations</title>
    </head>
    <body>
    <xsl:apply-templates/>
    </body>
    </html>
    </xsl:template>

    <xsl:template match="registrations">
    <table>
    <xsl:for-each select="registration[count(.|key('entry',company
    [1])=1]">
    <xsl:sort select="company"/>
    <tr>
    <th valign="top">
    <xsl:value-of select="company"/>
    </th>
    <td valign="top">
    <xsl:for-each select="key('entry',current()/company)">
    <xsl:sort select="last"/>
    <xsl:value-of select="first"/>
    <xsl:text> </xsl:text>
    <xsl:value-of select="last"/>
    <xsl:if test="position()!=last()">
    <br/>
    </xsl:if>
    </xsl:for-each>
    </td>
    </tr>
    </xsl:for-each>
    </table>
    </xsl:template>

    </xsl:stylesheet>

    ///Peter
     
    Peter Flynn, Aug 24, 2005
    #2
    1. Advertising

  3. Chris Kettenbach

    Stefan Ram Guest

    Peter Flynn <> writes:
    >XML doesn't have fields -- the database did but this isn't a
    >database any more. In XML they're called elements


    Sometimes it might be appropriate to use attributes.
     
    Stefan Ram, Aug 24, 2005
    #3
  4. Hi Peter,
    I get error when processing the stylesheet. It errors here.

    <xsl:for-each select="registration[count(.|key('entry',company
    [1])=1]">

    specifically:

    Expression does not return a DOM node.
    registration[-->count(.|key('entry',company[1])=1]<--

    Ideas?


    Thanks,
    Chris

    "Peter Flynn" <> wrote in message
    news:...
    > Chris Kettenbach wrote:
    >
    >> Good Morning,
    >> Sorry for xposting. Just need a liitle help.
    >> I have an xml file that's generated from a database. How do I select
    >> distinct values from a field

    >
    > XML doesn't have fields -- the database did but this isn't a database any
    > more. In XML they're called elements (they've got a lot in common with
    > fields but they ain't the same).
    >
    >> in xslt and then loop through the records and
    >> produce output.

    >
    > It looks like you need to process the information grouped by company name,
    > and there's a technique in XSLT 1.0 for doing this called Muenchian
    > grouping
    > (it won't be needed in XSLT 2 because that has a built-in group-processing
    > command, but XSLT 2 is not a Recommendation yet).
    >
    > Check the XSLT FAQ for "Muenchian" to find examples.
    >
    > ///Peter
    >
    > [Group and followup corrected to comp.text.xml]
    >
    >> Example
    >>
    >> <registrations>
    >> <registration>
    >> <company>Awesome Printers</company>
    >> <first>John</first>
    >> <last>Smith</last>
    >> </registration>
    >> <registration>
    >> <company>Awesome Printers</company>
    >> <first>Bob</first>
    >> <last>Roberts</last>
    >> </registration>
    >> <registration>
    >> <company>Awesome Printers</company>
    >> <first>John</first>
    >> <last>Johnson</last>
    >> </registration>
    >> <registration>
    >> <company>Other Company</company>
    >> <first>Tom</first>
    >> <last>Thomas</last>
    >> </registration>
    >> <registration>
    >> <company>Other Company</company>
    >> <first>Dan</first>
    >> <last>Daniels</last>
    >> </registration>
    >> </registrations>
    >> ++++++++++++++++++++++++++++++++++++++++++++++++
    >> I want the out put to be something like this
    >>
    >> <table>
    >> <tr>
    >> <td>Company Name</td>
    >> <td>Employees</td>
    >> </tr>
    >> <tr>
    >> <td>Awesome Printers</td>
    >> <td>John Smith<br/>
    >> Bob Roberts<br/>
    >> John Johnson<br/></td>
    >> </tr>
    >> <tr>
    >> <td>Other Company</td>
    >> <td>Tom Thomas<br/>
    >> Dan Daniels<br/></td>
    >> </tr>
    >> </table>
    >>
    >> Effectively writing unique company names once and then putting only the
    >> employees from that company into the employee table cell.

    >
    > If you scrolled down this far then you've been rewarded :)
    >
    > The trick is to use lookup keys to test the selected group of elements
    > against, in order to pick only the first ocurrence in each group; then
    > output the group header at that point; and only then go and process all
    > the
    > elements in the group to extract the detail from each of them.
    >
    > The syntax is tricky because it's very compact. It selects all
    > registration
    > elements, but subjects each of them to the test that [the number of nodes
    > in
    > the union of (the current node and the first node with the current company
    > value returned by the lookup) is equal to 1] -- in effect meaning they are
    > one and the same node, ie the first in their company name grouping. You'll
    > probably need to read that 3 or 4 times -- it took me a week to grok it.
    >
    > <?xml version="1.0" encoding="iso-8859-1"?>
    > <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    > version="1.0">
    >
    > <xsl:eek:utput method="html"/>
    >
    > <xsl:key name="entry" match="registration" use="company"/>
    >
    > <xsl:template match="/">
    > <html>
    > <head>
    > <title>Registrations</title>
    > </head>
    > <body>
    > <xsl:apply-templates/>
    > </body>
    > </html>
    > </xsl:template>
    >
    > <xsl:template match="registrations">
    > <table>
    > <xsl:for-each select="registration[count(.|key('entry',company
    > [1])=1]">
    > <xsl:sort select="company"/>
    > <tr>
    > <th valign="top">
    > <xsl:value-of select="company"/>
    > </th>
    > <td valign="top">
    > <xsl:for-each select="key('entry',current()/company)">
    > <xsl:sort select="last"/>
    > <xsl:value-of select="first"/>
    > <xsl:text> </xsl:text>
    > <xsl:value-of select="last"/>
    > <xsl:if test="position()!=last()">
    > <br/>
    > </xsl:if>
    > </xsl:for-each>
    > </td>
    > </tr>
    > </xsl:for-each>
    > </table>
    > </xsl:template>
    >
    > </xsl:stylesheet>
    >
    > ///Peter
    >
     
    Chris Kettenbach, Aug 24, 2005
    #4
  5. Chris Kettenbach

    toudidel Guest

    hint: from performed elements you have to create attributtes in yor new
    element, because there isn't posibility to being few the same attributtes in
    one xml element

    td
     
    toudidel, Aug 25, 2005
    #5
  6. Chris Kettenbach

    Peter Flynn Guest

    Chris Kettenbach wrote:

    > Hi Peter,
    > I get error when processing the stylesheet. It errors here.
    >
    > <xsl:for-each select="registration[count(.|key('entry',company
    > [1])=1]">
    >
    > specifically:
    >
    > Expression does not return a DOM node.
    > registration[-->count(.|key('entry',company[1])=1]<--
    >
    > Ideas?


    What processor are you using? I ran it through Saxon without errors, and the
    output was:

    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>Registrations</title>
    </head>
    <body>
    <table>
    <tr>
    <th valign="top">Awesome Printers</th>
    <td valign="top">John Johnson<br>Bob Roberts<br>John Smith
    </td>
    </tr>
    <tr>
    <th valign="top">Other Company</th>
    <td valign="top">Dan Daniels<br>Tom Thomas
    </td>
    </tr>
    </table>
    </body>
    </html>

    ///Peter

    > "Peter Flynn" <> wrote in message
    > news:...
    >> Chris Kettenbach wrote:
    >>
    >>> Good Morning,
    >>> Sorry for xposting. Just need a liitle help.
    >>> I have an xml file that's generated from a database. How do I select
    >>> distinct values from a field

    >>
    >> XML doesn't have fields -- the database did but this isn't a database any
    >> more. In XML they're called elements (they've got a lot in common with
    >> fields but they ain't the same).
    >>
    >>> in xslt and then loop through the records and
    >>> produce output.

    >>
    >> It looks like you need to process the information grouped by company
    >> name, and there's a technique in XSLT 1.0 for doing this called Muenchian
    >> grouping
    >> (it won't be needed in XSLT 2 because that has a built-in
    >> group-processing command, but XSLT 2 is not a Recommendation yet).
    >>
    >> Check the XSLT FAQ for "Muenchian" to find examples.
    >>
    >> ///Peter
    >>
    >> [Group and followup corrected to comp.text.xml]
    >>
    >>> Example
    >>>
    >>> <registrations>
    >>> <registration>
    >>> <company>Awesome Printers</company>
    >>> <first>John</first>
    >>> <last>Smith</last>
    >>> </registration>
    >>> <registration>
    >>> <company>Awesome Printers</company>
    >>> <first>Bob</first>
    >>> <last>Roberts</last>
    >>> </registration>
    >>> <registration>
    >>> <company>Awesome Printers</company>
    >>> <first>John</first>
    >>> <last>Johnson</last>
    >>> </registration>
    >>> <registration>
    >>> <company>Other Company</company>
    >>> <first>Tom</first>
    >>> <last>Thomas</last>
    >>> </registration>
    >>> <registration>
    >>> <company>Other Company</company>
    >>> <first>Dan</first>
    >>> <last>Daniels</last>
    >>> </registration>
    >>> </registrations>
    >>> ++++++++++++++++++++++++++++++++++++++++++++++++
    >>> I want the out put to be something like this
    >>>
    >>> <table>
    >>> <tr>
    >>> <td>Company Name</td>
    >>> <td>Employees</td>
    >>> </tr>
    >>> <tr>
    >>> <td>Awesome Printers</td>
    >>> <td>John Smith<br/>
    >>> Bob Roberts<br/>
    >>> John Johnson<br/></td>
    >>> </tr>
    >>> <tr>
    >>> <td>Other Company</td>
    >>> <td>Tom Thomas<br/>
    >>> Dan Daniels<br/></td>
    >>> </tr>
    >>> </table>
    >>>
    >>> Effectively writing unique company names once and then putting only the
    >>> employees from that company into the employee table cell.

    >>
    >> If you scrolled down this far then you've been rewarded :)
    >>
    >> The trick is to use lookup keys to test the selected group of elements
    >> against, in order to pick only the first ocurrence in each group; then
    >> output the group header at that point; and only then go and process all
    >> the
    >> elements in the group to extract the detail from each of them.
    >>
    >> The syntax is tricky because it's very compact. It selects all
    >> registration
    >> elements, but subjects each of them to the test that [the number of nodes
    >> in
    >> the union of (the current node and the first node with the current
    >> company value returned by the lookup) is equal to 1] -- in effect meaning
    >> they are one and the same node, ie the first in their company name
    >> grouping. You'll probably need to read that 3 or 4 times -- it took me a
    >> week to grok it.
    >>
    >> <?xml version="1.0" encoding="iso-8859-1"?>
    >> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    >> version="1.0">
    >>
    >> <xsl:eek:utput method="html"/>
    >>
    >> <xsl:key name="entry" match="registration" use="company"/>
    >>
    >> <xsl:template match="/">
    >> <html>
    >> <head>
    >> <title>Registrations</title>
    >> </head>
    >> <body>
    >> <xsl:apply-templates/>
    >> </body>
    >> </html>
    >> </xsl:template>
    >>
    >> <xsl:template match="registrations">
    >> <table>
    >> <xsl:for-each select="registration[count(.|key('entry',company
    >> [1])=1]">
    >> <xsl:sort select="company"/>
    >> <tr>
    >> <th valign="top">
    >> <xsl:value-of select="company"/>
    >> </th>
    >> <td valign="top">
    >> <xsl:for-each select="key('entry',current()/company)">
    >> <xsl:sort select="last"/>
    >> <xsl:value-of select="first"/>
    >> <xsl:text> </xsl:text>
    >> <xsl:value-of select="last"/>
    >> <xsl:if test="position()!=last()">
    >> <br/>
    >> </xsl:if>
    >> </xsl:for-each>
    >> </td>
    >> </tr>
    >> </xsl:for-each>
    >> </table>
    >> </xsl:template>
    >>
    >> </xsl:stylesheet>
    >>
    >> ///Peter
    >>
     
    Peter Flynn, Aug 25, 2005
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Yogs

    XML Distinct

    Yogs, Feb 15, 2004, in forum: ASP .Net
    Replies:
    3
    Views:
    2,425
  2. Matt
    Replies:
    3
    Views:
    522
    Tor Iver Wilhelmsen
    Sep 17, 2004
  3. jj23
    Replies:
    1
    Views:
    489
    Patrick TJ McPhee
    Feb 13, 2004
  4. AlecL
    Replies:
    1
    Views:
    353
    Peter Bradley
    Mar 8, 2007
  5. Hicham Mouline
    Replies:
    1
    Views:
    401
    Kai-Uwe Bux
    Apr 11, 2010
Loading...

Share This Page