Parse XML into another table - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Parse XML into another table

Author

Topic

PeteLeHoq
Starting Member

37 Posts

Posted - 2011-12-26 : 13:39:48

I'm attempting to parse data from a table which has all my xml data in one record and column (xml_col).

I have the script below which works but just selects the first record from the xml, and inserts it into the other table. How can I get it to loop thru all records in the xml?

Example xml:
<exampledata>
<product>
<id>1</id>
<name>test</name>
<desc>testdesc</desc>
<price>$10</price>
</product>
<product>
<id>2</id>
<name>test2</name>
<desc>testdesc2</desc>
<price>$20</price>
</product>
</exampledata>

Script:


SELECT * FROM my_xml
DECLARE @docHandle int
DECLARE @xmlDocument XML
SELECT @xmlDocument = Xml_Col FROM my_xml
INSERT products(id,name,desc,price)
SELECT	
        t.v.value('id[1]', 'BIGINT') AS id,
        t.v.value('name[1]', 'varchar(50)') AS name,
	t.v.value('desc[1]', 'varchar(50)') AS desc,
	t.v.value('price[1]', 'BIGINT') AS price
FROM	@xmlDocument.nodes('/exampledata/product') AS t(v)
SELECT * FROM products

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-26 : 14:45:09

The intermediate variable @xmlDocument would store the data from just one row in the source table. Avoid that altogether like this:

SELECT * FROM my_xml
DECLARE @docHandle int
--DECLARE @xmlDocument XML
--SELECT @xmlDocument = Xml_Col FROM my_xml
INSERT products(id,name,desc,price)
SELECT	
        t.v.value('id[1]', 'BIGINT') AS id,
        t.v.value('name[1]', 'varchar(50)') AS name,
	t.v.value('desc[1]', 'varchar(50)') AS desc,
	t.v.value('price[1]', 'BIGINT') AS price
FROM	my_xml x
        cross apply x.Xml_Col.nodes('/exampledata/product') AS t(v)
SELECT * FROM products

PeteLeHoq
Starting Member

37 Posts

Posted - 2011-12-26 : 15:42:45

Excellent, works perfect, many thanks sunita.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-26 : 17:13:10

Very welcome, Peter.

Merry Christmas & Happy New Year!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-27 : 02:26:17

quote:
Originally posted by PeteLeHoq

I'm attempting to parse data from a table which has all my xml data in one record and column (xml_col).

I have the script below which works but just selects the first record from the xml, and inserts it into the other table. How can I get it to loop thru all records in the xml?

Example xml:
<exampledata>
<product>
<id>1</id>
<name>test</name>
<desc>testdesc</desc>
<price>$10</price>
</product>
<product>
<id>2</id>
<name>test2</name>
<desc>testdesc2</desc>
<price>$20</price>
</product>
</exampledata>

Script:

SELECT * FROM my_xml
DECLARE @docHandle int
DECLARE @xmlDocument XML
SELECT @xmlDocument = Xml_Col FROM my_xml
INSERT products(id,name,desc,price)
SELECT
t.v.value('id[1]', 'BIGINT') AS id,
t.v.value('name[1]', 'varchar(50)') AS name,
t.v.value('desc[1]', 'varchar(50)') AS desc,
t.v.value('price[1]', 'BIGINT') AS price
FROM @xmlDocument.nodes('/exampledata/product') AS t(v)
SELECT * FROM products

Here's a blog i wrote explaining how you nest it for populating two tables simultaneously

http://visakhm.blogspot.com/2010/04/using-xml-to-batch-load-master-child.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

PeteLeHoq
Starting Member

37 Posts

Posted - 2011-12-28 : 08:08:05

Thanks visak for that link, because it leads on to my next problem, but not sure how to modify my script to suit, also sunita may be able to help.

My xml now looks like this:

Under <price> I have <rrp> and <sale> so looks like so..
<price>
<rrp>
<sale>
</price>

<exampledata>
<product>
<id>1</id>
<name>test</name>
<desc>testdesc</desc>
<price>
<rrp>$30</rrp>
<sale>$20</sale>
</price>
</product>
<product>
<id>2</id>
<name>test2</name>
<desc>testdesc2</desc>
<price>
<rrp>$30</rrp>
<sale>$20</sale>
</price>
</product>
</exampledata>

But when I run my current script as per sunita's reply, it misses out the rrp and offer, I guess as these are not child nodes under "product".

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-28 : 09:15:12

You would need to navigate to the <rrp> and <sale> nodes, something like this:

t.v.value('price/rrp[1]', 'bigint') AS rrp,

That will of course, pick up only one value of rrp. If there is the possibility that there will be multiple nodes for rrp under a single price node, then you have to decide whether you want to pick just one, or do something else.

PeteLeHoq
Starting Member

37 Posts

Posted - 2011-12-28 : 10:16:54

thanks sunita. I found that these changes got to what I was looking for:

t.v.value('(price/rrp)[1]', 'bigint') AS rrp,

and

cross apply x.Xml_Col.nodes('//product') AS t(v)

Pete.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources