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);
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())));
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. ???????????}
76. ???????????SqlContext.Pipe.SendResultsEnd();
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. ???????// }
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();
113. ???????//}
114. ???}

?

? ?

測試語句

? ?

1. DROP TABLE test
3. CREATE TABLE TEST ( NAME VARCHAR(5), DATA XML )
5. GO
8. INSERT INTO TEST
9. VALUES ( 'A', '<ROOT><M>AAAA</M></ROOT>' )
10. ????????,
11. ????????( 'B', '<ROOT><D>00000</D></ROOT>' )
12. , ( 'C', '123' )
14. SELECT *
15. FROM test
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)
34. SELECT dbo.Xml2Json(( SELECT *
35. ???????????????????????FROM test
36. ?????????????????????FOR
37. ???????????????????????XML AUTO ,
38. ???????????????????????????ELEMENTS ,
39. ???????????????????????????ROOT
40. ?????????????????????)) xml2json
42. SELECT dbo.Json2Xml(dbo.Xml2Json(( SELECT *
43. ????????????????????????????????????FROM test
44. ??????????????????????????????????FOR
45. ????????????????????????????????????XML AUTO ,
46. ????????????????????????????????????????ELEMENTS ,
47. ????????????????????????????????????????ROOT
48. ??????????????????????????????????))) json2xml
52. DECLARE @json NVARCHAR(MAX)
53. SET @json = dbo.Xml2Json(( SELECT *
54. ???????????????????????????FROM test
55. ?????????????????????????FOR
56. ???????????????????????????XML AUTO ,
57. ???????????????????????????????ELEMENTS ,
58. ???????????????????????????????ROOT
59. ?????????????????????????))
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/

??