2

My table contains 2 columns, one is the Id of type int and the other is of type Xml.

In the Xml column, I have data of this structure 

<Fields>
    <Field name='aa' value='000'/>
    <Field name='bb' value='111'/>
    <Field name='cc' value='222'/>
</Fields>

Now using XQuery, I want to query the table to get all rows where field tag with name attribute is aa and value attribute is 111, AND field tag with name is attribute bb and value attribute is 222.

In other words I want to supply the name / value pair and get rows where the xml data match.

Thank you !

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Kay Me
  • 21
  • 2

1 Answers1

1

You could use something like this (since you didn't mention your table or column names, I just used something on my own - adapt those as needed!):

SELECT 
    (list of columns)
FROM 
    dbo.YourTableNameHere
WHERE 
    XmlContent.exist('/Fields/Field[@name = "aa"][@value = "000"') = 1

This would return all rows from your table where the XML contains a <Field> node with attributes name = "aa" and value = "000"

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Thanks! But I want to return not just for a name value pair. That is,I want where name is aa and value is 000 AND name is bb and value is 222. Thanks for your promt response – Kay Me Jun 18 '12 at 15:54
  • Thanks! But I want to return not just for a name value pair. That is,I want all rows where there exists a field node with attribute name name is 'aa' and value is '000' AND and another field node with attribute name 'bb' and value is '222'. Thanks for your prompt – Kay Me Jun 18 '12 at 16:10
  • @otubuOlukayode: in that case, just add `AND XmlContent.exist('/Fields/Field[@name = "bb"][@value = "222"') = 1` to the `WHERE` clause of that query I posted – marc_s Jun 18 '12 at 16:21