我是使用 SQL Server 2005,想知道在 xml 搜索中是否有办法执行 LIKE 子句。
这是一个示例查询:
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
我希望基本上做的是 WHERE LastName like @lastName + '%'
。
编辑:
有效查询:
SELECT Id, CaseNumber, XmlTest
FROM UploadReport
WHERE
XmlTest.exist('(/CodeFiveReport/Parties/Party/Name/LastName[contains(text()[1], "Sm")])') = 1