Generating XML for unrelated queries with differing columns and column counts

Question

Post reply

Generating XML for unrelated queries with differing columns and column counts

mick L

SSCommitted

Points: 1526
More actions
February 15, 2010 at 10:05 am

#216765

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

Viewing 7 posts - 1 through 6 (of 6 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Well, it's certainly obtuse, but this is one way to do it:

SELECT (

Select CAST(C AS XML)

FROM (

Select (select * from sys.tables for xml path('table'), type) AS [C]

UNION ALL

Select (select * from sys.columns for xml path('column'), type)

) a

for xml path(''), type

)

for xml path('rootName'), type

[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]

mick L SSCommitted Points: 1526 More actions · Answer 2

Thanks for this solution. This works fine against tables. How would I go about this if each set of elements were created from a SQL statement involving joins?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

mick L (2/16/2010)
Thanks for this solution. This works fine against tables. How would I go about this if each set of elements were created from a SQL statement involving joins?

I believe that you would just hide the joins and other details, etc. in derived tables (i.e., subqueries) or CTEs.

[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]

mick L SSCommitted Points: 1526 More actions · Answer 4

I posted on msdn and got this solution

http://social.msdn.microsoft.com/Forums/en/sqlxml/thread/38016fd9-4a7d-48d5-850b-dc01e370f3af

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

mick L (2/16/2010)
I posted on msdn and got this solution
http://social.msdn.microsoft.com/Forums/en/sqlxml/thread/38016fd9-4a7d-48d5-850b-dc01e370f3af

Sooo... Five hours after I worked to produce a completely correct and valid answer to your question (and in less than forty minutes to turn it around), you go somewhere else and ask them the same question again?

Is there really any reason why we should put any effort into trying to answer your questions in the future?

[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 6

RBarryYoung (2/16/2010)
mick L (2/16/2010)
I posted on msdn and got this solution
http://social.msdn.microsoft.com/Forums/en/sqlxml/thread/38016fd9-4a7d-48d5-850b-dc01e370f3af
Sooo... Five hours after I worked to produce a completely correct and valid answer to your question (and in less than forty minutes to turn it around), you go somewhere else and ask them the same question again?
Is there really any reason why we should put any effort into trying to answer your questions in the future?

Well, I apologize. It turns out that the MSDN forums (which I use too) have clocks that are off by five hours for me. Which means that you actually posted there before I answered here, and not four and a half hours after I answered here. Again, my apologies.

[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]