quote: Originally posted by dabneychase
I have a SQL table which contains two fields which I am interested in. One is documentid. It contains varchar data. The other is itemdata which actually contains XML code but is not an xml field. Within the xml code are values for various items and they have consistent tags. I would like parse the XML data within the field so that every time it finds the tag <DataField1>, the value is pushed to a column DataField1 in my query. The end result would be:
documentid DataField1
See illustration here
--Sample table to illustrate the scenario
declare @t table
(
documentid int,
itemdata varchar(8000)
)
insert @t
values (1,'<RootNode><NodeElement><DataField1>Value1</DataField1><DataField2>12345</DataField2><DataField3>2013-02-12T13:24:30</DataField3><DataField4>$123.45</DataField4></NodeElement></RootNode>')
SELECT documentid,
p.q.value('./DataField1[1]','varchar(100)') AS DataField1,
p.q.value('./DataField2[1]','int') AS DataField2,
p.q.value('./DataField3[1]','datetime') AS DataField3,
p.q.value('./DataField4[1]','money') AS DataField4
FROM (SELECT documentid,CAST(itemdata as XML) AS itemdata FROM @t) t
CROSS APPLY itemdata.nodes('/RootNode/NodeElement')p(q)
output
----------------------------------------------------------------------------------
documentid DataField1 DataField2 DataField3 DataField4
----------------------------------------------------------------------------------
1 Value1 12345 2013-02-12 13:24:30.000 123.45
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs
|