How To Save XML Query Results to a File
I have an SQL query and I am using For XML Path to generate the result as an XML.
Can anyone help me about converting that XML output into “a.xml” file and save in a particular folder of a computer?
Also want to know, is there any method other than BCP to achieve this?
One Solution collect form web for “How To Save XML Query Results to a File”
You could try using
-- Read your query results into an XML variable DECLARE @xml AS XML = (SELECT * FROM YourTable FOR XML PATH) -- Cast the XML variable into a VARCHAR DECLARE @xmlChar AS VARCHAR(max) = CAST(@xml AS VARCHAR(max)) -- Escape the < and > characters SET @xmlChar = REPLACE(REPLACE(@xmlChar, '>', '^>'), '<', '^<') -- Create command text to echo to file DECLARE @command VARCHAR(8000) = 'echo ' + @xmlChar + ' > c:\test.txt' -- Execute the command EXEC xp_cmdshell @command
You could also try a Powershell command if you wanted a bit more control e.g. to set encoding…
DECLARE @command VARCHAR(8000) = 'powershell -Command "Set-Content -Encoding UTF8 C:\test.txt \"' + @xmlChar + '\""'
A few notes…
There is an 8000 character length limit on the command, so it’s no good for large files.
If you save the file to a mapped drive, it will look for that drive on the database server. So, C:\ will be referring to the C:\ drive of the server, not where you are running Management Studio.
Special permissions are required to run
Click here for more details.