BCP SQL XML

The other day I was facing a task where i was supposed to export data from one system to another. The data had to be XML and validated against a specific schema. Once i got the XML right i decided to use BCP to create the 25000+ XML files that was the result of the data export. The syntax i used was like this (simplyfied): BCP “SELECT……FOR XML PATH” QUERYOUT “Path to my XML file” -w -T -S Where -w specifies Unicode characted format, -T specifies Trusted connection and -S Specifies my server.

No problem to generate the files but when i look at one of the files just to verify that everything is ok i see that the XML is no longer valid. When i investigate this further it seems that BCP has exported the data into the files in lines that are cut after 1024 characters leaving the XML invalid. Normally having the XML as one long string is no problem but in this case the string was broken at the wrong place, in the middle of a tag making it invalid.

I then tried a different approach: Created a table and a column with datatype XML, inserted my XML result to this table and then again exported the data with BCP using QUERYOUT from the table that i created. This time the XML looked fine!! The conclusion is that, for some reason, i get a different result when selecting a XML column rather than creating the XML on the fly when using BCP with the QUERYOUT option.