SQL2008使用json.net实现XML与JSON互转

借助CLR,首先实现字符串的互转,然后使用存储过程实现JSON2table

 
 

  1. public
    class JsonFunction
  2.    {
  3.        /// <summary>
  4.        /// XML转JSON
  5.        /// </summary>
  6.        /// <param name="xml"></param>
  7.        /// <returns></returns>
  8.        /// <remarks>
  9.        /// json不建议太长
  10.        /// </remarks>
  11.        [Microsoft.SqlServer.Server.SqlFunction(Name = "Xml2Json")]
  12.        public
    static SqlString Xml2Json(SqlXml xml)
  13.        {
  14.            System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
  15.            doc.LoadXml(xml.Value);
  16.            string json = JsonConvert.SerializeXmlNode(doc, Formatting.Indented);
  17.            doc.Clone();
  18.            return
    new SqlString(json);
  19.  
  20.        }
  21.        [Microsoft.SqlServer.Server.SqlFunction(Name = "Json2Xml")]
  22.        public
    static SqlXml Json2Xml(string json)
  23.        {
  24.            System.Xml.XmlDocument doc = JsonConvert.DeserializeXmlNode(json);
  25.            System.IO.StringWriter sw = new System.IO.StringWriter();
  26.            doc.WriteTo(new System.Xml.XmlTextWriter(sw));
  27.            return
    new SqlXml(new System.Xml.XmlTextReader(new System.IO.StringReader(sw.ToString())));
  28.  
  29.        }
  30.        [Microsoft.SqlServer.Server.SqlProcedure(Name = "Json2Table")]
  31.        public
    static
    void Json2Table(string path, string json)
  32.        {
  33.            System.Xml.XmlDocument doc = JsonConvert.DeserializeXmlNode(json);
  34.            System.Xml.XmlNodeList list = null;
  35.            if (!string.IsNullOrEmpty(path))
  36.                list = doc.SelectNodes(path);
  37.            else
  38.                list = doc.ChildNodes;
  39.            if (list == null || list.Count == 0)
  40.                return;
  41.            List<SqlMetaData> metas = new List<SqlMetaData>();
  42.            string lastName = "";
  43.            bool flag = false;
  44.            for (int i = 0; i < list[0].ChildNodes.Count; i++)
  45.            {
  46.                if (lastName == list[0].ChildNodes[i].Name)
  47.                {
  48.                    flag = true;
  49.                    break;
  50.                }
  51.                else
  52.                    lastName = list[0].ChildNodes[i].Name;
  53.                metas.Add(new SqlMetaData(list[0].ChildNodes[i].Name, SqlDbType.NVarChar, SqlMetaData.Max));
  54.            }
  55.            var rec = new SqlDataRecord(metas.ToArray());
  56.            SqlContext.Pipe.SendResultsStart(rec);
  57.            foreach (System.Xml.XmlNode node in list)
  58.            {
  59.                if (flag)//行模式
  60.                    for (int i = 0; i < node.ChildNodes.Count; i++)
  61.                    {
  62.                        rec.SetString(0, node.ChildNodes[i].InnerXml);
  63.                        SqlContext.Pipe.SendResultsRow(rec);
  64.                    }
  65.                else
  66.                {
  67.                    for (int i = 0; i < metas.Count; i++)
  68.                    {
  69.                        rec.SetString(i, node.ChildNodes[i].InnerXml);
  70.                    }
  71.                    SqlContext.Pipe.SendResultsRow(rec);
  72.                }
  73.            }
  74.  
  75.  
  76.            SqlContext.Pipe.SendResultsEnd();
  77.  
  78.        }
  79.        //public static void Json2Table(string json)
  80.        //{
  81.        // //find first array
  82.        // Q.Json.Linq.JObject jo = JsonConvert.DeserializeObject(json) as Q.Json.Linq.JObject;
  83.        // Q.Json.Linq.JToken token = jo.First;
  84.        // while (token != null && token.Type != Q.Json.Linq.JTokenType.Array)
  85.        // {
  86.        // token = token.First;
  87.        // }
  88.        // if (token == null)
  89.        // return;
  90.        // Q.Json.Linq.JArray array = token as Q.Json.Linq.JArray;
  91.        // if (array.Count == 0)
  92.        // return;
  93.        // List<SqlMetaData> metas = new List<SqlMetaData>();
  94.        // token = array[0].First;
  95.        // while (token != null)
  96.        // {
  97.        // metas.Add(new SqlMetaData((token as Q.Json.Linq.JProperty).Name, SqlDbType.NVarChar, SqlMetaData.Max));
  98.        // token = token.Next;
  99.        // }
  100.  
  101.        // var rec = new SqlDataRecord(metas.ToArray());
  102.        // SqlContext.Pipe.SendResultsStart(rec);
  103.        // foreach (var item in array)
  104.        // {
  105.        // for (int i = 0; i < metas.Count; i++)
  106.        // {
  107.        // rec.SetString(i, item[metas[i].Name].ToString());
  108.        // }
  109.        // SqlContext.Pipe.SendResultsRow(rec);
  110.        // }
  111.        // SqlContext.Pipe.SendResultsEnd();
  112.  
  113.        //}
  114.    }

 

 
 

测试语句

 
 

  1.  DROP
    TABLE test
  2.  
  3. CREATE
    TABLE TEST ( NAME VARCHAR(5), DATA XML )
  4.  
  5. GO
  6.  
  7.  
  8. INSERT
    INTO TEST
  9. VALUES ( 'A', '<ROOT><M>AAAA</M></ROOT>' )
  10.         ,
  11.         ( 'B', '<ROOT><D>00000</D></ROOT>' )
  12. , ( '' )
  13.  
  14. SELECT *
  15. FROM test
  16.  
  17.  
  18. --DECLARE @X XML
  19. --SELECT @x = ( SELECT *
  20. -- FROM test
  21. -- FOR
  22. -- XML AUTO ,
  23. -- ELEMENTS ,
  24. -- ROOT
  25. -- )
  26. --SELECT @x
  27. --DECLARE @json NVARCHAR(MAX)
  28. --SELECT @json = dbo.xml2json(@x)
  29. --SELECT @json
  30. --SELECT dbo.Json2Xml(@json)
  31.  
  32.  
  33.  
  34. SELECT dbo.Xml2Json(( SELECT *
  35.                        FROM test
  36.                      FOR
  37.                        XML AUTO ,
  38.                            ELEMENTS ,
  39.                            ROOT
  40.                      )) xml2json
  41.  
  42. SELECT dbo.Json2Xml(dbo.Xml2Json(( SELECT *
  43.                                     FROM test
  44.                                   FOR
  45.                                     XML AUTO ,
  46.                                         ELEMENTS ,
  47.                                         ROOT
  48.                                   ))) json2xml
  49.  
  50.  
  51.  
  52. SET @json = dbo.Xml2Json(( SELECT *
  53.                            FROM test
  54.                          FOR
  55.                            XML AUTO ,
  56.                                ELEMENTS ,
  57.                                ROOT
  58.                          ))
  59.  
  60. EXECUTE dbo.json2table 'root/test', @json
  61. EXECUTE dbo.json2table '', @json

 

 
 

执行效果

SQL2008使用json.net实现XML与JSON互转

 
 

参考

http://www.json4sql.com/examples.html

 
 

https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/

  

上一篇:linux下自定义命令的几种实现方法


下一篇:xml和json的区别