Howto: Produce 1 XML dataset from several queries

Question

Howto: Produce 1 XML dataset from several queries

sotn

SSCrazy

Points: 2672
More actions
December 20, 2010 at 10:38 am

#232175

I have many tables all with different fields and I need to export the contents of each of them as 1 single XML resultset with a root node
I am trying to do this through a stored procedure.
For example.
SELECT fieldA, fieldB
FROM table1 FOR XML AUTO, ELEMENTS, ROOT ('Root')
SELECT fieldC, fieldD
FROM table2 FOR XML AUTO, ELEMENTS
I need to get the result as
<root>
<rec>
<field1>value</field1>
<field2>value</field2>
</rec>
<rec>
<field1>value from fieldC</field1>
<field2>value from fieldD</field2>
</rec>
</root>

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Unfortunately, we don't have any table def or sample data to play with.

But based on the identical structure within each rec node I'd recommend to use a UNION statement to merge the two result sets and apply the XML formatting afterwards.

For a coded version please provide ready to use sample data as described in the first link in my signature.

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]

sotn SSCrazy Points: 2672 More actions · Answer 2

I have a table named CUS

This has fields ID, Name, Postcode

I also have a table named PRD

This has fields Item, Value

My end result is this

<Root>

<rec>

<ID>ABC123value</ID>

</rec>

<rec>

</rec>

<rec>

</rec>

</Root>

I can get close to this with the queries

SELECT ID, Name, Postcode

FROM CUS FOR XML AUTO, ELEMENTS, ROOT ('Root')

SELECT Item, Value

FROM PRD FOR XML AUTO, ELEMENTS

But I need a single result set not 2.

Any ideas how to produce this format?

There are more fields but if I can get this working then I can extend it for all my fields.

Thanks

LutzM SSC Guru Points: 107049 More actions · Answer 3

sotn (12/22/2010)
Any ideas how to produce this format?

Yes (concept as mentioned before).

For a coded version please provide ready to use sample data as described in the first link in my signature.

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]

sotn SSCrazy Points: 2672 More actions · Answer 4

Hi Lutz,

thanks for the reply.

I am a little confused as my previous post shows that my tables have completely different field names and number of fields so I cannot 'easily' use union and it has data in it as well.

my CUS table has 3 fields and 1 record

My PRS table has 2 fields and 2 records

LutzM SSC Guru Points: 107049 More actions · Answer 5

sotn (12/22/2010)
Hi Lutz,
thanks for the reply.
I am a little confused as my previous post shows that my tables have completely different field names and number of fields so I cannot 'easily' use union and it has data in it as well.
my CUS table has 3 fields and 1 record
My PRS table has 2 fields and 2 records

I tend to overlook stuff like that if there's nothing to copy and paste into SSMS where I can benefit from automagic code formatting as well as syntax highlight. I also would have spotted it immediately when I would have tried to help you with a coded version. But, unfortunately... (I guess you know how this would continue...).

I'll step back for now until I can be more helpful.

Currently filed under "help us help you pending" 😉

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]