CODEWITHSUNDEEP
sqlxml
mint
mint

Reputation: 3433

Is there a LIKE Clause for querying XML in SQL

I'm using SQL Server 2005 and am wondering if there is any way to do a LIKE clause in my xml searches.

Here's a sample query:

            SELECT  Id, CaseNumber, XmlTest
            FROM UploadReport 
            WHERE 
            CASE 
                  WHEN @lastName != '' AND XmlTest.exist('(/CodeFiveReport/Parties/Party/Name/LastName[text()=sql:variable("@lastName")])') = 1 THEN 1
                  WHEN @firstName != '' AND XmlTest.exist('(/CodeFiveReport/Parties/Party/Name/FirstName[text()=sql:variable("@firstName")])') = 1 THEN 1
                  WHEN @middleName != '' AND XmlTest.exist('(/CodeFiveReport/Parties/Party/Name/MiddleName[text()=sql:variable("@middleName")])') = 1 THEN 1
                  WHEN @ssn != '' AND XmlTest.exist('(/CodeFiveReport/Parties/Party/SocialSecurityNumber[text()=sql:variable("@ssn")])') = 1 THEN 1             
                  ELSE 0
            END = 1

I would like to do WHERE LastName like @lastName + '%' basically.

EDIT:

Working query:

    SELECT  Id, CaseNumber, XmlTest
            FROM UploadReport 
            WHERE 
                 XmlTest.exist('(/CodeFiveReport/Parties/Party/Name/LastName[contains(text()[1], "Sm")])') = 1

Upvotes: 3

Views: 742

Answers (1)

Andomar
Andomar

Reputation: 238086

Have you tried contains()?

[contains(sql:variable("@lastName"))]

Upvotes: 3

Related Questions