Bulk Insert XML document into SQL table. How?

Question

Post reply

Bulk Insert XML document into SQL table. How?

JMI

SSChampion

Points: 11831
More actions
December 9, 2008 at 8:04 am

#154998

MSDN page http://msdn.microsoft.com/en-us/library/ms191184.aspx
explains that you can use Bulk Insert with XML data source file
but fails to show a good example.
Their format file looks like this:
9.0
1
1 SQLBINARY 0 0 "\0\0\0\0z" 1 xCol ""
But what if I want to transfer XML data into rowsets?
I mean, is it possible to use regular format file technique like:
9.0
15
1 SQLCHAR 0 255 "\t" 1 TRANSIT_NUM SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 255 "\t" 2 TRANS_EFF_DT SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 255 "\t" 3 TRANSIT_NAME SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 255 "\t" 4 VPO_CD SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 255 "\t" 5 VPO_NAME SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 255 "\r" 6 REGION_CD
Does anybody have any knowledge about this?
My goal is to use Bulk Insert with XML source document
and format file to map the data to SQL table.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

There are several options but I think this is what you are looking for.

* Noel

JMI SSChampion Points: 11831 More actions · Answer 2

It's a COM object.

But I was looking for a simple BCP command

using format file. Similar to BCP text file to SQL table.

I understand maybe BCP can not be used

with XML when you want to transform xml into rowsets.

Something like,

BULK INSERT 'TableName', 'C:\Files\Customers.XML'
WITH
(FORMATFILE = 'C:\Files\Customers_Schema.XML')

I totally realize that. I just want to confirm that I don't have to spend more time on trying to bcp XML in a simple way.

noeld SSC Guru Points: 96590 More actions · Answer 3

Can you post examples of destination table and source xml file?

With Operowset you can do the parsing on SQL Server itself:

INSERT Table

SELECT CONVERT(xml, BulkColumn, 2)

FROM OPENROWSET(Bulk 'c:\file.xml', SINGLE_BLOB) data

IF you get your "rowterminator" right you can probably use the BULK INSERT into a blob but in that case I would rather use the above snippet.

* Noel

JMI SSChampion Points: 11831 More actions · Answer 4

noeld,

Your example will only insert XML document into "BulkColumn" (XML data type)

column of table T1.

That's not what I'm looking for.

What I'm looking for is to transfer XML data into SQL table rowsets:

XML column "CustomerID" --> T1.CustomerID

XML column "FirstName" --> T1.FirstName

and so on....

Actually I cannot afford to do research on this longer so I decided

to use SQLXMLBulkLoad COM object.

I'm gonna create a small VBScript and load XML into SQL table through it:

Dim ArgObj, var1, var2
Set ArgObj = WScript.Arguments
'First parameter
var1 = ArgObj(0)
'Second parameter
var2 = ArgObj(1)
Msgbox "Variable 1=" & var1 & " Variable 2=" & var2
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data source=BNSLKZGH4W\SQLExpress;database=Test;integrated security=SSPI"
objBL.ErrorLogFile = "c:\Files\XML\f1\error.log"
objBL.Execute var1, var2
Set objBL = Nothing
' run mapping xml data file
' COM.vbs c:\Files\XML\f1\Commcard_Schema.XML C:\Files\XML\f1\Commcard.XML

I did a test already and it worked.