cq45,品牌价值排行榜,cctv3生活就是舞台
废话不多说了,直接给大家贴代码了。
-- 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"}
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复
数据库SQL---数据库、基本表、视图、索引的定义、修改、删除
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (上)
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (下)
网友评论