SQL Server Performance Forum – Threads Archive
Import XML file via Bulk Insert?
Hi all, Looking at trying to get an XML file imported into a table for manipulation. Given this piece from BOL, I have hopes that Bulk Insert in 2005 can handle XML:quote:
Bulk Exporting or Importing SQLXML Documents
To bulk export or import SQLXML data, use one of the following data types: Data type Effect
SQLCHAR or SQLVARYCHAR
The data is sent in the client code page or in the code page implied by the collation). The effect is the same as specifying the DATAFILETYPE = ‘char’ without specifying a format file. SQLNCHAR or SQLNVARCHAR
The data is sent as Unicode. The effect is the same as specifying the DATAFILETYPE = ‘widechar’ without specifying a format file. SQLBINARY or SQLVARYBIN
The data is sent without any conversion.
Bulk Exporting or Importing SQLXML Documents
To bulk export or import SQLXML data, use one of the following data types: Data type Effect
SQLCHAR or SQLVARYCHAR
The data is sent in the client code page or in the code page implied by the collation). The effect is the same as specifying the DATAFILETYPE = ‘char’ without specifying a format file. SQLNCHAR or SQLNVARCHAR
The data is sent as Unicode. The effect is the same as specifying the DATAFILETYPE = ‘widechar’ without specifying a format file. SQLBINARY or SQLVARYBIN
The data is sent without any conversion.
Am I mistaken? If so, is SSIS the only way to get XML data into a SQL table? Panic, Chaos, Disorder … my work here is done –unknown
check this:
http://msdn2.microsoft.com/en-us/library/ms171806.aspx
Thanks… So it appears there isn’t a SQL/T-SQL way of doing this, or a simple utility to call. With all the MS noise about support for XML, I find that quite disappointing. While I can’t seem to use the SSIS XML task to import XML (since I have a dynamic schema that can change on every generation of the incoming file), I would have hoped that in a situation with a defined schema (XSD), at least BCP, and possibly Bulk Insert, would have been able to handle XML files. That’s a real pity, especially when combined with the fact that SSIS doesn’t cater for an XML destination. I realize that SSIS has enhanced the ability to call custom code to handle all of this, but (a) I think some of the things around XML should be common components that MS could/should have provided, (b) while I know I need to learn how to do these things, up until now, time and the need to use VBScript, or VB or C# hasn’t been there. Now I’ll have to try and farm this off to a developer (all of whom are equally busy)… … anyone feel sorry for me yet? Oh well, </sympathy-mode > </whining> <working>…. Panic, Chaos, Disorder … my work here is done –unknown
]]>