SQL Server查询时如何同步将XML字段与查询结果整体转换为JSON

核心思路 #

SQL Server没有原生的XML直接转JSON的内置语法，需要先将XML字段解析为结构化的关系数据，再通过FOR JSON转换为JSON结构，嵌套到外层查询的结果中即可。

具体实现代码 #

假设你的OrderParameterXML结构为：

<ParameterList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" x="1" y="10"/>

对应的查询语句如下：

SELECT TOP (1)
    [Source],
    -- 解析XML并转成JSON对象
    (
        SELECT
            T.c.value('@x', 'int') AS [ParameterList.x],
            T.c.value('@y', 'int') AS [ParameterList.y]
        FROM OrderParameter.nodes('/*:ParameterList') T(c)
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) AS OrderParameter
FROM [Rops].[dbo].[PreOrder]
FOR JSON PATH;

关键说明 #

• nodes('/*:ParameterList')：将XML中所有ParameterList节点拆分为行集，路径中的*:用于忽略节点的命名空间，避免xsd/xsi命名空间导致匹配不到节点
• value('@x', 'int')：提取节点的x属性值，转换为int类型；如果是子节点值，去掉@符号即可，比如value('x[1]', 'int')
• WITHOUT_ARRAY_WRAPPER：去掉子查询JSON结果的外层方括号，直接返回JSON对象，避免嵌套多余的数组结构
• 列名用.分隔时，FOR JSON PATH会自动将其转换为嵌套的JSON对象结构，和你期望的输出格式匹配

通用场景方案 #

如果XML结构不固定，建议封装自定义标量函数实现通用XML转JSON逻辑，函数内部递归遍历XML节点拼接JSON字符串，查询时直接调用函数处理OrderParameter字段即可。

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