如何在SQL Server中过滤xml数据类型列

Question

我有一个有两个xml列的表，表内的数据很大。我想对xml类型的列进行过滤，以检查它是否包含ID。

我的示例xml列IncomingXML和值看起来像这样:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sws="abc">
<soapenv:Header>
<To xmlns="http://schemas.microsoft.com/ws/2005/05/addressing/none" soapenv:mustUnderstand="1">abc</To>
<Action xmlns="http://schemas.microsoft.com/ws/2005/05/addressing/none" soapenv:mustUnderstand="1">student</Action>
</soapenv:Header>
<soapenv:Body>
<sws:ProvisionStudent>
<sws:provisionStudentInput>
<sws:Operation>U</sws:Operation>
<sws:ADAccount>SU123456789</sws:ADAccount>
<sws:ADPassword>abcde</sws:ADPassword>
<sws:Prefix />
<sws:FirstName>ancd</sws:FirstName>
<sws:LastName>xyz</sws:LastName>
<sws:MiddleName />
<sws:Suffix />
<sws:Email>[e-mail]</sws:Email>
<sws:EmplId>123456789</sws:EmplId>
<sws:CampusCode />
<sws:CompletionYear>0</sws:CompletionYear>
<sws:CurrentCumulativeGpa>0</sws:CurrentCumulativeGpa>
<sws:GraduationGpa />
<sws:GraduationProgramCode />
<sws:ProgramCode />
<sws:UserType />
</sws:provisionStudentInput>
</sws:ProvisionStudent>
</soapenv:Body>
</soapenv:Envelope>

请帮我解决像

这样的问题

select * 
from table 
where IncomingXML like '%SU123456789%'

我尝试了以下方法，但没有成功。

select * 
from table 
where cast(IncomingXML as nvarchar(max)) like '%SU123456789%'

Answer 1

可以使用XQuery

DECLARE @toSearch nvarchar(100) = 'SU123456789';
WITH XMLNAMESPACES(
'http://schemas.xmlsoap.org/soap/envelope/' AS soapenv,
DEFAULT 'abc'
)
SELECT *
FROM YourTable t
WHERE t.IncomingXML.exist('/soapenv:Envelope/soapenv:Body/ProvisionStudent/provisionStudentInput/ADAccount[text() = sql:variable("@toSearch")]') = 1;

以效率为代价在任意节点上搜索

DECLARE @toSearch nvarchar(100) = 'SU123456789';
SELECT *
FROM YourTable t
WHERE t.IncomingXML.exist('//*[text() = sql:variable("@toSearch")]') = 1;

,db&lt的在小提琴

显然也可以在XQuery中嵌入文字而不是变量。