Bulk XML Import in SQL Server

Question

I reviewed 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 he says that in some cases it executes VBScript code, and I could not find where VBScript should run. Is it possible to execute 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 

It looks like it could be executed in classic asp or something else, but I prefer to store it inside SQL Server. Does anyone know how to accomplish something like this with embedded SQL Server? or does anyone have a better method for mass importing XML into a SQL server?

Answer 1

SQL Server is able to read XML and embed it as needed. Here is an example of an XML file and an insert extracted 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 parses 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); 

Answer 2

I tried this and for 975 lines of 1 MB of XML file, it took about 2.5 minutes to execute on a very fast PC.

I switched to using OpenXml in a multi-step process, and the process takes less than a second.

 CREATE TABLE XMLwithOpenXML ( Id INT IDENTITY PRIMARY KEY, XMLData XML, LoadedDateTime DATETIME ) INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime) SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() FROM OPENROWSET(BULK 'clients.xml', SINGLE_BLOB) AS x; DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX) SELECT @XML = XMLData FROM XMLwithOpenXML WHERE ID = '1' -- The row to process EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML INSERT INTO Clients SELECT CustomerID, CustomerName FROM OPENXML(@hDoc, 'Clients/Client') WITH ( CustomerID [varchar](50) 'ID', CustomerName [varchar](100) 'Name' ) EXEC sp_xml_removedocument @hDoc GO 

I got it from here: http://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/

Basically you load XML into a table as a large piece of text, then you use OpenXml to process it.