你的位置:首页 > 数据库

[数据库]SQL2008使用json.net实现XML与JSON互转


借助CLR,首先实现字符串的互转

 

 

public class JsonFunction

{

/// <summary>

/// 转JSON

/// </summary>

/// <param name="></param>

/// <returns></returns>

/// <remarks>

/// json不建议太长

/// </remarks>

[Microsoft.SqlServer.Server.SqlFunction(Name = "

public static SqlString (Sql )

{

System.. doc=new System..();

doc.Load(.Value);

string json= JsonConvert.Serialize(doc, Formatting.Indented);

doc.Clone();

return new SqlString(json);

 

}

[Microsoft.SqlServer.Server.SqlFunction(Name = "Json2

public static Sql Json2(string json)

{

System.. doc = JsonConvert.Deserialize(json);

System.IO.StringWriter sw=new System.IO.StringWriter();

doc.WriteTo(new System..(sw));

return new Sql(new System..(new System.IO.StringReader( sw.ToString())));

 

}

}

 

测试语句

 

DROP TABLE test

 

CREATE TABLE TEST ( NAME VARCHAR(5), DATA )

 

GO

 

 

INSERT INTO TEST

VALUES ( 'A', '<ROOT><M>AAAA</M></ROOT>' ),

( 'B', '<ROOT><D>00000</D></ROOT>' )

 

DECLARE @X

SELECT @x = ( SELECT *

FROM test

FOR

AUTO ,

ELEMENTS ,

ROOT

)

SELECT @x

DECLARE @json NVARCHAR(MAX)

SELECT @json = dbo.(@x)

SELECT @json

SELECT dbo.Json2(@json)