Control Freak Control Freak - 3 months ago 28
SQL Question

Bulk Import XML into SQL Server

I was looking at these examples on Microsoft.com here:

http://support.microsoft.com/kb/316005

http://msdn.microsoft.com/en-us/library/aa225754%28v=sql.80%29.aspx

But it's saying in part of it's steps that VBScript code has to be executed, and I wasn't able to find where the VBScript should be executed. Is it possible to be executed in SQL Server itself?

The code from the site looks something like this:

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data source=MySQLServer;
database=MyDatabase;uid=MyAccount;pwd=MyPassword"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "c:\customermapping.xml", "c:\customers.xml"
Set objBL = Nothing


This looks like it could be executed in classic asp or something, but I prefer to keep it inside SQL Server. Does anyone know how to execute something like this all with-in SQL Server? or does anyone have a better method for Bulk import XML into SQL server?

Answer

SQL Server is capable of reading XML and inserting it as you need. Here is an example of an XML file and insertion pulled from here:

XML:

<Products>
  <Product>
    <SKU>1</SKU>
    <Desc>Book</Desc>
  </Product>
  <Product>
    <SKU>2</SKU>
    <Desc>DVD</Desc>
  </Product>
  <Product>
    <SKU>3</SKU>
    <Desc>Video</Desc>
  </Product>
</Products>

Insert statement that is parsing the XML:

INSERT INTO Products (sku, product_desc) 
SELECT X.product.query('SKU').value('.', 'INT'),
       X.product.query('Desc').value('.', 'VARCHAR(30)')
FROM ( 
SELECT CAST(x AS XML)
FROM OPENROWSET(
     BULK 'C:\Products.xml',
     SINGLE_BLOB) AS T(x)
     ) AS T(x)
CROSS APPLY x.nodes('Products/Product') AS X(product);