How To Save XML Query Results to a File

Question

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?

Which client are you use? – Hamlet Hakobyan Sep 12 '13 at 22:02 — Hamlet Hakobyan, Sep 12 '13 at 22:02
Im using sql server management studio 2012 – Naveen Sep 12 '13 at 22:25 — Naveen, Sep 12 '13 at 22:25

score 5 · Accepted Answer · answered Sep 13 '13 at 07:56

You could try using xp_cmdshell....

-- 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 xp_cmdshell.

Click here for more details.

What an answer, I spent days trying to find this solution and when I found this answer my issue was resolved in seconds. Wow thanks @davmos — Sizons, Sep 26 '17 at 08:04

How To Save XML Query Results to a File

1 Answers1

Linked