XML to HTML Table with Sorting and Distinct

VictorMk · April 21st, 2004, 09:25 PM

I am have XML document where I need to first select some of the nodes via some sort of distinct function and then for each type select sorted list of other nodes. Can someone send some examples of doing this?
Sincerely, Victor.

pgtips · April 22nd, 2004, 02:50 AM

why don't you show us what your xml looks like (or maybe just the relevant bit if its large), and also what you want the output to look like, and we'll see what we can do.

VictorMk · April 22nd, 2004, 12:22 PM

Thanks for the reply.
Hereâs what I need. I have a complex xml structure that has several request codes, and a xmlCategoryNodes with attributes: Code and Type.
I need to reformat this into the HTML where for each xmlCategoryTypes (across all Request Codes) I get the lowest of Codes (sort order does not matter at this point). Once I understand how the process works I hope then I can reformat into a specific HTML table structure.

Here is what the resulting format should look like:

xmlCategoryNode Type - SU
-- xmlCategoryNode Code 11

xmlCategoryNode Type - OB
-- xmlCategoryNode Code 8I

xmlCategoryNode Type - OS
-- xmlCategoryNode Code 6A

Hereâs the source XML:

<xmlResponse>
    <MessageHeader SegId="MSGHDR">
        <MessageId>CCC</MessageId>
        <SessionId>1234</SessionId>
    </MessageHeader>
    <xmlCodeResponse SegId="CAT2">
        <Request Code="111"/>
        <xmlCategoryNode Code="11">
            <CatType>SU</CatType>
        </xmlCategoryNode>
        <xmlCategoryNode Code="ST">
            <CatType>SU</CatType>
        </xmlCategoryNode>
        <xmlCategoryNode Code="9A">
            <CatType>OB</CatType>
        </xmlCategoryNode>
        <xmlCategoryNode Code="8I">
            <CatType>OB</CatType>
        </xmlCategoryNode>
        <xmlCategoryNode Code="7A">
            <CatType>OS</CatType>
        </xmlCategoryNode>
        <xmlCategoryNode Code="6A">
            <CatType>OS</CatType>
        </xmlCategoryNode>
        <xmlCategoryNode Code="OV">
            <CatType>OS</CatType>
        </xmlCategoryNode>
    </xmlCodeResponse>
    <xmlCodeResponse SegId="CAT2">
        <Request Code="222"/>
        <xmlCategoryNode Code="11">
            <CatType>SU</CatType>
        </xmlCategoryNode>
        <xmlCategoryNode Code="7A">
            <CatType>OS</CatType>
        </xmlCategoryNode>
        <xmlCategoryNode Code="6A">
            <CatType>OS</CatType>
        </xmlCategoryNode>
    </xmlCodeResponse>
    <xmlCodeResponse SegId="CAT2">
        <Request Code="333"/>
        <xmlCategoryNode Code="11">
            <CatType>SU</CatType>
        </xmlCategoryNode>
        <xmlCategoryNode Code="7A">
            <CatType>OS</CatType>
        </xmlCategoryNode>
        <xmlCategoryNode Code="6A">
            <CatType>OS</CatType>
        </xmlCategoryNode>
    </xmlCodeResponse>
</xmlResponse>

Sorry for the long xml source, but I think thatâs the only way to relay the requirements.
Thanks, V.

pgtips · April 23rd, 2004, 04:04 AM

Here you go Victor. Its a standard method known as Meunchian Grouping which uses keys to group the source XML by CatType. If you're not familiar with this method there's a v good explanation here http://www.jenitennison.com/xslt/gro...muenchian.html

Code:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:key name="ResponseByCat" match="xmlCodeResponse/xmlCategoryNode" use="CatType"/>
    <xsl:template match="/">
        <html>
            <head>
                <title>Muenchian Grouping Example</title>
            </head>
            <body>
                <h3>Muenchian Grouping Example</h3>

                <xsl:apply-templates select="xmlResponse/xmlCodeResponse/xmlCategoryNode[generate-id()=generate-id(key('ResponseByCat', CatType)[1])]">
                    <xsl:sort select="CatType" order="ascending"/>
                </xsl:apply-templates>
            </body>
        </html>
    </xsl:template>
    <xsl:template match="xmlCategoryNode">
        <xsl:value-of select="CatType"/> | 

        <xsl:for-each select="key('ResponseByCat', CatType)">
            <xsl:sort select="@Code" order="ascending"/>

            <xsl:if test="position()=1">
                <xsl:value-of select="@Code"/><br/>
            </xsl:if>
        </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

This produces the following output:
OB | 8I
OS | 6A
SU | 11

If you have any problems converting the output to the format you want, or indeed if you have any other questions about the stylesheet, just ask.

rgds
Phil

VictorMk · April 23rd, 2004, 06:29 PM

Phil,
Thanks a million for your help. This sheds light on the XSL sorting for me.
However, hereâs my dilemma, ultimately I need to display the data on an ASP.NET web page. I am using an XML API, where I issue a request and get 2 XML responses. Then I have to combine the data from both responses sort them use the lowest price for both each type of product, as well as each product and then present it users.
The code sample you helped me with allows me to group by category type, but I was having trouble getting unique products per category type.
And in the end I am wondering is it best to do with XSL style sheets or do it in some form with .NET XPath objects.
Any advice would be greatly appreciated.
Thanks, V.