December 20, 2010 at 10:38 am
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>
December 20, 2010 at 12:13 pm
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.
December 22, 2010 at 4:32 am
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>
<Name>Acme Corp</Name>
<Postcode>M1 4XY</Postcode>
</rec>
<rec>
<Item>SQL 2008 R2</Item>
<Value>5000</Value>
</rec>
<rec>
<Item>SQL 2005</Item>
<Value>4500</Value>
</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
December 22, 2010 at 6:33 am
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.
December 22, 2010 at 6:39 am
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
December 22, 2010 at 7:14 am
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"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy