Can I redistribute SQL Express?
http://blogs.msdn.com/sqlexpress/archive/2004/07/04/172952.aspx
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 setuphere'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
