Bulk Inserts with XML

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)
/* Create a new procedure using an XML parameter */
CREATE PROCEDURE dbo.insert_orderXML_sp
      @orderDate        DATETIME
    , @customer_id      INT
    , @orderDetailsXML  XML
    DECLARE @myOrderID INT;
    INSERT INTO dbo.orders
        , customer_id    
        , @customer_id
    SET @myOrderID = SCOPE_IDENTITY();
    INSERT INTO dbo.orderDetails
        , lineItem
        , product_id
    SELECT @myOrderID
         , myXML.value('./@lineItem', 'int')
         , myXML.value('./@product_id', 'int')
    FROM @orderDetailsXML.nodes('/orderDetail') As nodes(myXML);
/* 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.

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , , , , , . Bookmark the permalink.

2 Responses to Bulk Inserts with XML

  1. Chris says:

    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.

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

    … will return…


    HTH… thanks for reading! :)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>