June 15, 2017 at 9:24 am
I have done a lot of research on how to export a query to an xml file and felt that I finally made a break through by having xml data in an actual xml file, however, it appears that it is not keeping the "For XML Path" hierarchy in the xml file that I create. I am hoping someone can see where I went wrong. I am using the bcp method, however, if there is a better method, I am open to any suggestion. I am using SQL Server 2014,
DECLARE @FileName VARCHAR(50)
DECLARE @SQLCmd VARCHAR(500)
SELECT @FileName = 'C:\XMLtest\SampleXMLOutput.xml'
-- in this command, we are making sure there is only one ROOT node
SELECT @SQLCmd = 'bcp ' +
'"SELECT * ' +
' FROM Enterprise32.dbo.invoices_45Dayslate ' +
' FOR XML PATH (''Customer''), ROOT (''Overdue''), ELEMENTS "' +
' queryout ' +
@FileName +
' -w -T -S' + @@SERVERNAME
-- display command, for visual check
SELECT @SQLCmd AS 'Command to execute'
-- create the XML file
EXECUTE master..xp_cmdshell @SQLCmd
Basically this creates lines of xml data with tags(elements), but some are getting broken up as they go line to line.
Any help is greatly appreciated.
June 15, 2017 at 12:18 pm
Well, I will hang my head in shame, I just needed to hit a format button in my xml reader to get what I was looking for. :Whistling:
June 15, 2017 at 1:15 pm
pdeboer 19955 - Thursday, June 15, 2017 12:18 PMWell, I will hang my head in shame, I just needed to hit a format button in my xml reader to get what I was looking for. :Whistling:
But at least you posted back so anyone with the same issue would know how to handle it. Thanks for doing that and helping out others. No shame in that.
Sue
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply