I was looking at these examples on Microsoft.com here:
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;
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "c:\customermapping.xml", "c:\customers.xml"
Set objBL = Nothing
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:
<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);