Using XQuery in SQL Server

Question

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 !

To do that you need to use the xml Data Type Methods. Give it a try and ask a new question when you run into trouble. — Mikael Eriksson
– Mikael Eriksson, Commented Jun 17, 2012 at 9:14

marc_s · Accepted Answer · 2012-06-17 09:39:42Z

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"

answered Jun 17, 2012 at 9:39

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kay Me Over a year ago

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 Over a year ago

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

marc_s Over a year ago

@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

Collectives™ on Stack Overflow

Using XQuery in SQL Server

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related