SQL Server查询带命名空间的XML时select语句无法正常取值问题

你遇到的问题是XML默认命名空间导致的XPath匹配失败。你的XML根节点<AcknowledgeShipmentInbound>定义了默认命名空间http://schema.infor.com/InforOAGIS/2，该节点下所有未显式指定命名空间前缀的子节点都默认归属于这个命名空间，你写的不带命名空间匹配规则的XPath无法定位到对应节点，所以查不到结果。

以下是两种可直接落地的解决方案： #

方案1：显式声明命名空间（推荐，规范严谨） #

使用SQL Server的WITH XMLNAMESPACES语法声明默认命名空间，之后写的XPath就可以直接匹配节点名称，不需要加额外前缀：

Declare @xmlData xml
set @xmlData = '<?xml version="1.0"?>
<AcknowledgeShipmentInbound releaseID="9.2" xmlns="http://schema.infor.com/InforOAGIS/2" 
xmlns:xs="http://www.w3.org/2001/XMLSchema"> 
<ApplicationArea>
    <Sender>
        <LogicalID>lid://ln01/2100</LogicalID>
        <ComponentID>erp</ComponentID>
        <ConfirmationCode>OnError</ConfirmationCode>
    </Sender>
    <CreationDateTime>2021-09-02T09:22:03Z</CreationDateTime>
    <BODID>1630574520554:123461:0</BODID>
 </ApplicationArea>
 <DataArea>
    <Acknowledge>
        <TenantID>KP_TRN</TenantID>
        <AccountingEntityID>2100</AccountingEntityID>
        <LocationID>S_2100</LocationID>
        <OriginalApplicationArea xmlns="">
            <Sender>
                <LogicalID>oracle_erp_ihub_v2</LogicalID>
                <ComponentID>External</ComponentID>
                <ConfirmationCode>OnError</ConfirmationCode>
            </Sender>
            <CreationDateTime>2021-09-02T09:22:00.554Z</CreationDateTime>
            <BODID>ihub_v2:1630574520554:123461:0</BODID>
        </OriginalApplicationArea>
        <ResponseCriteria>
            <ResponseExpression actionCode="Rejected"/>
            <ChangeStatus>
                <ReasonCode>tlbcts0074</ReasonCode>
                <Reason languageID="en-US">Request validation; SHIPMENTS WITH REQUEST ID 
   2021090205 the value is too long.</Reason>
            </ChangeStatus>
        </ResponseCriteria>
    </Acknowledge>
    <ShipmentInbound>
        <ShipmentInboundHeader>
            <DocumentID>
                <ID>Shipments with request id 28880902052200</ID>
            </DocumentID>
        </ShipmentInboundHeader>
        <ShipmentInboundLine>NONE</ShipmentInboundLine>
    </ShipmentInbound>
</DataArea>
</AcknowledgeShipmentInbound>'

DECLARE @DocumentId NVARCHAR(128);

-- 显式声明默认命名空间
WITH XMLNAMESPACES (DEFAULT 'http://schema.infor.com/InforOAGIS/2')
SELECT T.c.value('(DocumentID/ID/text())[1]', 'VARCHAR(128)') AS DocumentID
FROM @xmlData.nodes('/AcknowledgeShipmentInbound/DataArea/ShipmentInbound/ShipmentInboundHeader') T(c)

运行后即可直接拿到你需要的ID值：Shipments with request id 28880902052200 #

方案2：使用命名空间通配符（快速适配，不推荐生产环境长期使用） #

就是你写的第一个查询的写法，在每个节点名称前加*:匹配任意命名空间，不需要提前声明命名空间，适合临时快速查询场景：

Select T.c.value('(DocumentID/ID/text())[1]', 'VARCHAR(128)') AS DocumentID
FROM @xmlData.nodes('*:AcknowledgeShipmentInbound/*:DataArea/*:ShipmentInbound/*:ShipmentInboundHeader') T(c)

注意事项 #

如果需要查询<OriginalApplicationArea>节点下的内容，该节点用xmlns=""重置了默认命名空间为无命名空间，查询该节点下的内容时需要单独处理匹配规则。

内容的提问来源于stack exchange，提问作者KamP