SQL Server: Import XML document with one-to-many relationship (insert into multiple tables)

Question

I'm using SQL Server 2012 to import a simple XML document. Here's a sample of what the XML looks like:

<Orders>
    <Order>
        <Customer>Bob Smith</Customer>
        <Address>123 Main St, Anytown, NY</Address>
        <OrderItems>
            <Item>
                <ItemName>Table</ItemName>
                <Quantity>1</Quantity>
            </Item>
            <Item>
                <ItemName>Chair</ItemName>
                <Quantity>4</Quantity>
            </Item>
        </OrderItems>
    </Order>
    <Order>
        <Customer>Jane Doe</Customer>
        <Address>456 Broadway Ave, Someplace, TX</Address>
        <OrderItems>
            <Item>
                <ItemName>Banana Slicer</ItemName>
                <Quantity>1</Quantity>
            </Item>
        </OrderItems>
    </Order>
    <Order>
        <Customer>Joe Public</Customer>
        <Address>789 Euclid Rd, Random, ID</Address>
        <OrderItems>
            <Item>
                <ItemName>Hammer</ItemName>
                <Quantity>1</Quantity>
            </Item>
            <Item>
                <ItemName>Nails</ItemName>
                <Quantity>50</Quantity>
            </Item>
            <Item>
                <ItemName>Chisel</ItemName>
                <Quantity>2</Quantity>
            </Item>
        </OrderItems>
    </Order>
</Orders>

Note that each order can have one or more items in it.

Here are the destination tables (using temp tables for now):

CREATE TABLE dbo.#Order
(
    [OrderID] int IDENTITY(1001,1) PRIMARY KEY,
    [Customer] varchar(200) NOT NULL,
    [Address] varchar(200) NOT null
);

CREATE TABLE dbo.#OrderItem
(
    [OrderItemID] int IDENTITY(5001,1) PRIMARY KEY,
    [OrderID] int
        FOREIGN KEY REFERENCES dbo.#Order(OrderID)
        ON DELETE CASCADE,
    [ItemName] varchar(100) NOT NULL,
    [Quantity] int NOT NULL
);

The above should be imported as follows:

OrderID     Customer    Address
-------     --------    -------
1001        Bob Smith   123 Main St, Anytown, NY
1002        Jane Doe    456 Broadway Ave, Someplace, TX
1003        Joe Public  789 Euclid Rd, Random, ID

OrderItemID OrderID ItemName        Quantity
----------- ------- --------        --------
5001        1001    Table           1
5002        1001    Chair           4
5003        1002    Banana Slicer   1
5004        1003    Hammer          1
5005        1003    Nails           50
5006        1003    Chisel          2

Is there a way to insert all the data into both the Order and the OrderItem tables without using a cursor? I'm not against using a cursor for this, but I would like to know if there is a simpler, set-based alternative. The tricky part is that OrderItem needs to know about the inserted OrderID of each order that was imported.

Here's my initial attempt at importing the data (using a cursor). I'm using temp tables (instead of permnanent tables) and hardcoding the XML to make it easier to just copy and run this code:

-------------------------------------------------------------------------------
-- Create Temp Tables
-------------------------------------------------------------------------------

IF OBJECT_ID('tempdb.dbo.#OrderItem') IS NOT NULL
    DROP TABLE #OrderItem
GO

IF OBJECT_ID('tempdb.dbo.#Order') IS NOT NULL
    DROP TABLE #Order
GO

CREATE TABLE dbo.#Order
(
    [OrderID] int IDENTITY(1001,1) PRIMARY KEY,
    [Customer] varchar(200) NOT NULL,
    [Address] varchar(200) NOT null
);

CREATE TABLE dbo.#OrderItem
(
    [OrderItemID] int IDENTITY(5001,1) PRIMARY KEY,
    [OrderID] int
        FOREIGN KEY REFERENCES dbo.#Order(OrderID)
        ON DELETE CASCADE,
    [ItemName] varchar(100) NOT NULL,
    [Quantity] int NOT NULL
);

-------------------------------------------------------------------------------
-- Define Sample XML Document
-------------------------------------------------------------------------------

DECLARE @xml xml = '
    <Orders>
        <Order>
            <Customer>Bob Smith</Customer>
            <Address>123 Main St, Anytown, NY</Address>
            <OrderItems>
                <Item>
                    <ItemName>Table</ItemName>
                    <Quantity>1</Quantity>
                </Item>
                <Item>
                    <ItemName>Chair</ItemName>
                    <Quantity>4</Quantity>
                </Item>
            </OrderItems>
        </Order>
        <Order>
            <Customer>Jane Doe</Customer>
            <Address>456 Broadway Ave, Someplace, TX</Address>
            <OrderItems>
                <Item>
                    <ItemName>Banana Slicer</ItemName>
                    <Quantity>1</Quantity>
                </Item>
            </OrderItems>
        </Order>
        <Order>
            <Customer>Joe Public</Customer>
            <Address>789 Euclid Rd, Random, ID</Address>
            <OrderItems>
                <Item>
                    <ItemName>Hammer</ItemName>
                    <Quantity>1</Quantity>
                </Item>
                <Item>
                    <ItemName>Nails</ItemName>
                    <Quantity>50</Quantity>
                </Item>
                <Item>
                    <ItemName>Chisel</ItemName>
                    <Quantity>2</Quantity>
                </Item>
            </OrderItems>
        </Order>
    </Orders>';

-------------------------------------------------------------------------------
-- Query XML Document
-------------------------------------------------------------------------------

--SELECT
--  Tbl.Col.value('Customer[1]', 'varchar(200)') AS [Customer],
--  Tbl.Col.value('Address[1]', 'varchar(200)') AS [Address],
--  Tbl.Col.query('./OrderItems/Item') AS [ItemsXML]
--FROM
--  @xml.nodes('//Order') AS Tbl(Col)


-------------------------------------------------------------------------------
-- Import XML document (Attempt 1 - using a cursor)
-------------------------------------------------------------------------------

DECLARE @OrderNode xml;
DECLARE @OrderID int;

DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
    SELECT Tbl.Col.query('.') FROM @xml.nodes('//Order') AS Tbl(Col)

OPEN cur

FETCH NEXT FROM cur INTO @OrderNode

WHILE @@FETCH_STATUS = 0 BEGIN

    ------------------------------------------
    -- Import order
    ------------------------------------------

    INSERT INTO #Order
    (
        [Customer],
        [Address]
    )
    SELECT
        Tbl.Col.value('Customer[1]', 'varchar(200)'),
        Tbl.Col.value('Address[1]', 'varchar(200)')
    FROM @OrderNode.nodes('Order') AS Tbl(Col);


    ------------------------------------------
    -- Get the inserted order ID
    ------------------------------------------

    SELECT @OrderID = SCOPE_IDENTITY();


    ------------------------------------------
    -- Import order items
    ------------------------------------------

    INSERT INTO #OrderItem
    (
        OrderID,
        ItemName,
        Quantity
    )
    SELECT
        @OrderID,
        Tbl.Col.value('ItemName[1]', 'varchar(100)'),
        Tbl.Col.value('Quantity[1]', 'int')
    FROM @OrderNode.nodes('Order/OrderItems/Item') AS Tbl(Col);


    -------------------------------------------------------------------------------
    -- Move on to next order
    -------------------------------------------------------------------------------

    FETCH NEXT FROM cur INTO @OrderNode

END

CLOSE cur
DEALLOCATE cur

-------------------------------------------------------------------------------
-- Show results
-------------------------------------------------------------------------------

SELECT * FROM #Order
SELECT * FROM #OrderItem

You asked, "Is there a way to insert all the data into both the Order and the OrderItem tables without using a cursor?" Yes. Insert it all the XML into a temp table with redundant data to begin with and then extract the specific information for each order and then for each ordered item. to the appropriate tables. or perhaps: http://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/ or http://stackoverflow.com/questions/5152671/how-to-load-an-xml-file-into-a-database-using-an-ssis-package — xQbert, Jul 23 '14 at 19:14

score 1 · Accepted Answer · edited May 23 '17 at 12:05

To extend on the comment by xQbert, if your sample XML data is in an XML variable @x, you can use the below to put all orders & order items into a temp table:

DECLARE @x XML = '... your sample xml ...';

SELECT  Ord.n.value('for $i in . return count(../*[. << $i]) + 1', 'int') AS OrderNbr
        , Ord.n.value('./Customer[1]','varchar(200)') AS Customer
        , Ord.n.value('./Address[1]','varchar(200)') AS Address
        , Item.n.value('./ItemName[1]','varchar(200)') AS ItemName
        , Item.n.value('./Quantity[1]','int') AS Quantity
INTO    #Orders
FROM    @x.nodes('/Orders/Order') AS Ord(n)
        CROSS APPLY Ord.n.nodes('./OrderItems/Item') AS Item(n);

Then you need to do two inserts, one to the parent table which includes DISTINCT master order records, preserving the identity values with an OUTPUT clause, and the second insert would include all order items and use the preserved PK values from the first insert. See this link:

How can I INSERT data into two tables simultaneously in SQL Server?

Thanks, I like this approach. If there is an existing field in the XML that can be used to uniquely identify master records, then I think koppinjo's answer may be cleaner (no need to insert into a temp table first with redundant data). However, if that's not the case, then this is the way to go. — Lukas S., Jul 24 '14 at 13:34

score 1 · Answer 2 · answered Jul 24 '14 at 11:27

Building further upon Kevin Suchlicki's answer, if you declare your XML as an XML variable @x, then plug in the following, you will get the results in your original post:

DECLARE @Order TABLE ([OrderID] int IDENTITY(1001,1) PRIMARY KEY,
    [Customer] varchar(200) NOT NULL,
    [Address] varchar(200) NOT NULL)

DECLARE @OrderItem TABLE (
    [OrderItemID] int IDENTITY(5001,1) PRIMARY KEY,
    [OrderID] int,
    [ItemName] varchar(100) NOT NULL,
    [Quantity] int NOT NULL
)

INSERT INTO @Order (Customer, Address)
SELECT  t.c.value('(Customer)[1]','varchar(200)') AS 'Customer',  
        t.c.value('(Address)[1]','varchar(200)') AS 'Address'
FROM    @x.nodes('Orders/Order') t(c);


INSERT INTO @OrderItem (OrderID, ItemName, Quantity)
SELECT  
        Ordr.OrderID
        , Item.n.value('./ItemName[1]','varchar(200)') AS ItemName
        , Item.n.value('./Quantity[1]','int') AS Quantity
FROM    @x.nodes('Orders/Order') Ord(n)
    CROSS APPLY Ord.n.nodes('./OrderItems/Item') AS Item(n)
    JOIN @Order Ordr ON Ordr.Customer = Ord.n.value('./Customer[1]','varchar(200)')

SELECT *
FROM @Order

SELECT *
FROM @OrderItem

Obviously table variables are used here, but you can just as easily swap them out for temp tables.

The only issue I see with this is it assumes is unique among all nodes. If there is a field in the XML that can be used to uniquely identify the master records, then this approach would work (the JOIN condition would need to be changed to use that field). It's shorter and cleaner in that case. However, if there is no such field, then inserting all data into a temp table first (with an extra counter column to identify master records), as in Kevin Suchlicki's answer, may be the best route. — Lukas S., Jul 24 '14 at 13:27

SQL Server: Import XML document with one-to-many relationship (insert into multiple tables)

2 Answers2