export xml datatype column - 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

SQL Server 2008 Forums

Transact-SQL (2008)

export xml datatype column

Author

Topic

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2013-01-05 : 22:21:34

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-06 : 10:04:16

[code]
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

[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources