return multiple xml datatables

Question

return multiple xml datatables

MJ-727860

SSC Veteran

Points: 202
More actions
May 15, 2008 at 11:06 am

#185605

I know you can return 1 datatable with the following to fill an xml file in a web app:
select * from myTable FOR XML path('dbname'), root('root');
How do you return multiple xml datatables to fill an xml file?

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

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

John Beggs SSCommitted Points: 1921 More actions · Answer 1

I am not 100% on what result you are looking for, but here are a couple of options.

If you are looking to join multiple result sets as a single XML output, I do not believe that is possible using T-SQL. It certainly could be done using the CLR, but I am not sure of the value that would give.

Depending on the platform of your web app, you may find it easier to join the result sets in the web app itself. If you are using some flavor of an MS platform, I would recommend that you actually let the SQL server return normal output into your dataset and then convert the dataset to XML in the web app. If you fill a dataset from a command that returns multiple result sets, they are constructed as datatables with in the dataset, making the conversion to XML very simple.

Hopefully this helps, but if not more detail on what you are trying to accomplish will help provide a more specific answer.

GL!

MJ-727860 SSC Veteran Points: 202 More actions · Answer 2

Hi. I'm trying to pass data as xml for security purposes. This is the link I'm referring to: http://msdn.microsoft.com/en-us/library/ms345137.aspx but I need to return multiple datatables to populate a form. Thank you for responding.

Daniel Kaiser Grasshopper Points: 14 More actions · Answer 3

What you need to do is nest the SQL select statements. Something like this

Select

(

SELECT

'123 any Street' as "DisplayAddress",

'123432' as "LatLong/Latitude",

'-2342424' as "LatLong/Longitude"

For XML PATH(''), type) as "FoundAddress",

(SELECT

mp_Location.LocationID as "Property/@LocationID",

mp_Location.Company as "Property/Company",

mp_Location.Address as "Property/Address",

mp_Location.PrimaryCity as "Property/City",

mp_Location.State as "Property/State",

mp_Location.PostalCode as "Property/PostalCode",

mp_Location.CountryRegion as "Property/CountryRegion",

mp_Location.Phone as "Property/Phone",

(SELECT mp_Brand.BRAND_ID as "Brand/@BrandID",

mp_Brand.BRAND_NAME as "Brand"

FROM mp_Brand with (NoLock) INNER JOIN

mp_LocationToBrand with (NoLock) ON mp_Brand.BRAND_ID = mp_LocationToBrand.BRAND_ID

WHERE mp_LocationToBrand.Location_ID = mp_Location.LocationID

FOR XML PATH(''),type) as "Property/Brands"

FROM mp_Location

WHERE mp_Location.LocationID = '1496099'

For XML PATH(''), type) as "Properties"

FOR XML PATH(''), ROOT('BFRetailSearch')

This will generate XML like in the attachment

John Beggs SSCommitted Points: 1921 More actions · Answer 4

Thanks for posting that Daniel.

I had tried several different nested queries and couldn't get any of them to return in the desired format. Now I get to see where I was off! 🙂

MJ-727860 SSC Veteran Points: 202 More actions · Answer 5

Thank you Daniel! 😀 I was looking into writing a function that returned an xml data type of concatenated xml strings for the multiple select statements.