SqlServer CLR扩展存储过程（json to table）

SqlServer2016以前是不支持处理json格式数据的需要自定义函数来解决。最常用的方式就是正则+字符串截取。利用CLR集成Newtonsoft来处理json数据会比较方便。

CLR扩展存储过程（json to table）代码如下：

   [Microsoft.SqlServer.Server.SqlProcedure]
    public static void JsonToTable(SqlString input, SqlString columns)
    {
        if (input == SqlString.Null || input.Value == null)
            return;
        string[] columnArray = null;
        if (columns == null && columns == SqlString.Null &&　columns.Value!=null)
        {       
            columnArray = columns.Value.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
        }

        Object jsonObj = JsonConvert.DeserializeObject(input.Value);
        JArray jArray = jsonObj as JArray;

        JObject jObject = jsonObj as JObject;
        if (jArray == null)
        {
            if (jObject == null)
                return;
            else
                jArray = new JArray(jObject);

        }
        else
            jObject = jArray[0] as JObject;

        SqlDataRecord rec = null;
        foreach (JObject item in jArray)
        {
            IEnumerable<JProperty> jPropertys = jObject.Properties();
            List<SqlMetaData> sqlMetaDatas = new List<SqlMetaData>();
            foreach (JProperty jProperty in jPropertys)
            {
            	//区分大小写
                if (columnArray == null || columnArray.Length == 0 || (columnArray.Length > 0 && Array.IndexOf(columnArray, jProperty.Name) >= 0))
                {
                    sqlMetaDatas.Add(new SqlMetaData(jProperty.Name, SqlDbType.NVarChar, 4000));
                }
            }
            rec = new SqlDataRecord(sqlMetaDatas.ToArray());
            break;
        }
        SqlContext.Pipe.SendResultsStart(rec);
        {
            foreach (JObject item in jArray)
            {
                IEnumerable<JProperty> jPropertys = item.Properties();
                int index = 0;
                foreach (JProperty jProperty in jPropertys)
                {
                    if (columnArray == null || columnArray.Length == 0 || (columnArray.Length > 0 && Array.IndexOf(columnArray, jProperty.Name) >= 0))
                    {
                        rec.SetSqlString(index, jProperty.Value.ToString());
                        index++;
                    }
                }
                // send new record/row
                SqlContext.Pipe.SendResultsRow(rec);
            }
        }
        SqlContext.Pipe.SendResultsEnd();    // finish sending
    }

SqlServer调用示例

EXEC	 [dbo].[JsonToTable]
@input = '{"key":"3","value":"test"}',
@columns='key,value'