借助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)
原标题:SQL2008使用json.net实现XML与JSON互转
关键词:.NET