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

<SURNAME>Smith</SURNAME>
</operator>
<operator>
<ID>52</ID>
<NAME>Michael</NAME>
<SURNAME>Williams</SURNAME>
</operator>
<operator>
<ID>50</ID>
<NAME>John "The Fox"</NAME>
<SURNAME>Brown</SURNAME>
</operator>
</operators>
</stat>
</stats>
</station>
<station>
<ID>66</ID>
<CITY>Caribou</CITY>
<STATE>ME</STATE>
<LAT_N>4.7000000e+001</LAT_N>
<LONG_W>6.8000000e+001</LONG_W>
<stats>
<stat>
<STATION_ID>66</STATION_ID>
<MONTH>1</MONTH>
<TEMP_F>6.6999998e+000</TEMP_F>
<RAIN_I>2.0999999e+000</RAIN_I>
<operators>
<operator>
<ID>51</ID>
<NAME>Paul</NAME>
<SURNAME>Smith</SURNAME>
</operator>
</operators>
</stat>
<stat>
<STATION_ID>66</STATION_ID>
<MONTH>7</MONTH>
<TEMP_F>6.5800003e+001</TEMP_F>
<RAIN_I>4.5200000e+000</RAIN_I>
<operators>
<operator>
<ID>51</ID>
<NAME>Paul</NAME>
<SURNAME>Smith</SURNAME>
</operator>
</operators>
</stat>
</stats>
</station>

3.如何生成JSON数据

1）创建辅助函数

CREATE FUNCTION [dbo].[qfn_XmlToJson](@XmlData xml)
RETURNS nvarchar(max)
AS
BEGIN
 declare @m nvarchar(max)
 SELECT @m='['+Stuff
 (
   (SELECT theline from
  (SELECT ','+' {'+Stuff
    (
       (SELECT ',"'+coalesce(b.c.value('local-name(.)', 'NVARCHAR(255)'),'')+'":'+
           case when b.c.value('count(*)','int')=0
           then dbo.[qfn_JsonEscape](b.c.value('text()[1]','NVARCHAR(MAX)'))
           else dbo.qfn_XmlToJson(b.c.query('*'))
           end
         from x.a.nodes('*') b(c)
         for xml path(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)')
        ,1,1,'')+'}'
     from @XmlData.nodes('/*') x(a)
    ) JSON(theLine)
    for xml path(''),TYPE).value('.','NVARCHAR(MAX)')
   ,1,1,'')+']'
  return @m
END

CREATE FUNCTION [dbo].[qfn_JsonEscape](@value nvarchar(max) )
returns nvarchar(max)
as begin 
 if (@value is null) return 'null'
 if (TRY_PARSE( @value as float) is not null) return @value 
 set @value=replace(@value,'','')
 set @value=replace(@value,'"','"') 								 
 3/4   首页 上一页 1 2 3 4 下一页 尾页