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

---

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

---

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