CODEWITHSUNDEEP
sqlxmlt-sqlxquery
pawel1708hp
pawel1708hp

Reputation: 759

SQL WHERE clause with multiple XML attributes

I have table in database with XML column. Now I need to select some rows by two attributes from XML. So far I've come up with this:

SELECT o.Id 
FROM Objects o 
WHERE o.SerializedObject.value('(/object/param[@id="111"]/@value)[1]', 'varchar(8)') = '-1'
  AND o.SerializedObject.value('(/object/param[@id="222"]/@value)[1]', 'varchar(8)') = '8'

EDIT:

XML is like:

<object>
   <param id="1" value="111"/>
   <param id="2" value="222"/>
   ...
   <param id="200" value="4545"/>
<object>

Each object has ~2k params.

I'm wondering if there is a better way to do that with single XML query.

Upvotes: 0

Views: 1325

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

This depends on your XML (you did not show an example, but I assume this is kind of EAV).

You can try using XML's method .exist():

DECLARE @mockup TABLE(ID INT IDENTITY,Comment VARCHAR(100),SerializedObject XML);
INSERT INTO @mockup VALUES
 ('just one of them','<object><param id="111" value="-1"/></object>')
,('both, but wrong values','<object><param id="111" value="-1"/><param id="222" value="-1"/></object>')
,('both, should fit','<object><param id="111" value="-1"/><param id="222" value="8"/></object>')

SELECT o.Id,o.Comment,o.SerializedObject
FROM @mockup o 
WHERE o.SerializedObject.exist('/object[param[@id="111" and @value="-1"] and param[@id="222" and @value="8"]]')=1;

.exist() is the fastest here, because it does not return any value. It will just return 1 on the first occurance found. This is especially fast, when there are many occurances of a <param id="111" value="???"> Otherwise you'd have to shred the whole lot and place the filter on the whole resultset.

And - of course! - the necessary hint: As told in a comment by Jeroen Mostert dealing with bigger XMLs might turn out as a bottle neck. If you need this more often, you might think about a relational design instead of big XMLs...

Upvotes: 1

Related Questions