0

I'm having a hard time working out how to write XML to my hard drive in SQL Server. I gather I could do something like this:

EXEC xp_cmdshell 'bcp "@@@INSERT SELECT/FOR XML QUERY@@@" queryout "C:\test.xml" -T -c -t'

But my SELECT/FOR XML query runs to about 40 lines so I'd rather not. I tried storing the XML as @storedXML, saving it in a table and then querying that table with bcp:

CREATE TABLE xmlOutput(outXML xml)
INSERT INTO xmlOutput(outXML)
VALUES (@storedXML)

DECLARE @sqlCmd VARCHAR(1000)
SET @sqlCmd = 'bcp "SELECT * FROM xmlOutput" queryout C:\test.xml -c -T -t'
EXEC xp_cmdshell @sqlCmd

But all that gets me is "Unable to open BCP host data-file".

Sergei Walankov
  • 125
  • 9
  • Have you seen this post: https://stackoverflow.com/questions/18619061/unable-to-open-bcp-host-data-file – Max Zolotenko Dec 21 '19 at 09:00
  • 1
    Have you considered putting your 40 lines of code in a stored procedure then using `bcp "exec myDB.dbo.myProcedure" queryout "C:\test.xml" -T -c`? – AlwaysLearning Dec 21 '19 at 10:25
  • Thank you - the stored procedure eventually did the trick, after I sorted out further issues involving folder permissions. It was also necessary to include -S and my server/instance name. – Sergei Walankov Dec 23 '19 at 07:44

0 Answers0