CODEWITHSUNDEEP
sqlsql-serverxmldatabasexml-parsing
Dylan Haines
Dylan Haines

Reputation: 75

SQL Query XML Column using XML tag?

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.

Upvotes: 2

Views: 341

Answers (1)

Stavros Zotalis
Stavros Zotalis

Reputation: 726

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

Upvotes: 2

Related Questions