T-SQL解析XML 空ClientIds节点返回ClientId为0的问题

问题原因 #

现有代码直接将XPath路径定位到ClientIds/int层级，当<ClientIds />为空自闭合节点时不存在任何<int>子节点，自然查询返回空结果，无法触发默认值0的逻辑。另外注意测试代码里的根节点名需要和实际XML完全匹配，否则会直接查询失败。

推荐实现（使用原生XML数据类型方法，兼容SQL Server 2005及以上版本） #

不推荐继续使用老式的sp_xml_preparedocument/OPENXML组合，这套方案存在内存泄漏风险，性能也弱于原生XML方法。直接用XML类型自带的nodes()和value()方法，通过OUTER APPLY关联客户端ID节点，空值时用ISNULL补默认值0即可：

DECLARE @xml XML
-- 有客户端场景测试值
SET @xml = N'<?xml version="1.0" encoding="utf-16"?> 
<ArrayOfServerSettings>
   <ServerSettings>
     <Address>10.0.1.1</Address>
     <Nodes>
        <NodeSettings>
            <PortNumber>5000</PortNumber>
            <ClientIds>
                <int>1</int>
                <int>2</int>
                <int>3</int>
            </ClientIds>
        </NodeSettings>
      </Nodes>
   </ServerSettings>
</ArrayOfServerSettings>'

-- 无客户端场景可替换为以下XML测试
/*
SET @xml = N'<?xml version="1.0" encoding="utf-16"?> 
<ArrayOfServerSettings>
   <ServerSettings>
     <Address>10.0.1.1</Address>
     <Nodes>
        <NodeSettings>
            <PortNumber>5000</PortNumber>
            <ClientIds />
        </NodeSettings>
      </Nodes>
   </ServerSettings>
</ArrayOfServerSettings>'
*/

SELECT
    s.c.value('Address[1]', 'NVARCHAR(256)') AS Address,
    n.c.value('PortNumber[1]', 'INT') AS PortNumber,
    ISNULL(c.c.value('.', 'INT'), 0) AS ClientId
FROM @xml.nodes('/ArrayOfServerSettings/ServerSettings') s(c)
CROSS APPLY s.c.nodes('Nodes/NodeSettings') n(c)
OUTER APPLY n.c.nodes('ClientIds/int') c(c)

核心逻辑说明：

• 先逐层定位到ServerSettings、NodeSettings层级，保证基础的Address、PortNumber字段一定能查出来
• 用OUTER APPLY关联ClientIds下的int节点，效果等同于左连接，没有匹配节点时关联结果为NULL
• 最后用ISNULL把NULL的ClientId转成0，刚好符合无客户端时的返回要求

兼容OPENXML的写法 #

如果因为老版本兼容必须沿用OPENXML，调整XPath定位到NodeSettings层级，再通过外连接关联int节点补默认值即可：

DECLARE @xml XML
SET @xml = N'<?xml version="1.0" encoding="utf-16"?> 
<ArrayOfServerSettings>
   <ServerSettings>
     <Address>10.0.1.1</Address>
     <Nodes>
        <NodeSettings>
            <PortNumber>5000</PortNumber>
            <ClientIds />
        </NodeSettings>
      </Nodes>
   </ServerSettings>
</ArrayOfServerSettings>'

DECLARE @DocID INT 
EXEC sp_xml_preparedocument @DocID OUTPUT, @xml

SELECT 
    D0.Address,
    D0.PortNumber,
    ISNULL(D1.ClientID, 0) AS ClientID
FROM
    OPENXML(@DocID, '/ArrayOfServerSettings/ServerSettings/Nodes/NodeSettings', 1)
    WITH (
        Address NVARCHAR(256) '../../Address[1]',
        PortNumber INT 'PortNumber[1]'
    ) AS D0
OUTER APPLY (
    SELECT ClientID 
    FROM OPENXML(@DocID, '/ArrayOfServerSettings/ServerSettings/Nodes/NodeSettings/ClientIds/int', 1)
    WITH (ClientID INT '.')
    WHERE parentid = D0.id
) AS D1

EXEC sp_xml_removedocument @DocID

注意：使用OPENXML必须执行sp_xml_removedocument释放文档句柄，否则会持续占用SQL Server内存不释放。

两种写法都可以自动适配两种XML结构：传入带客户端ID的XML时返回每个ID对应的行，传入空ClientIds的XML时返回ClientId为0的单行结果。

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