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! 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?