With the CLR, you first implement the cross-transfer of strings
?
?
Public class jsonfunction
{
/// <summary>
/// XML Turn JSON
/// </Summary>
/// <paramname="xml"></param>
/// <returns></returns>
/// <remarks>
// json not recommended too long
/// </remarks>
[Microsoft.SqlServer.Server.SqlFunction (Name = "Xml2json")]
Public Static SqlString Xml2json(SqlXmlxml)
{
System. Xml. XmlDocumentDoc=newSystem. Xml. XmlDocument();
Doc. LOADXML(xml. Value);
string json= jsonconvert. Serializexmlnode(doc, formatting. Indented);
Doc. Clone();
return New SqlString(json);
?
}
[Microsoft.SqlServer.Server.SqlFunction (Name = "Json2xml")]
Public Static SqlXml json2xml(stringjson)
{
System. Xml. XmlDocumentDoc =jsonconvert. Deserializexmlnode(json);
system . io. StringWriter sw=new Span style= "Color:blue" >system. Io. Stringwriter
docwriteto (new system. Xml. Xmltextwritersw
return New SqlXml(newSystem. Xml. XmlTextReader(newSystem. IO. StringReader( SW. ToString() ));
?
}
}
?
Test statement
?
DROP TABLE Test
?
CREATE TABLE TEST(NAME VARCHAR(5),DATA XML)
?
GO
?
?
INSERT into TEST
VALUES (' A ', ' <ROOT><M>AAAA</M></ROOT>'),
(' B ', ' <ROOT><D>00000</D></ROOT>')
?
DECLARE @X XML
SELECT @x =( SELECT *
from Test
For
XML AUTO ,
ELEMENTS ,
ROOT
)
SELECT @x
DECLARE @json NVARCHAR(MAX)
SELECT @json = dbo. Xml2json(@x)
SELECT @json
SELECT dbo. Json2xml(@json)
?
?
SQL2008 using Json.NET to implement XML and JSON mutual transfer