SQL Server数据转JSON：函数、语法与实战案例，深入理解

废话不多说了，直接给大家贴代码了。 -- create function create function [dbo].[fnXmlToJson] (@XmlData xml) returns nvarchar(max) as begin return (select stuff( (select * from (select ',{'+ stuff( (select ',"'+ coalesce(b.c.value('local-name(.)', 'NVARCHAR(MAX)'),'')+'":"'+ b.c.value('text()[]','NVARCHAR(MAX)') +'"' f 在SQL Server中，XML数据类型提供了处理XML文档的能力，而JSON是另一种广泛使用的轻量级数据交换格式。有时，我们可能需要将XML数据转换成JSON格式，以适应现代Web应用程序的需求。本文将介绍如何在SQL Server中创建一个自定义函数，将XML变量转换为JSON文本。 让我们详细解析提供的代码段。这是一个名为`fnXmlToJson`的用户定义函数，它接受一个XML类型的参数`@XmlData`并返回一个NVARCHAR(MAX)类型的值，即转换后的JSON字符串。 ```sql CREATE FUNCTION [dbo].[fnXmlToJson] (@XmlData xml) RETURNS nvarchar(max) AS BEGIN RETURN ( SELECT STUFF( (SELECT ',' + STUFF( (SELECT '"' + COALESCE(b.c.value('local-name(.)', 'NVARCHAR(MAX)'), '') + '":"' + b.c.value('text()[1]', 'NVARCHAR(MAX)') + '"' FROM x.a.nodes('*') b(c) FOR XML PATH(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)') , 1, 1, '') FROM @XmlData.nodes('/root/*') x(a) FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') , 1, 1, '') ) END; ``` 这个函数的工作原理如下： 1. `@XmlData.nodes('/root/*') x(a)`：从XML的根节点下选取所有子元素，并将结果存储为一个游标变量x，其中a是每个子元素的别名。 2. `b.c.value('local-name(.)', 'NVARCHAR(MAX)')`：获取每个XML元素的本地名称（不包括命名空间）。 3. `b.c.value('text()[1]', 'NVARCHAR(MAX)')`：获取每个元素的第一个文本节点的值。 4. `FOR XML PATH(''), TYPE`：将结果集转换为XML，其中每个元素的值用逗号分隔。 5. `STUFF` 函数用于去除第一个逗号，因为JSON对象的键值对之间不需要分隔符。 6. 最终的`SELECT`语句组合所有的键值对，并返回一个格式化的JSON字符串。 为了测试这个函数，创建了一个名为`PivotExample`的表，并插入了一些数据： ```sql CREATE TABLE [dbo].[PivotExample] ( [Country] [nvarchar](MAX) NULL, [Year] [smallint] NOT NULL, [SalesAmount] [money] NULL ) ON [PRIMARY]; INSERT INTO [dbo].[PivotExample] VALUES ('Australia', 2008, 12345.67), ('Germany', 2008, 23456.78), ('United States', 2008, 34567.89), ('France', 2008, 45678.90); DECLARE @xml xml; SET @xml = (SELECT TOP 4 * FROM [dbo].[PivotExample] FOR XML PATH, ROOT); SELECT dbo.fnXmlToJson(@xml); ``` 在这个示例中，`@xml`变量存储了`PivotExample`表的前四行数据的XML表示，然后通过调用`fnXmlToJson`函数将其转换为JSON字符串。 函数的输出将会是类似这样的JSON数组： ```json [ {"Country":"Australia","Year":2008,"SalesAmount":12345.67}, {"Country":"Germany","Year":2008,"SalesAmount":23456.78}, {"Country":"United States","Year":2008,"SalesAmount":34567.89}, {"Country":"France","Year":2008,"SalesAmount":45678.90} ] ``` 总结来说，这个自定义函数`fnXmlToJson`提供了一种方便的方式，将XML数据转换为JSON格式，适用于需要在SQL Server环境中进行数据格式转换的场景。然而，值得注意的是，从SQL Server 2016开始，内置的JSON支持已经增强，可以直接使用内置的`FOR JSON`子句将查询结果转换为JSON，无需编写自定义函数。例如： ```sql SELECT * FROM [dbo].[PivotExample] FOR JSON AUTO; ``` 这种方法更简洁，且在某些情况下性能更好，因为它利用了SQL Server内部的优化。但是，对于旧版本的SQL Server或需要自定义转换逻辑的情况，`fnXmlToJson`函数是一个实用的解决方案。