Bulk insert XML data into SQL Server

Question

Using the table structure below how would I create a bcp bulk insert that would handle the XML data. It is important that this is run from command line.

CREATE TABLE [dbo].[MyTable](
[Id] [uniqueidentifier] NOT NULL DEFAULT (newid()),
[MyXmlField] [xml] NULL

Thanks in advance...

Answer 1

The basic syntax for using bcp is:

bcp <table_name> <direction> <file_name> <options> 

Where the arguments take the following values:

• table_name is the fully qualified name of the table. For example, you might use inventory.dbo.fruits to insert records into the fruits table, owned by the database owner, in the inventory database.
• direction indicates whether you want to import (“in” direction) or export (“out” direction) data.
• file_name is the full path to the file. For example, you could import the file C:\fruit\inventory.txt.
• options allow you to specify parameters for the bulk operation. For example, you can specify the maximum number of errors allowed with the –m option. You may also use the –x option to specify an XML file format. Consult Microsoft’s bcp documentation for a full list.

Will need more info to know what switches to use, but it should be somehting like

bcp database.dbo.MyTable in "C:\folder\xmlfile.xml" -c -T

-c Performs the operation using a character data type.
-T Specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security.

Also here is Microsoft's bcp Utility which should help you with knowing what switches to use.

Answer 2

use -N switch if your file contains unicode characters.

-N : Performs the bulk-copy operation using the native (database) data types of the data for noncharacter data, and Unicode characters for character data. This option offers a higher performance alternative to the -w option, and is intended for transferring data from one instance of SQL Server to another using a data file. It does not prompt for each field. Use this option when you are transferring data that contains ANSI extended characters and you want to take advantage of the performance of native mode.