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 |
|
beam
Starting Member
1 Post |
 Posted - 2009-02-25 : 06:54:39
|
| I have a xml column in a table. Data could look like this:<xml xmlns="http://something"> <data> <grid id="ProductsOnShelf"> <row id="1"> <item id="type">Computer</item> <item id="size">Big</item> <item id="price">100</item> </row> <row id="2"> <item id="type">Bike</item> <item id="size">Huge</item> <item id="price">50</item> </row> </grid> </data></xml>That is the content in one row in the table. All rows have xml data on that form, but the actual data for the items is varying.Now I want to create a sql query, where I select all rows in the table which contains item-nodes with id="size" and the value "Huge", independent of which row in the grid data they are located in. I can do this with the exist-function, but then it becomes case-sensitive, and I do not want that.I have tried this one (among others), but it does not return any result:SELECT *FROM ProductsWHERE ProductsOnShelf.query('declare namespace st="http://something"; /st:data/st:grid[@id="ProductsOnShelf"]/st:row/st:item[@id="size"]').value('.','NVARCHAR(255)') = 'Huge'Suggestions? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
Where-clause on xml column