How To: Insert identity values using XML Bulk Load. (22168 Requests) You can use XML Bulk Load to insert records into tables that use identity columns. Here is a simple example to demonstrate how this can be done. First create a table in your database.
CREATE TABLE Cust ( CustomerID CompanyName City GO int IDENTITY PRIMARY KEY,
varchar(20) NOT NULL, varchar(20) DEFAULT 'Seattle')
Next create the schema for the table and save it. I have saved the schema as c:\samples\iden.xsd.
type="xsd:string" />
Then create some sample data to load into the database and save it.
Hanari Carnes NY Toms Spezialitten LA Victuailles en stock
Then to load the data use the following VBScript code.
Set oBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.2.0")
oBL.ConnectionString = "provider=SQLOLEDB.1;server=(local)\VSdotNet;" & _ "database=test;Trusted_Connection=Yes;" oBL.KeepIdentity = False
oBL.Execute "c:\samples\iden.xsd", "c:\samples\iden.xml"
Be sure to modify the connection string with your own values. The limitation on identity columns in XML Bulk Load is that you cannot use it with multiple tables. For instance, if you had an orders table that had a foreign key for the customers table, it would not be bulk loadable. This is a limitation of XML Bulk Loading. How To: Insert and Update with OpenXML (15750 Requests) I would like to update existing record if the ID exists in the table, otherwise insert the record(node) into the table..
This is pretty easy to do. Below is an example that uses a table named 'test' that has an ID column called xmlID and a data column called xmlData.
declare @i int
exec sp_xml_preparedocument @i output, ' '
insert into test select xmlID, xmlData from OpenXml(@i, 'mydata/test') with (xmlID int, xmlData nvarchar(30)) where xmlID not in (select xmlID from test)
update test set test.xmlData = ox.xmlData from OpenXml(@i, 'mydata/test') with (xmlID int, xmlData nvarchar(30)) ox where test.xmlID = ox.xmlID
exec sp_xml_removedocument @i
So you can use the same openxml pointer to do an update and an insert.
I want to import XML into my database, where do I start? (14077 Requests) I would start with the Books Online that is installed with SQL server. Take a look at the OPENXML topic (or do a search). You will also want to download SQLXML 3.0 and install the SDK so that you can read up on Bulk Loading XML documents.
There are also a could of books that have just been released on the topic. Programming Microsoft SQL Server 2000 With XML by MS Press SQL Server 2000 XML by WROX Pick them up at bookpool.com (40% discount usually). Unfortunately I haven't had time to read either one so I can't say which is better.