TopXML SQL XML library : Filtering unique values

Home Page

Add/Edit your code items

Search the code library

Browse for the code library

SQL XML
SQL XML
SQL XML Contents
Passing stored procedure a list of complete zipcodes to validate
Convert a flat text file into xml
XML IMDB Concepts.
Using XML as a Data Store
Creating An XML Based Cache Using COM
Character encoding, a few words on the subject
How to use the ADO Stream object to retrieve SQL2000 XML
How to transfer XML into a plain text file?
Convert XDR to XML
Sort a table using the header of the table
Applying a XSLT stylesheet using the DBMS_XMLQuery package
N:N relationships with DBMS_XMLQuery Package
DBMS_XMLQuery package bind values to produce more than one xml file
Download data from a database in XML
Inserting XML file into a table using DBMS_XMLSave Package
Insert XML file into a table using ADO
How to call out to a COM component's method through XSLT
How to select DISTINCT items from XML via XSLT
How to convert Word (RTF) documents to XML for auto publication
Creating SQL Statements with XSLT

<< SEO System.XML >>

By	Mark Wilson I am the creator of TopXML. I am available for international and local (Australia) contracts. I am a Solution Architect/Business Analyst. I have worked in IT in several countries (NZ, Australia, South Africa, UK) building and training teams for government and very large non-governmental organizations. I am ex-Microsoft Consulting Services. I wrote the first book on Microsoft XML published in 2000 called XML Programming with VB and ASP. Most recently I have been building tools for the SEO industry. Ask me for a 37 point SEO health-checkup for your website.
First Posted	04/19/2001
Times viewed	350

Filtering unique values

Summary Following code allows you to filter data by one of the data-fields similar to a Select unique in SQL.

Imagine the follwing scenario: You have a list of customers define by country, name , address and phone. You want to display them by country but you only want to display the country name once and then list all the customer for that country. Or you want to diplay how many customer have an address in each country. Since the list of countries is part of the XML data you can't use a select="record[country='usa']" The solution uses some xpath funcionality to filter out all other siblings with the same country value. Note: I tried to use xsl:sort on the data first to create an ordered list but discovered that the Xpath functionality (preceding-sibling works on the original XML and not on the sorted list. Following the excellent advise of Marrow I have now added the Meunchian technique to filter unique values. This technique uses xsl:key and generate-id.

Additional information
<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:key name="scountry" match="customer" use="country"/> <xsl:template match="/"> <HTML> <BODY> <TABLE border="1"> <xsl:for-each select="//customer[generate-id(.) =generate-id(key('scountry', country))~"> <tr> <td> <xsl:value-of select="key('scountry', country)/country"/></td> <td> <xsl:for-each select="key('scountry', country)"> <table> <tr> <xsl:apply-templates select="name" /> <xsl:apply-templates select="address" /> <xsl:apply-templates select="phone" /> </tr> </table> </xsl:for-each> </td> </tr> </xsl:for-each> </TABLE> <p/> <TABLE> <xsl:for-each select="customers/customer"> <xsl:sort select="country"/> <TR> <xsl:choose> <xsl:when test="preceding-sibling::customer[country=current()/country~"></xsl:when> <xsl:otherwise > <td> <xsl:attribute name="rowspan"><xsl:value-of select="count(//customers/customer[country=current()/country~)" /></xsl:attribute> <xsl:attribute name="valign">Top</xsl:attribute> <xsl:value-of select="country" /> </td> </xsl:otherwise> </xsl:choose> <xsl:apply-templates select="name" /> <xsl:apply-templates select="address" /> <xsl:apply-templates select="phone" /> </TR> </xsl:for-each> </TABLE> <p/> <TABLE> <xsl:for-each select="customers/customer"> <xsl:sort select="country"/> <xsl:sort select="name"/> <xsl:choose> <xsl:when test="preceding-sibling::customer[country=current()/country~"></xsl:when> <xsl:otherwise > <TR> <td><xsl:value-of select="country" /> </td> <td> <xsl:value-of select="count(//customers/customer[country=current()/country~)" /></td> </TR> </xsl:otherwise> </xsl:choose> </xsl:for-each> </TABLE> </BODY> </HTML> </xsl:template> <xsl:template match="name"> <TD STYLE="font-size:14pt font-family:serif"> <xsl:apply-templates /> </TD> </xsl:template> <xsl:template match="address"> <TD> <xsl:apply-templates /> </TD> </xsl:template> <xsl:template match="phone"> <TD> <xsl:apply-templates /> </TD> </xsl:template> </xsl:stylesheet>

Further additional information
<?xml version="1.0" encoding="UTF-8"?> <customers> <customer> <country>USA</country> <name>Harry Jones</name> <address>120 Courtland Avanue</address> <phone>01-239845</phone> </customer> <customer> <country>USA</country> <name>Jeff Watson</name> <address>116 Foxfield Park</address> <phone>01-239845</phone> </customer> <customer> <country>France</country> <name>Eric Dubois</name> <address>27a Avenue Louise</address> <phone>01-239845</phone> </customer> <customer> <country>France</country> <name>Anna Leloup</name> <address>12 Rue Neuve</address> <phone>07-68789</phone> </customer> <customer> <country>France</country> <name>ChantalDubois</name> <address>12 Avenue Louise</address> <phone>01-239845</phone> </customer> <customer> <country>Ireland</country> <name>Niamh McCormack</name> <address>7 Botanic Avenue </address> <phone>01-239845</phone> </customer> </customers>

Rate this article on a scale of 1 to 10 (0 votes, average 0)

Your vote :

<< SEO System.XML >>


Leave a comment for this article
Your name
Your email (optional)
Your comment
Optional: Upload an attachment
Enter the code shown:



	Recent Jobs An immediate job opportunity as a Biztal Software Developers Needed in Charlotte, Sr. Software Engineer - Analytics Immediate Mainframe openings for Chennai Immediate TANDEM-TAL openings for Chenna View all Jobs (Add yours) View all CV (Add yours)

	answering service masks swimming pool builder free conference call water softener Teleconference Host Department NOLIMIT Web Hosting MSN sunglasses

	Partners Dream.In.Code dotNet Slackers dotNet Spider Your HTML Source VisualBuilder.com DevGuru Planet Source Code ZVON.ORG Web Design ASPAlliance XML Pitstop Scripts The Spot 4 SAP Bitshop Web Hosting

Email TopXML