废话不多说了,直接给大家贴代码了。
-- 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)') +'"' from x.a.nodes('*') b(c) for xml path(''),type).value('(./text())[]','NVARCHAR(MAX)'),,,'') +'}' from @XmlData.nodes('/root/*') x(a)) JSON(theLine) for xml path(''),type).value('.','NVARCHAR(MAX)' ) ,,,'')); end; go -- test table and data create table [dbo].[PivotExample] ( [Country] [nvarchar]() null ,[Year] [smallint] not null ,[SalesAmount] [money] null ) on [PRIMARY]; insert into [dbo].[PivotExample]values('Australia', , .); insert into [dbo].[PivotExample]values('Germany', , .); insert into [dbo].[PivotExample]values('United States', , .); insert into [dbo].[PivotExample]values('France', , .); declare @xml xml; set @xml=(select top * from [dbo].[PivotExample] for xml path, root); select dbo.fnXmlToJson(@xml); --return string {"Country":"Australia","Year":"","SalesAmount":"."}, {"Country":"Germany","Year":"","SalesAmount":"."}, {"Country":"United States","Year":"","SalesAmount":"."}, {"Country":"France","Year":"2008","SalesAmount":"922179.0400"}
声明:本文内容来源于网络,版权归原作者所有,内容由互联网用户自发贡献自行上传,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任。如果您发现有涉嫌版权的内容,欢迎发送邮件至:notice#nhooo.com(发邮件时,请将#更换为@)进行举报,并提供相关证据,一经查实,本站将立刻删除涉嫌侵权内容。