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.
| Author |
Topic |
|
dabneychase
Starting Member
1 Post |
 Posted - 2013-09-26 : 12:08:21
|
| 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 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-26 : 12:56:21
|
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
You can cast the xml string to xml data type (assuming it is well-formed XML ) and then then query against it. nodes method and value methods are most popular and are sufficient for most purposes. There is a list of the available methods here: http://technet.microsoft.com/en-us/library/ms190798.aspx |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-09-28 : 03:37:03
|
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 scenariodeclare @t table(documentid int,itemdata varchar(8000))insert @tvalues (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 DataField4FROM (SELECT documentid,CAST(itemdata as XML) AS itemdata FROM @t) tCROSS 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
Parsing XML