尝试在SQL Server中使用查询从表列访问XML节点值时，它总是返回一个空字符串

Question

有一个名为UserRequest的表，其中一列为XML类型(列名为RequestXML(。RequestXML中的值如下所示：

<MyRequest xmlns:i="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://schemas.datacontract.org/2004/07/">
<transfer>
<Field>
<fieldId>12323</fieldId>
<FieldDetails>
<RequestInfoField>
<requestDecs>Test</requestDecs>
<reqDate>01/01/2021</reqDate>
</RequestInfoField>
<identifierKey>45638</identifierKey>
</FieldDetails>
</Field>
</transfer>
</MyRequest>

使用以下SQL查询检索identifierKey的XML节点值，但该查询始终返回空字符串：

select 
T.RequestXML.query('MyRequest/transfer/Field/FieldDetails/identifierKey') As identifierKey, * 
from UserRequest  T

Answer 1

您还没有在SQL:中声明您的(默认(命名空间

WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org/2004/07/')
SELECT UR.RequestXML.query('MyRequest/transfer/Field/FieldDetails/identifierKey') As identifierKey,
*
FROM dbo.UserRequest UR;

但是，如果您明确想要identifierKey的值，请使用value，而不是query。

WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org/2004/07/')
SELECT UR.RequestXML.value('(MyRequest/transfer/Field/FieldDetails/identifierKey)[1]','int') AS identifierKey
*
FROM dbo.UserRequest UR;

db<gt；小提琴

Answer 2

像这样：

declare @doc xml = '<MyRequest xmlns:i="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://schemas.datacontract.org/2004/07/">
<transfer>
<Field>
<fieldId>12323</fieldId>
<FieldDetails>
<RequestInfoField>
<requestDecs>Test</requestDecs>
<reqDate>01/01/2021</reqDate>
</RequestInfoField>
<identifierKey>45638</identifierKey>
</FieldDetails>
</Field>
</transfer>
</MyRequest>';
WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org/2004/07/')
SELECT T.RequestXML.value('(/MyRequest/transfer/Field/FieldDetails/identifierKey)[1]','int') As identifierKey,  *
FROM (values(@doc)) T(RequestXML);