Last week, I blogged about how to perform one-to-many inserts with table-valued parameters, a feature new in 2008. For those who do not yet have 2008 or will not have it in the near future, it may still be beneficial to use XML for bulk inserts.
Here’s a pretty simple example of how to accomplish this:
/* Create some tables to work with */ CREATE TABLE dbo.orders ( order_id INT IDENTITY(1,1) Not Null , orderDate DATETIME Not Null , customer_id INT Not Null CONSTRAINT PK_orders PRIMARY KEY CLUSTERED(order_id) ); CREATE TABLE dbo.orderDetails ( orderDetail_id INT IDENTITY(1,1) Not Null , order_id INT Not Null , lineItem INT Not Null , product_id INT Not Null CONSTRAINT PK_orderDetails PRIMARY KEY CLUSTERED(orderDetail_id) CONSTRAINT FK_orderDetails_orderID FOREIGN KEY(order_id) REFERENCES dbo.orders(order_id) ); Go /* Create a new procedure using an XML parameter */ CREATE PROCEDURE dbo.insert_orderXML_sp @orderDate DATETIME , @customer_id INT , @orderDetailsXML XML AS BEGIN SET NOCOUNT ON; DECLARE @myOrderID INT; INSERT INTO dbo.orders ( orderDate , customer_id ) VALUES ( @orderDate , @customer_id ); SET @myOrderID = SCOPE_IDENTITY(); INSERT INTO dbo.orderDetails ( order_id , lineItem , product_id ) SELECT @myOrderID , myXML.value('./@lineItem', 'int') , myXML.value('./@product_id', 'int') FROM @orderDetailsXML.nodes('/orderDetail') As nodes(myXML); SET NOCOUNT OFF; END GO /* Call our stored procedure */ EXECUTE dbo.insert_orderXML_sp @orderDate = '2008-01-01' , @customer_id = 101 , @orderDetailsXML = '<orderDetail lineItem="1" product_id="123" /> <orderDetail lineItem="2" product_id="456" /> <orderDetail lineItem="3" product_id="789" /> <orderDetail lineItem="4" product_id="246" /> <orderDetail lineItem="5" product_id="135" />'; /* Check our data */ SELECT * FROM dbo.orders; SELECT * FROM dbo.orderDetails; /* Clean up our mess */ DROP PROCEDURE insert_orderXML_sp; DROP TABLE dbo.orderDetails; DROP TABLE dbo.orders; |
I’ve found that this is more efficient when performing large parent/child inserts, i.e. 1 parent record to 100 child records. Keep in mind that there’s a point where doing an insert with XML is more expensive than using a traditional INSERT stored procedure. I haven’t run any tests yet to help define what that threshold is… more to come on this in the near future.
More on the Nodes() Method can be found here in Books Online: http://msdn.microsoft.com/en-us/library/ms188282(SQL.90).aspx
Update: I’ve just learned that the “value” keyword is case-sensitive. Apparently my code box plug-in was defaulting “value” to “VALUE.” 🙂
Here’s the error message you’ll get if you don’t have “value” in lower-case:
Cannot find either column “myXML” or the user-defined function or aggregate “myXML.VALUE”, or the name is ambiguous.
Wow. It looks like they’ve really streamlined the XML interface. No more need for this syntax:
sp_xml_preparedocument @docHandle OUTPUT, @data;
select …
EXEC sp_xml_removedocument @docHandle ;
That was incredibly cumbersomein 2005. I will have to play with this new version.
Do you know, by chance if they changed their default interpretation of “” to be a NULL value in stead of an empty string? That caused problems on a recent project that tried to populate a nullable integer field.
Hi Chris! Yes, they really have made working with XML much easier (and faster!) in 2005.
Using the nodes method above, you would now get a 0 value when encountering “”, i.e.
[cc lang=”tsql”]
DECLARE @myXML xml;
SET @myXML = ‘
SELECT myColumnValue = myXML.value(‘./@myColumn’, ‘int’)
FROM @myXML.nodes(‘/myRow’) AS nodes(myXML);
[/cc]
… will return…
myColumnValue
————————-
0
NULL
1
HTH… thanks for reading! 🙂