如何通过SQL Server XML函数获取XML prolog的standalone属性值

• 你收到的XQuery [XmlDocuments.XmlContent.query()]: Top-level attribute nodes are not supported报错，是因为XQuery的query()方法不支持直接返回顶层属性节点。
• 更核心的限制是：SQL Server 原生xml数据类型在存储时会自动丢弃XML声明（prolog）部分，version、encoding、standalone这类声明属性都不会被保留，所以你用任何XML类型自带的XQuery方法都无法读取到这些值。

方案1：预提取字段存储（性能最优，适合大型表） #

新增单独的业务字段存储standalone属性值，在数据写入XmlDocuments表之前，先从原始XML文本中提取该值存入对应字段，后续查询直接读取该字段即可，无需每次解析XML文本，查询效率最高。

方案2：新增NVARCHAR列存原始XML（兼顾查询灵活性和性能） #

新增NVARCHAR(MAX)类型的列存储原始XML文本，查询时直接用字符串函数截取属性值，示例代码如下：

SELECT
    CASE
        WHEN CHARINDEX('standalone="', XmlContent_Original) > 0
        THEN SUBSTRING(
            XmlContent_Original,
            CHARINDEX('standalone="', XmlContent_Original) + LEN('standalone="'),
            CHARINDEX('"', XmlContent_Original, CHARINDEX('standalone="', XmlContent_Original) + LEN('standalone="'))
            - (CHARINDEX('standalone="', XmlContent_Original) + LEN('standalone="'))
        )
        ELSE NULL
    END AS StandaloneValue
FROM XmlDocuments

方案3：临时转换查询（仅适合小批量数据查询） #

如果不想修改表结构，可在查询时临时将xml类型列转换为NVARCHAR(MAX)后再截取，但大型表全表查询时性能损耗较大，建议搭配WHERE条件过滤需要处理的行：

SELECT
    CASE
        WHEN CHARINDEX('standalone="', CAST(XmlContent AS NVARCHAR(MAX))) > 0
        THEN SUBSTRING(
            CAST(XmlContent AS NVARCHAR(MAX)),
            CHARINDEX('standalone="', CAST(XmlContent AS NVARCHAR(MAX))) + LEN('standalone="'),
            CHARINDEX('"', CAST(XmlContent AS NVARCHAR(MAX)), CHARINDEX('standalone="', CAST(XmlContent AS NVARCHAR(MAX))) + LEN('standalone="'))
            - (CHARINDEX('standalone="', CAST(XmlContent AS NVARCHAR(MAX))) + LEN('standalone="'))
        )
        ELSE NULL
    END AS StandaloneValue
FROM XmlDocuments
-- 建议添加过滤条件减少数据处理量
-- WHERE Id = xxx

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