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?

  • SQL Exception When Sending values from jtable to sql
  • Update a table using JOIN in SQL Server?
  • XML parsing: line 1, character 63, A string literal was expected
  • Lag() with conditon in sql server
  • Currently running query inside a stored procedure
  • What are some ways of accessing Microsoft SQL Server from Linux?
  • Also want to know, is there any method other than BCP to achieve this?

  • How to find & replace space from hyperlink src attribute SQL Server
  • How to Stop Inserting?
  • SQL Server Full Text Search Very Slow
  • Transpose some columns to row
  • Architecture of team foundation server (tfs) excel add-in
  • Transact SQL CONTAINS not returning match for identical strings with a wildcard
  • One Solution collect form web for “How To Save XML Query Results to a File”

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.