How can I generate XML and apply a style sheet in t-sql?

Question

How can I generate XML and apply a style sheet in t-sql?

oradbguru

Hall of Fame

Points: 3126
More actions
November 28, 2012 at 8:57 am

#285227

Hello, I am beginning to write a procedure that will extract data from several table in XML format and apply a style sheet. I am looking for good examples on how to do this. Any code examples with explanations would be greatly appreciated.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Is the data already in XML format, or is it relational (tables+columns+rows)? Can't quite tell from the post.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

oradbguru Hall of Fame Points: 3126 More actions · Answer 2

Sorry, the data is in relational format, spread across several tables.

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

By "apply a style sheet" are you talking about applying an XML transform using XSLT or apply CSS formatting?

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

oradbguru Hall of Fame Points: 3126 More actions · Answer 4

I am thinking it is XSLT, but not positive. The file is a .xsd and looks like this:

<xs:element name="Person">

<xs:complexType>

<xs:sequence>

<xs:element name="Title" minOccurs="0">

<xs:simpleType>

<xs:restriction base="xs:string">

<xs:maxLength value="10" />

</xs:restriction>

</xs:simpleType>

</xs:element>

<xs:element name="FName">

<xs:simpleType>

<xs:restriction base="xs:string">

<xs:maxLength value="50" />

</xs:restriction>

</xs:simpleType>

</xs:element>

<xs:element name="MName" minOccurs="0">

<xs:simpleType>

<xs:restriction base="xs:string">

<xs:maxLength value="35" />

</xs:restriction>

</xs:simpleType>

</xs:element>

<xs:element name="LName">

<xs:simpleType>

<xs:restriction base="xs:string">

<xs:maxLength value="70" />

</xs:restriction>

</xs:simpleType>

</xs:element>

<xs:element name="NameSuffix" minOccurs="0">

<xs:simpleType>

<xs:restriction base="xs:string">

<xs:maxLength value="5" />

</xs:restriction>

</xs:simpleType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

David McKinney SSChampion Points: 10472 More actions · Answer 5

This isn't a stylesheet; it's an xsd schema. A stylesheet would be used to transform your xml (once you have it!) into a different format (xml / html /text or whatever), whereas the xsd defines how the xml you produce should look. I suggest you look in the books section - there's a pdf available for download called the Art of XSD by Jacob Sebastian - which specifically looks at XSD with SQL Server.

oradbguru Hall of Fame Points: 3126 More actions · Answer 6

Okay. If I were to obtain a style sheet, is there a good source I can reference with code examples? Thank you for your expertise.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

You can generate XML in T-SQL, and it is absolutely fine.

But applying XSLT? It is not good idea.

SQL SERVER is not a right tool for this, actually it doesn't have such capabilities at all.

You will need to write non-SQL application to do it. Maximum what you will be able to do is to use it from SQL, but even that - not a good idea at all...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

David McKinney SSChampion Points: 10472 More actions · Answer 8

Not sure if you want guidance on writing an xsl stylesheet or on applying the transformation.

The two articles below should give you some info on both of the above - but you'll have to adapt to your context.

http://www.sqlservercentral.com/articles/XSL/2831/ I've got the xml - now what

http://www.sqlservercentral.com/articles/Security/3179/ An xsl auditing solution

However I suspect that want you really want to do is build your select statement with FOR XML in such a way that it validates against the xsd document?

Hope this is of some help.

oradbguru Hall of Fame Points: 3126 More actions · Answer 9

So is it best to extract relational data in xml format and using another language like c#, apply a style sheet to transform it into the desired format?

David McKinney SSChampion Points: 10472 More actions · Answer 10

Again, it depends on what you want to do in your transformation.

If you include a reference to your stylesheet (where to find it) in your xml document, you can use Internet Explorer to do the transformation.

Basically, if you click to open such an xml document (where IE is the application associated with the .xml extension) then the transformed result will be rendered automatically by the browser (ASSUMING that the result of the transformation is HTML)

Otherwise, as you say C# may be used (or VBA / VB.net etc.). You could build a CLR function in SQL (a couple of lines.)

oradbguru Hall of Fame Points: 3126 More actions · Answer 11

The final XML data will be stored in an xml file and sent and fed into another off site 3rd part application.

David McKinney SSChampion Points: 10472 More actions · Answer 12

I'm reading between the lines.

----------

The 3rd party has given you an .xsd and asked you to produce an xml extract for them which corresponds to their format.

You suppose that you need xsl (xslt) to meet this request.

-----------

Well, you don't. You just need to generate the xml in the correct format direct from the database.

Or did I misread?

oradbguru Hall of Fame Points: 3126 More actions · Answer 13

Spot on my friend. Now, what is the best approach to this? Resources and code examples would be great. Thank you!

David McKinney SSChampion Points: 10472 More actions · Answer 14

already given.....now all you've got to do is read them.