Question: 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](http://pratchev.blogspot.com/2008/11/import-xml-file-to-sql-table.html):

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);

```
I tried this and for 975 rows from a 1MB XML file, this took about 2.5 minutes to execute on a very fast PC.

I switched to using OpenXml in a multi-step process and 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 this from here:
<http://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/>

Basically you load the XML into a table as a big blob of text, then you use OpenXml to process it.