efficient way to put multiple tables in one xml output

hi guys,

i'm looking for alternatives on how to put multiple tables in one xml output.

i have this .net program that has been passed to me that currently loops through some tables and calls an sp that returns a query that has a FOR XML AUTO, ELEMENTS on it, then appends <Extract> ... </Extract> as a root node for all the extracted tables then saves it as an xml file. i'm trying to optimize it by limiting the number reads to the db server, so one option is for me do the loop inside the sp.

is this efficient? any snippet? by the way, i'm using sql2k

thanks guys.

/rh4m1ll3

hi,

Bit confused with the requirement. plz let me know what you are doing exactly from the below 2 approaches:

go to one table 1 - execute sp - return xml file1 - add <Extract> ... </Extract> as root node

go to one table 2 - execute sp - return xml file2 - add <Extract> ... </Extract> as root node

go to one table 3 - execute sp - return xml file3 - add <Extract> ... </Extract> as root node

..........

( or )

go to one table1 - get some details

go to one table2 - get some details

go to one table3 - get some details

...........

pass details to sp - return xml file - add <extract> .. <extract> as root node

confirm what you are doing?

Regards,

kwaerol.

|||alright this is the current setup

here's the psuedo of the current c# app

Code Snippet

for each table.. {
x += executescalar("theSPwithForXML") //appends each result
}
x = "<Extract>" + x + "</Extract>"
// then saves x as xml file

what i'm trying to achieve is

x = executescalar("theSPthatAppendsAllTablesIntoOneXML")
// then saves x as xml file

since you cannot assign the result of a query with a FOR XML to a variable in SQL2K, i'm looking for other alternatives or workarounds in TSQL, so that "theSPthatAppendsAllTablesIntoOneXML" can return a single xml with all the tables that i need to convert.

the last resort that i'm looking at is returning multiple result instead

CREATE PROCEDURE theSPthatAppendsAllTablesIntoOneXML
AS

SELECT *
FROM Table1
FOR XML AUTO, ELEMENTS

SELECT *
FROM Table2
FOR XML AUTO, ELEMENTS

SELECT *
FROM Table3
FOR XML AUTO, ELEMENTS

-- etc etc

GO|||

Hi,

You are correct. As per my knowledge we cannot take the xml output into a local variable in sql2k.

I feel that every time u do some sql operation and get some xml output

At last want to append <extract> nodes and save that as xml file.

If I am correct. Why don't you get the resultsets at one slot from db and then make use of Load xml using XMldatadocument.

If required you can create one schema file and make use of it to load the xml data document from dataset (having multiple Tables)

Regards,

Subbu