Insert Hierarchical XML Data into Tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Insert Hierarchical XML Data into Tables

Author

Topic

ywb
Yak Posting Veteran

55 Posts

Posted - 2014-05-01 : 15:51:16

I have hierarchical XML data in this format:

' ========================================
DECLARE @Input xml;
SET @Input = '<?xml version="1.0" encoding="utf-8"?>
<customers>
<customer name="Peter">
<order date="04/15/2014">
<product id="Product_A" quantity="2" />
<product id="Product_B" quantity="5" />
</order>
<order date="04/19/2014">
<product id="Product_C" quantity="1" />
</order>
</customer>
<customer name="Mary">
<order date="04/25/2014">
<product id="Product_D" quantity="23" />
<product id="Product_E" quantity="1" />
<product id="Product_F" quantity="10" />
</order>
</customer>
<customer name="John" />
</customers>';
' ========================================

SELECT @Input;

And I would like to insert data into the following tables:

' ========================================
CREATE TABLE Customer (CustomerID smallint IDENTITY(1, 1) PRIMARY KEY, CustomerName varchar(10));
CREATE TABLE CustomerOrder (OrderID smallint IDENTITY(1, 1) PRIMARY KEY, OrderDate smalldatetime);
CREATE TABLE CustomerOrderDetails (OrderDetailsID smallint IDENTITY(1, 1) PRIMARY KEY, OrderID smallint, ProductID varchar(10), Quantity smallint);
' ========================================

I think I can flatten the data into a single temporary table and then loop through each row to populate the target tables, but I am wondering if there are other better and cleaner methods?

Thanks!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-01 : 17:48:05

You shouldn't have to loop through. You can write set based queries from your temporary table to insert into the three destination tables.

Regardless of whether you directly insert from the XML to the destination tables, or insert using an intermediate temporary table, you will have to insert the data into each table in a separate query. You cannot insert into more than one table in a single query.

ywb
Yak Posting Veteran

55 Posts

Posted - 2014-05-02 : 12:10:39

Hi James,

Do you have any links that show some examples how that is done?

If possible, I would like to insert directly from XML instead of having the temporary table, because in real life my XML data have many nodes and attributes which I already have to specify in the insert statements. Building that temporary table just makes it even more cumbersome.

Thanks for your reply.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-02 : 16:07:34

I didn't mean to just lecture and not post any code, but when I looked at your table structure, it did not seem right. You will need a customerid column in your CustomerOrder table to relate it to a specific customer. Also, it is not clear to me what you want to do if there are multiple customer nodes that have the same name. Using VARCHAR(10), smallint etc, while saving a little bit of storage space, may cause you to end up with overflow conditions if the order quantity is large etc. Given all that, I didn't feel comfortable writing some half-baked code that would end up being more work for you.

Nonetheless, here is my half-baked code. This may not do exactly what you want, but it is something to get you started. You can copy this code to an SSMS query window and run it to see what it does.

DECLARE @Input xml;
SET @Input = '<?xml version="1.0" encoding="utf-8"?>
<customers>
<customer name="Peter">
<order date="04/15/2014">
<product id="Product_A" quantity="2" />
<product id="Product_B" quantity="5" />
</order>
<order date="04/19/2014">
<product id="Product_C" quantity="1" />
</order>
</customer>
<customer name="Mary">
<order date="04/25/2014">
<product id="Product_D" quantity="23" />
<product id="Product_E" quantity="1" />
<product id="Product_F" quantity="10" />
</order>
</customer>
<customer name="John" />
</customers>';


IF (OBJECT_ID('tempdb..#tmp') IS NOT NULL) DROP TABLE #tmp;
SELECT * 
INTO #tmp
FROM
(
	SELECT
		-- assuming you want to treat each customer node as a separate customer even if the name is the same
		DENSE_RANK() OVER (ORDER BY a) AS customerId,
		a.value('@name','varchar(10)') AS NAME,
		DENSE_RANK() OVER (ORDER BY a,b) AS orderid,
		b.value('@date','smalldatetime') AS Date,
		c.value('@id','varchar(10)') AS ProductId,
		c.value('@quantity','smallint') AS Quantity
	FROM
		@Input.nodes('//customer') T1(a)
		OUTER APPLY a.nodes('order') T2(b)
		OUTER APPLY b.nodes('product') T3(c)
) s


IF (OBJECT_ID('tempdb..#Customers') IS NOT NULL) DROP TABLE #Customers;
CREATE TABLE #Customers(Id INT, NAME VARCHAR(10));
INSERT INTO #Customers
        ( Id, [Name])
SELECT customerid, NAME FROM #tmp GROUP BY customerid, Name;

IF (OBJECT_ID('tempdb..#CustomerOrder ') IS NOT NULL) DROP TABLE #CustomerOrder;
CREATE TABLE #CustomerOrder (OrderId INT , CustomerId INT NOT NULL, OrderDate SMALLDATETIME);
INSERT INTO #CustomerOrder
        ( orderid, CustomerId, OrderDate )
SELECT orderid, customerid, Date FROM #tmp WHERE Date IS NOT null GROUP BY orderid, customerid, Date


IF (OBJECT_ID('tempdb..#CustomerOrderDetails ') IS NOT NULL) DROP TABLE #CustomerOrderDetails;
CREATE TABLE #CustomerOrderDetails  (OrderDetailsID  INT NOT NULL IDENTITY(1,1), OrderID  INT, Quantity  INT);
INSERT INTO #CustomerOrderDetails
        ( OrderID, Quantity )
SELECT orderid, quantity FROM #tmp WHERE Date IS NOT null

SELECT * FROM #Customers
SELECT * FROM #CustomerOrder
SELECT * FROM #CustomerOrderDetails;

ywb
Yak Posting Veteran

55 Posts

Posted - 2014-05-07 : 13:01:00

Hi James,

You were right. I did miss that CustomerID column.

Thank you for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources