Re: Converting a SQL XML Resultset using XSL...

Before generating a complex XSLT solution, have you considered altering the
XML returned from the SQL Server query?  Using the FOR XML clause, you can
pull the XML back in whatever format you desire, and this would limit the
complexity of the transformation required.  In other words, the following
query requires no transformation because the resulting XML is already in the
format that you require.


SELECT DISTINCT
            1        as Tag,
            Null     as Parent,
            sometable.id     as [individual!1!id],
            sometable.display_name     as [individual!1!id],
            null                as [tag!2!name],
              null             as [tmp!3!name],
            null              as [tmp!3!score]
    FROM sometable

UNION ALL


SELECT DISTINCT
            2        as Tag,
            1     as Parent,
            sometable.id,
            sometable.display_name,
            someothertable.tag_name,
             null,
            null
    FROM sometable inner join someothertable on sometable.id =
someothertable.fk

UNION ALL


SELECT DISTINCT
            3        as Tag,
            2     as Parent,
            sometable.id,
            sometable.display_name,
            someothertable.tag_name,
             sometmptable.tmp_name,
            sometmptable.score
    FROM your_join_here

ORDER BY [individual!1!id],[tag!2!name],[tmp!3!name]

FOR XML EXPLICIT


Kirk Allen Evans
kaevans@xxxxxxxxxxxxx
"XML and ASP.NET", New Riders Publishing
Available at Amazon.com
http://www.amazon.com/exec/obidos/ASIN/073571200X/


----- Original Message -----
From: "SANSONE, AARON M [Non-Pharmacia/1000]" <aaron.m.sansone@xxxxxxxxxxxx>
To: <XSL-List@xxxxxxxxxxxxxxxxxxxxxx>
Sent: Thursday, February 28, 2002 9:35 AM
Subject:  Converting a SQL XML Resultset using XSL...


> We are trying to convert a SQL Result set that is in XML to a business
layer
> XML Format.  We are having trouble trying to get the correct Stylesheet to
> generate the desired result.  We have a kludge of for-each loops and xsl
> keys that I am reluctant to show you here (fear of confusion).  This is a
> tabular view of a sinpet of what the SQL Query returns:
>
> id display_name tag_name tmp_name score
> 253241 Test1 XXX15 C 1
> 253241 Test1 XXX15 T 0
> 253241 Test1 XXX55 G 1
> 253241 Test1 XXX55 T 0
> ...
> 253242 Test2 XXX15 C 4
> 253242 Test2 XXX15 T 3
>
> Here is a snipet of our XML Result set (based on the above query results):
>
> <?xml version="1.0"?>
> <page>
> <rowset name="scores">
> <row>
> <id>253241</id>
> <display_name>Test1</display_name>
> <tag_name>XXX15</tag_name>
> <tmp_name>C</tmp_name>
> <score>1</score>
> </row>
> <row>
> <id>253241</id>
> <display_name>Test1</display_name>
> <tag_name>XXX15</tag_name>
> <tmp_name>T</tmp_name>
> <score>0</score>
> </row>
> <row>
> <id>253241</id>
> <display_name>Test1</display_name>
> <tag_name>XXX55</tag_name>
> <tmp_name>G</tmp_name>
> <score>1</score>
> </row>
> <row>
> <id>253241</id>
> <display_name>Test1</display_name>
> <tag_name>XXX55</tag_name>
> <tmp_name>T</tmp_name>
> <score>0</score>
> </row>
>
> ...
>
> <row>
> <id>253242</id>
> <display_name>Test2</display_name>
> <tag_name>XXX15</tag_name>
> <tmp_name>C</tmp_name>
> <score>4</score>
> </row>
> <row>
> <id>253242</id>
> <display_name>Test2</display_name>
> <tag_name>XXX15</tag_name>
> <tmp_name>T</tmp_name>
> <score>3</score>
> </row>
> </rowset>
> </page>
>
>
> After applying the stylesheet we would like the following result:
>
>
> <individual id="253241" name="Test1">
> <tag name="XXX15">
> <tmp name="C" score="1"/>
> <tmp name="T" score="0"/>
> </tag>
> <tag name="XXX55">
> <tmp name="G" score="1"/>
> <tmp name="T" score="0"/>
> </tag>
> </individual>
> ...
> <individual id="253242" name="Test2">
> <tag name="XXX15">
> <tmp name="C" score="4"/>
> <tmp name="T" score="3"/>
> </tag>
> </individual>
>
>
> Can anyone offer some assistance?  If you really want our current (non
> working) xsl I could post that...
>
> Thanks,
>
> Aaron
>
>  XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list
>
>


 XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list