SQL Server中查询XML账户数据并返回指定JSON结果的方法

问题背景 #

现有Accounts表，结构如下：

• OrganizationId int
• AccountDetails varchar(max)（存储XML格式数据）

表中数据示例：

OrganizationId | AccountDetails
--------------|------------------------------
1             | <Account><Id>100</Id><Name>A</Name></Account>
2             | <Account><Id>200</Id><Name>B</Name></Account>
3             | <Account><Id>300</Id><Name>C</Name></Account>
4             | <Account><Id>400</Id><Name>D</Name></Account>

需求：编写SQL查询，筛选出XML中Account/Id为200或400的记录，并返回如下格式的JSON：

result1 : { "account_id": 200, "account_name": "B" }
result2 : { "account_id": 400, "account_name": "D" }

同时有两个疑问：

1. 是否需要将AccountDetails列转换为XML类型后使用nodes特性进行查询筛选？
2. 是否需要编写SQL函数先将XML转换为JSON，再按需构建JSON结果？

解决方案 #

方式1：生成标准JSON结构 #

直接将AccountDetails转换为XML类型，提取字段后用FOR JSON PATH生成目标格式：

SELECT 
    'result' + CAST(ROW_NUMBER() OVER(ORDER BY OrganizationId) AS VARCHAR) AS [key],
    JSON_QUERY('{"account_id":' + CAST(AccountDetails.value('(/Account/Id)[1]', 'int') AS VARCHAR) 
               + ',"account_name":"' + AccountDetails.value('(/Account/Name)[1]', 'varchar(50)') + '"}') AS [value]
FROM Accounts
WHERE AccountDetails.value('(/Account/Id)[1]', 'int') IN (200, 400)
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

执行结果：

{"result1":{"account_id":200,"account_name":"B"},"result2":{"account_id":400,"account_name":"D"}}

方式2：生成指定的键值对字符串格式 #

如果需要严格匹配需求中的行式字符串格式，可直接用字符串拼接：

SELECT 
    'result' + CAST(ROW_NUMBER() OVER(ORDER BY OrganizationId) AS VARCHAR) + ' : { "account_id": ' 
    + CAST(AccountDetails.value('(/Account/Id)[1]', 'int') AS VARCHAR) 
    + ', "account_name": "' + AccountDetails.value('(/Account/Name)[1]', 'varchar(50)') + '" }' AS result_line
FROM Accounts
WHERE AccountDetails.value('(/Account/Id)[1]', 'int') IN (200, 400);

执行结果会返回两行符合要求的字符串。

疑问解答 #

1. 是否需要用nodes特性？
   不需要。每条记录的AccountDetails中仅包含一个<Account>节点，直接用value()方法即可提取Id和Name的值。nodes()方法仅适用于XML中有多个同层级节点需要展开为多行记录的场景。

2. 是否需要编写自定义函数转换XML为JSON？
   不需要。SQL Server自带的FOR JSON语法可直接构建所需JSON结构，或通过字符串拼接生成目标格式，无需额外编写函数。直接用value()提取XML字段后，结合FOR JSON或字符串拼接就能满足需求。

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