Subject: Re: [xsl] Converting a SQL XML Resultset using XSL... From: "Kirk Allen Evans" <kaevans@xxxxxxxxxxxxx> Date: Thu, 28 Feb 2002 13:59:19 -0800 |
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: [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
Current Thread |
---|
|
<- Previous | Index | Next -> |
---|---|---|
Re: [xsl] Converting a SQL XML Resu, Joerg Heinicke | Thread | RE: [xsl] Converting a SQL XML Resu, SANSONE, AARON M [No |
[xsl] html tags in xml elements, Alice Tull | Date | [xsl] ?Dataislands?, Rosa Cheng |
Month |