SQL Server动态XML层级数据导出：获取最低值遇NULL问题

问题根源 #

你写的动态表达式里，*[local-name() = sql:column("textMng.FieldId")] 是把 textMng.FieldId 存储的完整字符串s4356/L4356/F4357当成单个节点的名称去匹配，但XML里根本没有叫这个名字的节点——你实际需要的是把这个字符串拆成s4356、L4356、F4357三层节点依次遍历，这就是为什么硬编码路径能生效，动态写法不行。

可行解决方案 #

1. 动态SQL拼接XPath（最简单直接） #

直接把路径字符串拆成XPath层级，拼接成和硬编码一致的语句执行：

DECLARE @Root NVARCHAR(100) = 'rc0036';
DECLARE @FieldId NVARCHAR(200) = 's4356/L4356/F4357';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'
SELECT patdata.value(''(*[local-name() = "' + @Root + '"]/' + REPLACE(@FieldId, '/', '/') + '/text())[1]'', ''nvarchar(max)'')
FROM YourTable; -- 替换成你的实际表名
';

EXEC sp_executesql @SQL;

2. XQuery tokenize解析路径（无动态SQL） #

如果不想用动态SQL，SQL Server 2016及以上版本可以用tokenize函数拆分路径，再递归定位节点：

SELECT 
    patdata.query('
        let $root := *[local-name() = sql:column("DataMng.Root")]
        let $pathSegments := tokenize(sql:column("textMng.FieldId"), "/")
        return
            (
                for $i in 1 to count($pathSegments)
                return
                    if ($i=1) then $root/*[local-name() = $pathSegments[$i]]
                    else $root/*[local-name() = $pathSegments[$i-1]]/*[local-name() = $pathSegments[$i]]
            )[last()]/text()[1]
    ').value('.', 'nvarchar(max)') AS TargetValue
FROM YourTable; -- 替换成你的实际表名

3. 自定义函数封装逻辑（复用性高） #

如果需要多次使用这个逻辑，写个自定义函数更方便：

CREATE FUNCTION dbo.GetXmlNestedValue(
    @XmlContent XML,
    @RootNodeName NVARCHAR(100),
    @NestedPath NVARCHAR(200)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @PathParts TABLE (NodeName NVARCHAR(100), OrderNum INT IDENTITY(1,1));
    DECLARE @CurrentNode XML;
    DECLARE @Result NVARCHAR(MAX);

    -- 拆分路径为节点列表
    INSERT INTO @PathParts(NodeName)
    SELECT value FROM STRING_SPLIT(@NestedPath, '/');

    -- 定位根节点
    SET @CurrentNode = @XmlContent.query('*[local-name() = sql:variable("@RootNodeName")]');

    -- 逐层遍历节点
    DECLARE @CurrentOrder INT = 1;
    DECLARE @CurrentNodeName NVARCHAR(100);
    WHILE EXISTS(SELECT 1 FROM @PathParts WHERE OrderNum = @CurrentOrder)
    BEGIN
        SELECT @CurrentNodeName = NodeName FROM @PathParts WHERE OrderNum = @CurrentOrder;
        SET @CurrentNode = @CurrentNode.query('*[local-name() = sql:variable("@CurrentNodeName")]');
        SET @CurrentOrder += 1;
    END;

    -- 获取第一个文本值
    SET @Result = @CurrentNode.value('(./text())[1]', 'nvarchar(max)');
    RETURN @Result;
END;
GO

-- 使用示例
SELECT dbo.GetXmlNestedValue(patdata, DataMng.Root, textMng.FieldId) AS TargetValue
FROM YourTable;

效果验证 #

以上三种方法都能正确定位到XML中的F4357节点，返回第一个文本值5。

内容的提问来源于stack exchange，提问作者Ravit Shagan Damti