SQL2008使用json.net实现xml与JSON互转借助CLR，首先实现字符串的互转，然后使用存储过程实现JSON2table
publicclass JsonFunction { /// <summary> /// XML转JSON /// </summary> /// <param name="xml"></param> /// <returns></returns> /// <remarks> /// json不建议太长 /// </remarks> [Microsoft.SqlServer.Server.SqlFunction(Name = "Xml2Json")] publicstatic SqlString Xml2Json(SqlXml xml) { System.Xml.XmlDocument doc = new System.Xml.XmlDocument(); doc.LoadXml(xml.Value); string json = JsonConvert.SerializeXmlNode(doc, Formatting.Indented); doc.Clone(); returnnew SqlString(json); } [Microsoft.SqlServer.Server.SqlFunction(Name = "Json2Xml")] publicstatic SqlXml Json2Xml(string json) { System.Xml.XmlDocument doc = JsonConvert.DeserializeXmlNode(json); System.IO.StringWriter sw = new System.IO.StringWriter(); doc.WriteTo(new System.Xml.XmlTextWriter(sw)); returnnew SqlXml(new System.Xml.XmlTextReader(new System.IO.StringReader(sw.ToString()))); } [Microsoft.SqlServer.Server.SqlPRocedure(Name = "Json2Table")] publicstaticvoid Json2Table(string path, string json) { System.Xml.XmlDocument doc = JsonConvert.DeserializeXmlNode(json); System.Xml.XmlNodeList list = null; if (!string.IsNullOrEmpty(path)) list = doc.SelectNodes(path); else list = doc.ChildNodes; if (list == null || list.Count == 0) return; List<SqlMetaData> metas = new List<SqlMetaData>(); string lastName = ""; bool flag = false; for (int i = 0; i < list[0].ChildNodes.Count; i++) { if (lastName == list[0].ChildNodes[i].Name) { flag = true; break; } else lastName = list[0].ChildNodes[i].Name; metas.Add(new SqlMetaData(list[0].ChildNodes[i].Name, SqlDbType.NVarChar, SqlMetaData.Max)); } var rec = new SqlDataRecord(metas.ToArray()); SqlContext.Pipe.SendResultsStart(rec); foreach (System.Xml.XmlNode node in list) { if (flag)//行模式 for (int i = 0; i < node.ChildNodes.Count; i++) { rec.SetString(0, node.ChildNodes[i].InnerXml); SqlContext.Pipe.SendResultsRow(rec); } else { for (int i = 0; i < metas.Count; i++) { rec.SetString(i, node.ChildNodes[i].InnerXml); } SqlContext.Pipe.SendResultsRow(rec); } } SqlContext.Pipe.SendResultsEnd(); } //public static void Json2Table(string json) //{ // //find first array // Q.Json.Linq.JObject jo = JsonConvert.DeserializeObject(json) as Q.Json.Linq.JObject; // Q.Json.Linq.JToken token = jo.First; // while (token != null && token.Type != Q.Json.Linq.JTokenType.Array) // { // token = token.First; // } // if (token == null) // return; // Q.Json.Linq.JArray array = token as Q.Json.Linq.JArray; // if (array.Count == 0) // return; // List<SqlMetaData> metas = new List<SqlMetaData>(); // token = array[0].First; // while (token != null) // { // metas.Add(new SqlMetaData((token as Q.Json.Linq.JProperty).Name, SqlDbType.NVarChar, SqlMetaData.Max)); // token = token.Next; // } // var rec = new SqlDataRecord(metas.ToArray()); // SqlContext.Pipe.SendResultsStart(rec); // foreach (var item in array) // { // for (int i = 0; i < metas.Count; i++) // { // rec.SetString(i, item[metas[i].Name].ToString()); // } // SqlContext.Pipe.SendResultsRow(rec); // } // SqlContext.Pipe.SendResultsEnd(); //} }
测试语句
DROPTABLE test CREATETABLE TEST ( NAME VARCHAR(5), DATA XML ) GO INSERTINTO TESTVALUES ( 'A', '<ROOT><M>AAAA</M></ROOT>' ) , ( 'B', '<ROOT><D>00000</D></ROOT>' ), ( 'C', '123' ) SELECT *FROM test --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) SELECT dbo.Xml2Json(( SELECT * FROM test FOR XML AUTO , ELEMENTS , ROOT )) xml2json SELECT dbo.Json2Xml(dbo.Xml2Json(( SELECT * FROM test FOR XML AUTO , ELEMENTS , ROOT ))) json2xml DECLARE @json NVARCHAR(MAX)SET @json = dbo.Xml2Json(( SELECT * FROM test FOR XML AUTO , ELEMENTS , ROOT )) EXECUTE dbo.json2table 'root/test', @jsonEXECUTE dbo.json2table '', @json
执行效果
参考
http://www.json4sql.com/examples.html
https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/