Using XML to batch load master child

Recently I had a requirement where it was required to populate master child tables with batch data. I chose to pass the data in XML format and then use XML functions to shred and populate the tables with required data. I'm posting the approach I used here so it might benefit others. I've used a xml variable to store the value and using it I'm populating the two tables.

The scenario used below is the case where Order and Orderitems are to be populated using XML bulk data containing order details using a customer lookup table

The detail solution is as follows:-

--Creation of tables as well lookup table data

CREATE TABLE Customers

(

CustomerID int IDENTITY(1,1),

CustomerName varchar(100)

)

INSERT Customers(CustomerName)

VALUES('ABC School'),

('Zeolite Associates'),

('MNR Constructions'),

('Carton Paints')

GO

CREATE TABLE Orders

(

OrderID int IDENTITY(1,1),

OrderHeader varchar(500),

CustomerID int,

OrderDate datetime

)

CREATE TABLE OrderItems

(

OrderItemID int IDENTITY(1,1),

OrderID int,

ItemName varchar(500),

ItemQty Numeric(10,2),

UnitCost money

)

The xml we pass is as follows

Now the solution

DECLARE @INSERTED_VALUES table

(

OrderID int,

OrderHeader varchar(500) NOT NULL

)

insert into dbo.Orders

(OrderHeader,

CustomerID,

OrderDate)

OUTPUT inserted.OrderID,inserted.OrderHeader INTO @INSERTED_VALUES

SELECT t.OrderHeader,c.CustomerID,t.OrderDate

FROM

(

select a.b.value('OrderHeader[1]','varchar(500)') AS OrderHeader,

a.b.value('OrderDate[1]','datetime') AS OrderDate,

a.b.value('CustomerName[1]','varchar(500)') AS CustomerName

from @data.nodes('/Orders/Order') a(b)

)t

JOIN Customers c

ON c.CustomerName = t.CustomerName

insert into dbo.OrderItems

(

OrderID,

ItemName,

ItemQty,

UnitCost

)

select t.OrderID,

m.n.value('ItemName[1]','varchar(500)'),

m.n.value('ItemQty[1]','Numeric(10,2)'),

m.n.value('UnitCost[1]','money')

from @INSERTED_VALUES t

CROSS JOIN @data.nodes('/Orders/Order') a(b)

CROSS APPLY b.nodes('OrderItems/OrderItem')m(n)

WHERE a.b.exist('./OrderHeader/text() [. = sql:column("t.OrderHeader")]') = 1

SELECT * FROM Orders

SELECT * FROM OrderItems

Result from Order Items Table

Result from Orders Table