Reading XML values of multiple element and display

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 2005 Forums

Transact-SQL (2005)

Reading XML values of multiple element and display

Author

Topic

r03
Starting Member

2 Posts

Posted - 2009-09-03 : 22:05:51

I need to pass the following XML to a stored procedure:

<Products>
<Item>
<id>A671D</id>
<name>John</name>
<dateOfPurchase>21 Aug 09</dateOfPurchase>
</Item>
<Item>
<id>A892E</id>
<name>Tom</name>
<dateOfPurchase>22 Aug 09</dateOfPurchase>
</Item>
</Products>'

The data of the xml should be inserted into a table which resembles the following:

id name dateOfPurchase
--- ------ ------------------
A671D John 21 Aug 09
A892E Tom 22 Aug 09

How should I write the query in this case? Thanks again.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-04 : 00:45:59

[code]
is this u want?
declare @r xml

select @r = '<Products>
<Item>
<id>A671D</id>
<name>John</name>
<dateOfPurchase>21 Aug 09</dateOfPurchase>
</Item>
<Item>
<id>A892E</id>
<name>Tom</name>
<dateOfPurchase>22 Aug 09</dateOfPurchase>
</Item>
</Products>'

select [id],[name],[dateOfPurchase]
from (
SELECT
row_number() over(partition by t.n.value('local-name(.)[1]', 'VARCHAR(100)')
order by t.n.value('local-name(.)[1]', 'VARCHAR(100)')) as rid,
t.n.value('local-name(..)[1]', 'VARCHAR(100)') AS ParentNodeName,
t.n.value('local-name(.)[1]', 'VARCHAR(100)') AS NodeName,
t.n.value('text()[1]', 'VARCHAR(100)') AS NodeText
FROM @r.nodes('/*/*/*') AS t(n))s
pivot(max(nodetext) for nodename in ( [id],[name],[dateOfPurchase]))p

select max(case when nodename = 'id' then nodetext end) as id,
max(case when nodename = 'name' then nodetext end) as name,
max(case when nodename = 'dateofpurchase' then nodetext end) dateofpurchase
from (
SELECT
row_number() over(order by t.n.value('local-name(..)[1]', 'VARCHAR(100)')) as rid,
t.n.value('local-name(..)[1]', 'VARCHAR(100)') AS ParentNodeName,
t.n.value('local-name(.)[1]', 'VARCHAR(100)') AS NodeName,
t.n.value('text()[1]', 'VARCHAR(100)') AS NodeText
FROM @r.nodes('/*/*/*') AS t(n))s where rid <=3
union all
select max(case when nodename = 'id' then nodetext end) as id,
max(case when nodename = 'name' then nodetext end) as name,
max(case when nodename = 'dateofpurchase' then nodetext end) dateofpurchase
from (
SELECT
row_number() over(order by t.n.value('local-name(..)[1]', 'VARCHAR(100)')) as rid,
t.n.value('local-name(..)[1]', 'VARCHAR(100)') AS ParentNodeName,
t.n.value('local-name(.)[1]', 'VARCHAR(100)') AS NodeName,
t.n.value('text()[1]', 'VARCHAR(100)') AS NodeText
FROM @r.nodes('/*/*/*') AS t(n))s where rid >=3 and rid <=6
[/code]

matty
Posting Yak Master

161 Posts

Posted - 2009-09-04 : 00:58:45

declare @ProductXml xml

select @ProductXml = '<Products>
<Item>
<id>A671D</id>
<name>John</name>
<dateOfPurchase>21 Aug 09</dateOfPurchase>
</Item>
<Item>
<id>A892E</id>
<name>Tom</name>
<dateOfPurchase>22 Aug 09</dateOfPurchase>
</Item>
</Products>'


SELECT  
	 u.value('(id)[1]'		, 'varchar(50)'	) AS id,
	 u.value('(name)[1]'		, 'varchar(50)'	) AS name,
 	 u.value('(dateOfPurchase)[1]'	, 'datetime'	) AS dateOfPurchase
FROM  @ProductXml.nodes('/Products/Item')t(u)

r03
Starting Member

2 Posts

Posted - 2009-09-04 : 01:38:04

Hmmm...thanks for the input guys. I think matty has got the closest answer to what I'm looking for. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources