Converting SQL Server Data into XML

Hi Folks!

I have the following sql script I wrote using Explicit Option to convert data from SQL Server tables into a single XML file. I am aware of the tedious nature of the select statements, but this seems to the only option I have to depict parent-child nature of the data in XML format and also to schedule it as a job to run via SQL Server Agent.

My problem is that as I run this using the following command, I get "There is insufficient system memory to run this query" error.
I am using the following commnd:
exec master..xp_cmdshell 'bcp "EXEC swr_cv2..sproc_BuildXMLTree" queryout "C:\test.xml" -U -P -c -r -t'

Is there any way I can tune my query to fix that error?

Thanks so much for your help!

-ParulHi Folks!

I am aware of the tedious nature of the select statements,
-Parul

Really tedious Parul,For the first time I am seeing so big a proc... rather a Mamoth ;)|||I know, that's because the XML feed layout is huge as well...is there a way to tune a FOR XML EXPLICIT query?|||I know, that's because the XML feed layout is huge as well...is there a way to tune a FOR XML EXPLICIT query?
You can increase the max server memory,but I am not sure whether that will help you...check this (http://sqljunkies.com/PrintContent.aspx?type=tutorial&id=0D4FF40A-695C-4327-A41B-F9F2FE2D58F6)|||Thanks! I am really hoping not to tamper with the server settings as it is a production database. I am hoping I can may be build a temp table around this data, any ideas?|||Thanks! I am really hoping not to tamper with the server settings as it is a production database. I am hoping I can may be build a temp table around this data, any ideas?
have you tried XPath queries in this case ?|||No, I am not sure if I can write XPath Queries and schedule them via SQL Server Agent. Do you know if that is possible? Or do I need a .net application for it?|||No, I am not sure if I can write XPath Queries and schedule them via SQL Server Agent. Do you know if that is possible? Or do I need a .net application for it?
check this (http://www.aspfree.com/c/a/MS-SQL-Server/XML-and-the-SQL-2000-Server-part-3-XML-Data-with-XPath-Queries/1/) ...|||Thanks! This seems helpful in generating an XML layout; however, I am still wondering how I can schedule this to run daily...|||Thanks! This seems helpful in generating an XML layout; however, I am still wondering how I can schedule this to run daily...
An easy option can be Altova xmlspy 2006.
Try this www.altova.com (http://www.altova.com) and download XMLSPY 2006|||Thanks! BTW, I got my sql query to work! I replaced all the unions with inserts - now I am inserting all the data into an actual physical data, and then running FOR XML EXPLICIT on it. It seems to be working fine now!

I had a question though - do you know if there is a sql command to merge two files. Basically, I want to merge the header for the xml file with actual xml data in another file - is that possible without running any DOS commands?

Thanks so much!