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
