借助CLR,首先实现字符串的互转,然后使用存储过程实现JSON2table
-
public class JsonFunction
-
{
-
/// <summary>
-
/// XML转JSON
-
/// </summary>
-
/// <param name="xml"></param>
-
/// <returns></returns>
-
/// <remarks>
-
/// json不建议太长
-
/// </remarks>
-
[Microsoft.SqlServer.Server.SqlFunction(Name = "Xml2Json")]
-
public static 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();
-
return new SqlString(json);
-
-
}
-
[Microsoft.SqlServer.Server.SqlFunction(Name = "Json2Xml")]
-
public static 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));
-
return new SqlXml(new System.Xml.XmlTextReader(new System.IO.StringReader(sw.ToString())));
-
-
}
-
[Microsoft.SqlServer.Server.SqlProcedure(Name = "Json2Table")]
-
public static void 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();
-
-
//}
-
}
测试语句
-
-
-
-
-
VALUES ( '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)
-
-
-
-
FROM test
-
XML AUTO ,
-
ELEMENTS ,
-
ROOT
-
)) xml2json
-
-
FROM test
-
XML AUTO ,
-
ELEMENTS ,
-
ROOT
-
))) json2xml
-
-
-
-
FROM test
-
XML AUTO ,
-
ELEMENTS ,
-
ROOT
-
))
-
执行效果
参考
http://www.json4sql.com/examples.html
https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/