借助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. DECLARE @json NVARCHAR(MAX)
53. SET @json = dbo.Xml2Json(( SELECT *
54.                            FROM test
55.                          FOR
56.                            XML AUTO ,
57.                                ELEMENTS ,
58.                                ROOT
59.                          ))
60.  
61. EXECUTE dbo.json2table 'root/test', @json
62. EXECUTE dbo.json2table '', @json

执行效果

参考

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

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