实现SQL Server 原生数据从XML生成JSON数据的实例代码

SQL Server原生数据从XML生成JSON数据的实例代码

1. 介绍

在进行数据库开发和数据存储时，经常需要将数据格式从一种形式转换为另一种形式 。如将XML数据转换为JSON格式，方便数据在不同系统间传输和处理。本文介绍在SQL Server中，如何使用T-SQL将XML数据转换为JSON格式。

2. XML转JSON原理

XML数据和JSON数据都是结构化的数据，能够被解析器解析。XML解析器和JSON解析器实现的都是一种解释器模式。XML元素可以表示为JSON的属性（property）或嵌套的JSON对象（object）或JSON数组（array）。

比如，一个XML元素：

<bookstore>
    <book>
        <title>SQL Server入门指南</title>
        <author>张三</author>
        <price>29.99</price>
    </book>
    <book>
        <title>SQL Server实战实例</title>
        <author>李四</author>
        <price>39.99</price>
    </book>
</bookstore>

可以表示为JSON对象：

{
    "bookstore": {
        "book": [
            {
                "title":"SQL Server入门指南",
                "author":"张三",
                "price":29.99
            },
            {
                "title":"SQL Server实战实例",
                "author":"李四",
                "price":39.99
            }
        ]
    }
}

3. 使用T-SQL将XML转换为JSON

SQL Server 2016及以后版本支持使用FOR JSON语句将查询结果集转换为JSON数据。但是，如果想将XML数据直接转换为JSON数据，可以使用OPENJSON函数和JSON_QUERY函数。

3.1 使用OPENJSON函数将XML转换为JSON数组

使用OPENJSON函数可以将JSON文本或属性列表（key-value）转换为表格形式。通过将XML数据转换为JSON文本，然后将JSON文本作为OPENJSON函数的输入参数，可以将XML数据转换为JSON数组。代码如下：

DECLARE @xmlData XML = '
    <bookstore>
        <book>
            <title>SQL Server入门指南</title>
            <author>张三</author>
            <price>29.99</price>
        </book>
        <book>
            <title>SQL Server实战实例</title>
            <author>李四</author>
            <price>39.99</price>
        </book>
    </bookstore>'
    
DECLARE @jsonArray NVARCHAR(MAX) = (SELECT @xmlData FOR JSON PATH)
SELECT value FROM OPENJSON(@jsonArray) 

解释一下，首先声明一个XML变量@xmlData，然后使用FOR JSON PATH将其转换为JSON文本。此时，@jsonArray的值为：

[
    {
        "@xmlData": "\r\n        
            {
                "book": [
                    {
                        \"title\": \"SQL Server入门指南\",
                        \"author\": \"张三\",
                        \"price\": \"29.99\"
                    },
                    {
                        \"title\": \"SQL Server处理实例\",
                        \"author\": \"李四\",
                        \"price\": \"39.99\"
                    }
                ]
            }\r\n"
    }
]

接着，将@jsonArray作为OPENJSON函数的输入参数，返回一个JSON数组。该数组包含了一个对象，该对象的key为"@xmlData"，value为JSON文本。我们需要提取出JSON文本，然后用JSON_QUERY函数将其转换为JSON数组。

SELECT value 
FROM OPENJSON(JSON_QUERY(@jsonArray, '$[0]."@xmlData"'))

这里，JSON_QUERY函数的第二个参数是JSON路径表达式，表示提取@jsonArray数组的第一个元素的"@xmlData"属性值。该属性值是一个JSON文本，需要用OPENJSON函数解析。

经过上述处理，就得到了一个包含两个元素的JSON数组，每个元素表示一个书籍信息。

3.2 使用OPENJSON函数将XML转换为JSON对象

将XML数据转换为JSON对象，与将其转换为JSON数组类似。只需针对XML数据的不同结构，编写不同的T-SQL脚本即可。例如，将前面的XML数据作为JSON对象表示：

{
    "bookstore": {
        "book": [
            {
                "title":"SQL Server入门指南",
                "author":"张三",
                "price":29.99
            },
            {
                "title":"SQL Server实战实例",
                "author":"李四",
                "price":39.99
            }
        ]
    }
}

它与JSON数组的区别在于，对象的value是一个JSON数组，对象包含了一个property，即"bookstore"属性。因此，我们需要先将XML数据转换为嵌套式的JSON文本，再将该文本用JSON_QUERY函数提取出"bookstore"属性的值。

DECLARE @xmlData XML = '
    <bookstore>
        <book>
            <title>SQL Server入门指南</title>
            <author>张三</author>
            <price>29.99</price>
        </book>
        <book>
            <title>SQL Server实战实例</title>
            <author>李四</author>
            <price>39.99</price>
        </book>
    </bookstore>'
    
DECLARE @jsonObj NVARCHAR(MAX) = (SELECT @xmlData FOR JSON AUTO)
SELECT JSON_QUERY(@jsonObj, '$."bookstore"') AS bookstore

运行上述脚本，就能得到以下结果：

{
    "book": [
        {
            "title": "SQL Server入门指南",
            "author": "张三",
            "price": 29.99
        },
        {
            "title": "SQL Server实战实例",
            "author": "李四",
            "price": 39.99
        }
    ]
}

4. 总结

本文介绍了如何使用SQL Server原生函数将XML数据转换为JSON格式。转换的原理是，将XML元素转换为JSON对象、数组、属性等。具体实现上，可以使用OPENJSON函数、JSON_QUERY函数等。使用T-SQL将XML数据转换为JSON格式，可以简化格式转换的过程，同时在SQL Server环境下也非常方便。