This is the mail archive of the
xsl-list@mulberrytech.com
mailing list .
Re: Converting a SQL XML Resultset using XSL...
- From: "Kirk Allen Evans" <kaevans at xmlandasp dot net>
- To: <xsl-list at lists dot mulberrytech dot com>
- Date: Thu, 28 Feb 2002 13:59:19 -0800
- Subject: Re: [xsl] Converting a SQL XML Resultset using XSL...
- References: <6A1BB598448ED31198DC0008C75B715309A4914E@ems1000-05.monsanto.com>
- Reply-to: xsl-list at lists dot mulberrytech dot com
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@xmlandasp.net
"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@monsanto.com>
To: <XSL-List@lists.mulberrytech.com>
Sent: Thursday, February 28, 2002 9:35 AM
Subject: [xsl] 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