Generating XML

Question

Generating XML

Kal Penn

Ten Centuries

Points: 1273
More actions
January 22, 2010 at 8:15 am

#71894

How do I generate XML from a query in SQL Server, if its possible?

Viewing 10 posts - 1 through 9 (of 9 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

You should look for "FOR XML" clause in BOL or search this site for "XML Workshop Sebastian". The latter will result in a list of great articles by Jacob Sebastian.

Or you could provide some ready to use sample data as per the first link in my signature together with your expected result set and there will be several people around to show you what needs to be done (if possible, of course)...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Assuming you are on SQL Server 2005, here is a very simple example of what Lutz was talking about:

SELECT *

FROM INFORMATION_SCHEMA.TABLES

FOR XML PATH

If you execute this with "Results to grid" in Management Studio, you will see one row with one column, that has a hyperlink in it. If you click on that hyperlink, it will take you to an expanded XML window that will show you the whole thing, formatted and indented.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Kal Penn Ten Centuries Points: 1273 More actions · Answer 3

Thank you guys, I am going through Jacb sebastians workshop and his tutorials are just phenomenal. Thanks

LutzM SSC Guru Points: 107049 More actions · Answer 4

Glad you found it as useful as I did!

If you have any specific questions let us know and we'll try to find an answer 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

mick L SSCommitted Points: 1526 More actions · Answer 5

Folks, I want to generate XML for a basic structure where there is several differing element types listed within the root with no relationships.

Say I have products, customers and offices tables

I want to just query each table and have each set of elements listed within the document with no parent-child relationships.

<?xml version="1.0" encoding="UTF-8"?>

<THISISTHEROOT>

<Customer>

<firstName>Jim</firstName>

<lastName>Flim</lastName>

</Customer>

<Customer>

<firstName>Bob</firstName>

<lastName>Flob</lastName>

</Customer>

<product>

<productname> SoapPowder</productname>

<price>50</productname>

</product>

<product>

<productname> CheeseFlour</productname>

<price>20</productname>

</product>

<office>

<address> 15 ship lane </address>

<owner>Davys properties>

</office>

<office>

<address> 15 turnip lane </address>

<owner>Jones properties>

</office>

</THISISTHEROOT>

How do I construct a FOR XML statement where the ordinary SQL statement would involve just calling

select * from products

select * from customers

select * from offices

but have these in XML and within a root element.

Thanks

Mick

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

mick L (2/15/2010)
Folks, I want to generate XML for a basic structure where there is several differing element types listed within the root with no relationships.
Say I have products, customers and offices tables
I want to just query each table and have each set of elements listed within the document with no parent-child relationships.
<?xml version="1.0" encoding="UTF-8"?>
<THISISTHEROOT>
<Customer>
<firstName>Jim</firstName>
<lastName>Flim</lastName>
</Customer>
<Customer>
<firstName>Bob</firstName>
<lastName>Flob</lastName>
</Customer>
<product>
<productname> SoapPowder</productname>
<price>50</productname>
</product>
<product>
<productname> CheeseFlour</productname>
<price>20</productname>
</product>
<office>
<address> 15 ship lane </address>
<owner>Davys properties>
</office>
<office>
<address> 15 turnip lane </address>
<owner>Jones properties>
</office>
</THISISTHEROOT>
How do I construct a FOR XML statement where the ordinary SQL statement would involve just calling
select * from products
select * from customers
select * from offices
but have these in XML and within a root element.
Thanks
Mick

Two things: First, you need to post your question as a separate thread in this forum, so that it can receive the attention it deserves.

Secondly, when you do, please use the [ code="xml" ] tags to better format your XML code, with indentation, without having to entitize it first.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Here's an example using the [ code="xml" ] tag. If you want to see exactly how it is done, just hit the "Quote" button over my post:

<TABLES>