SqlServer解析XML，解析JSON数据格式 |
您所在的位置：网站首页 › sqlserver解析json提取数据 › SqlServer解析XML，解析JSON数据格式 |
SqlServer解析XML，解析JSON数据格式
|
一：解析XML(SQL2005版本开始支持) declare @pro_xml varchar(max)= ' 3 10 10.00 0.00 4 20 20.00 30.00 7 30 30.00 40.00 ' declare @xml xml=@pro_xml select * from ( select t.c.value('(pro_id/text())[1]','int') as pro_id, t.c.value('(pro_sale_num/text())[1]','int') as pro_sale_num, t.c.value('(server_amount/text())[1]','decimal(18,2)') as server_amount, t.c.value('(express_amount/text())[1]','decimal(18,2)') as express_amount from @xml.nodes('/reg/node') as t(c) ) as T二.解析JSON（SQL2016版本开始支持） SqlServer对json的功能主要包含 IsJson, Json_Value, Json_Modify, Json_Query 1: IsJson 解析该字符串是否是合法的json格式 格式：IsJson(@express) declare @pro_json varchar(max) set @pro_json='[ {"pro_id":3,"pro_sale_num":10,"server_amount":"10.00","express_amount":"5"}, {"pro_id":4,"pro_sale_num":15,"server_amount":"15.00","express_amount":"10"}, {"pro_id":7,"pro_sale_num":20,"server_amount":"20.00","express_amount":"15"} ]' select IsJson(@pro_json)合法返回1，不合法返回0, 如果表达式为 NULL，则返回NULL 2: Json_Value 从Json字符串中提出标量值 格式：Json_Value (value,'$.column') from openjson(@express) 注：include_null_values 表示表达式中为NULL,输出NULL declare @pro_json varchar(max) set @pro_json='[ {"pro_id":3,"pro_sale_num":10,"server_amount":"10.00","express_amount":"5"}, {"pro_id":4,"pro_sale_num":15,"server_amount":"15.00","express_amount":"10"}, {"pro_id":7,"pro_sale_num":20,"server_amount":"20.00","express_amount":"15"} ]' select JSON_VALUE(value,'$.pro_id') as pro_id, JSON_VALUE(value,'$.pro_sale_num') as pro_sale_num, JSON_VALUE(value,'$.server_amount') as server_amount, JSON_VALUE(value,'$.express_amount') as express_amount from openjson(@pro_json) include_null_values3: Json_Query从Json字符串中提取对象或数组 格式：Json_Query(value,'$.object') from openjson(@express) declare @pro_json varchar(max) set @pro_json='[ {"pro_id":3,"pro_sale_num":[{"sale_id":13,"sale_num":103}],"server_amount":"10.00","express_amount":"5"}, {"pro_id":4,"pro_sale_num":[{"sale_id":14,"sale_num":104}],"server_amount":"15.00","express_amount":"10"} ]' select JSON_VALUE(value,'$.pro_id'),JSON_QUERY(VALUE,'$.pro_sale_num') from openjson(@pro_json)4: json_Modify 更新Json字符串中的属性值，并返回更新的Json字符串 格式：json_Modify(value,'$.column',newValue) from openjson(@express) declare @pro_json varchar(max) set @pro_json= '[ {"pro_id":3,"pro_sale_num":[{"sale_id":13,"sale_num":103}],"server_amount":"10.00","express_amount":"5"}, {"pro_id":4,"pro_sale_num":[{"sale_id":14,"sale_num":104}],"server_amount":"15.00","express_amount":"10"} ]' select JSON_MODIFY(value,'$.pro_id','2') from openjson(@pro_json)5:多层复杂的Json解析 declare @pro_json varchar(max) set @pro_json= '[ {"pro_id":3,"pro_sale_num":[{"sale_id":13,"sale_num":103}],"server_amount":"10.00","express_amount":"5"}, {"pro_id":4,"pro_sale_num":[{"sale_id":14,"sale_num":104}],"server_amount":"15.00","express_amount":"10"} ]' select JSON_VALUE(value,'$.pro_id')as pro_id, JSON_VALUE(value,'$.server_amount') as server_amount, JSON_VALUE(value,'$.express_amount') as express_amount, t.sale_id,t.sale_num from openJson(@pro_json) as a cross apply (select JSON_VALUE(value,'$.sale_id')as sale_id,JSON_VALUE(value,'$.sale_num')as sale_num from openJson(Json_Query(value,'$.pro_sale_num')) as b) as t6:将Json字符串解析为表对象 DECLARE @JsonStr VARCHAR(max)= '{ "shidu": "33%", "pm25": 80.0, "pm10": 127.0, "quality": "轻度污染", "wendu": "7", "ganmao": "儿童、老年人及心脏、呼吸系统疾病患者人群应减少长时间或高强度户外锻炼", "yesterday": { "date": "21", "sunrise": "07:49", "high": "高温 7.0℃", "low": "低温 -5.0℃", "sunset": "18:02", "aqi": 77.0, "ymd": "2019-01-21", "week": "星期一", "fx": "西风", "fl": " |
【本文地址】
今日新闻 |
推荐新闻 |