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 |
|
BitShift
Yak Posting Veteran
98 Posts |
 Posted - 2007-05-10 : 10:24:20
|
I have some xml in an xml column. Each xml value will contain elements that would be equivilant to a row of data. How can I query all the records in this table, where each row will contain an xml column. Within the xml column will be elments that I want to shred into a row of a resultset.My xml will look something like this (for each xml field):<xml><user user_type="n123"><fname>fred</fname><mi>d</mi><lname>flinstone</lname><addr1>123 apple st</addr1><addr2>apt 1234</addr2><city>city123</city><state>al</state><zip>123456</zip><phone>1112228888</phone><email>user123@aol.com</email></user></xml> |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2007-05-11 : 01:38:13
|
| This may be one of the few times a cursor will be the best alternative.The only way I can think of making this work is tocreate a variable and append each rows data to that variable.Once done you can treat the variable as a xml file and easily query the data in sql and retrieve the data into a table/recordset.Other then the query,you may be able to use a CTE with a recursive query to do the same thing (Keep appending the record to a variable) |
 |
|
|
davenaylor2000
Starting Member
3 Posts |
Posted - 2007-05-11 : 04:59:59
|
| you could retrieve the rows aa a string and then use for open xml explicit to insert each record you require into a temporary table.then do the select on the temporary table? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-05-11 : 05:07:13
|
| use the nodes() method of the xml datatype.if it doesn't work post your attempts here._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
|
|
|
xquery or openxml or both ?