SQL Server Performance

Import XML file via Bulk Insert?

Discussion in 'SQL Server 2005 General Developer Questions' started by SQL_Guess, Jun 12, 2006.

  1. SQL_Guess New Member

    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.



    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
  2. ranjitjain New Member

  3. SQL_Guess New Member

    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

Share This Page