SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 export xml datatype column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

daniel50096230
Yak Posting Veteran

Malaysia
97 Posts

Posted - 01/05/2013 :  22:21:34  Show Profile  Reply with Quote
Hi everyone,

I have a xml column which stored following data:
<Product><Product_ID>Q001</Product_ID></Product><Product><Product_ID>Q002</Product_ID></Product>

Above data in stored in one row in one column

How can I write the query to select the data become something like this in the output:

Product_ID
Q001
Q002

Edited by - daniel50096230 on 01/06/2013 02:49:38

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/06/2013 :  10:04:16  Show Profile  Reply with Quote

declare @xmltable table
(
x xml
)

insert @xmltable
select '<Product><Product_ID>Q001</Product_ID></Product><Product><Product_ID>Q002</Product_ID></Product>'

SELECT m.n.value('Product_ID[1]','varchar(10)') AS ProductID
FROM
(SELECT CAST('<Products>' + cast(x AS varchar(max)) + '</Products>' AS xml) AS xmlCol FROM @xmltable)t
CROSS APPLY xmlCol.nodes('/Products/Product')m(n)


output
--------------------------------------
ProductID
--------------------------------------
Q001
Q002



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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000