SQL Query XML Column using XML tag?

Question

I have a table with some various information in the columns. One column named CorrelationInfo is XML and appears as follows:

<Backlog xmlns:xsi = yadda yadda yadda>
    <Correlations>
      <MemCorrelation EID = "1234">
      </MemCorrelation>
    </Correlations>
</Backlog>

I need a query that will count the number of rows where EID = "1234".

Please note: The more efficient the query the better. There are thousands of rows to query against.

I have tried something myself but I am still learning. Here is my work:

Declare @myEID varchar(MAX)
SET @myID = "1234"

SELECT  COUNT(1)
FROM TABLE t
WHERE t.CorrelationInfo.exist('/Backlog/Correlations/MemCorrelation[text()= @myID') =1

I have some experience querying XML but never when the where condition is located in an XML tag like MemCorrelation EID = "1234"...

This is for SQL Server 2008.

Stavros Zotalis · Accepted Answer · 2015-05-05 16:32:05Z

2

You could do something like that:

    DECLARE @myID  int = 1234;
    SELECT  COUNT(*)
    FROM TABLE t
    WHERE t.CorrelationInfo.exist('/Backlog/Correlations/MemCorrelation[@EID=sql:variable("@myID")]') = 1

Exists will return 1 if there is an element MemCorrelation with an attribute EID with value equal to that of the sql variable @myID.

In order to increase performance I would suggest to create at least a Primary XML index on the table and to experiment with a secondary one.

References for further reading:

sql:variable() function

predicates in path expressions

XML Indexes

edited May 5, 2015 at 16:32

answered May 5, 2015 at 16:26

Stavros Zotalis

7264 silver badges24 bronze badges

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

Collectives™ on Stack Overflow

SQL Query XML Column using XML tag?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related