为什么我的 T-SQL xmldata.query 找不到我想要的数据?

Question

我有以下有效的查询。它返回我想要的数据。

SELECT *    
FROM pennlink.dbo.logentry WITH (nolock)
WHERE CAST(xmldata.query('/ACORD/SignonRq/SignonPswd/CustId/CustPermId/text()') AS varchar(max)) LIKE '%test123%' 
AND TYPE = 'request' 
AND datesent > '10/27/2014' 
AND datesent < '10/28/2014

下面是上述查询找到的 XML 的格式。

<ACORD xmlns:q1="http://www.ACORD.org/standards/PC_Surety/ACORD1/xml/">
<SignonRq>
<SignonPswd>
<CustId>
<CustPermId>test123</CustPermId>
</CustId>
</SignonPswd>

在 xmldata 列中，还有格式与上述格式不同的 XML。

节点命名为：CustLoginId。加上一些命名空间。

见下文：

<ACORD xmlns="http://www.ACORD.org/standards/PC_Surety/ACORD1/xml/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" RatingSvcVersion="3.0">
<SignonRq>
<SignonPswd>
<CustId>
<CustLoginId>test456</CustLoginId>
</CustId>
<CustPswd>

我修改了查询以使用 CustLoginId，但没有返回任何数据。

见下文。

SELECT *    
FROM pennlink.dbo.logentry WITH (nolock)
WHERE CAST(xmldata.query('/ACORD/SignonRq/SignonPswd/CustId/CustLoginId/text()') AS varchar(max)) LIKE '%test456%'  
AND TYPE = 'request' 
AND datesent > '10/27/2014' 
AND datesent < '10/28/2014'

我做错了什么？

我认为它与命名空间有关。我找到了一些有关如何使用命名空间进行查询的参考，但我无法获得正确的语法。

请帮忙。

谢谢

Answer 1

您应该在查询中使用 SQLXML 方法。像这样的东西。

declare @tbl table(id int, xmldata xml)
insert @tbl(id,xmldata)
values(1, N'<ACORD xmlns="http://www.ACORD.org/standards/PC_Surety/ACORD1/xml/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" RatingSvcVersion="3.0">
<SignonRq>
<SignonPswd>
<CustId>
<CustLoginId>test456</CustLoginId>
</CustId>
<CustPswd/>
</SignonPswd>  
</SignonRq>    
</ACORD>'),
(2, N'<ACORD xmlns="http://www.ACORD.org/standards/PC_Surety/ACORD1/xml/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" RatingSvcVersion="3.0">
<SignonRq>
<SignonPswd>
<CustId>
<CustLoginId>test123</CustLoginId>
</CustId>
<CustPswd/>
</SignonPswd>  
</SignonRq>    
</ACORD>')
;with xmlnamespaces(default 'http://www.ACORD.org/standards/PC_Surety/ACORD1/xml/')
select id, t.v.query('(//SignonRq)[1]') query
,t.v.value('(//CustId/CustLoginId)[1]','varchar(50)') loginid
from @tbl
cross apply xmldata.nodes('ACORD') t(v) --convert xml data to table
where t.v.value('(//CustId/CustLoginId)[1]','varchar(50)')='test456'

Answer 2

declare @x xml = N'<ACORD xmlns="http://www.ACORD.org/standards/PC_Surety/ACORD1/xml/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" RatingSvcVersion="3.0">
<SignonRq>
<SignonPswd>
<CustId>
<CustLoginId>test456</CustLoginId>
</CustId>
<CustPswd/>
</SignonPswd>  
</SignonRq>    
</ACORD>'
select @x, @x.query('//*')--the default xmlns www.accord to each element

select 'exists', 
@x.value('declare default element namespace "http://www.ACORD.org/standards/PC_Surety/ACORD1/xml/"; (ACORD/SignonRq/SignonPswd/CustId/CustLoginId)[1]', 'varchar(100)') as test
where @x.exist('declare default element namespace "http://www.ACORD.org/standards/PC_Surety/ACORD1/xml/";
/ACORD/SignonRq/SignonPswd/CustId/CustLoginId[text()="test456"]') = 1;