I have one table where one column contains XML format data inside and i have more than one row in my table. i.e.
Here the XML id could be upto N as shown above.
Now i want to query on my table and in Where clause i want to put XPath Query on this column so that i could put a condition on specific ID and based on that iD i could get the whole data row from table, i am thinking query should be as below
Select * from XYZ where col1.query('//test/id') >= 5
Select * from XYZ where col1.value('(/test/id)', 'int')>= 5
Here the first query where i am using col1.query('//test/id') >= 5, There is syntax error, and in second one, first of all.. i have to declare the XML type local variable then set that vaiable like SET @localvariable = col1 from XYZ
and then @localvariable.value('(/test/id)', 'int'), eventhough i cannot put the range (>=,<=) here, as well as we cannot get the all rows in @localvariable when we set the value to the local variable. but i want my query should be as i show
How can i do this ? Any suggestion.....
View Complete Post