SqlServer解析XML，解析JSON数据格式
一：解析XML(SQL2005版本开始支持)
declare @pro_xml varchar(max)='<reg><node><pro_id>3</pro_id><pro_sale_num>10</pro_sale_num><server_amount>10.00</server_amount><express_amount>0.00</express_amount></node><node><pro_id>4</pro_id><pro_sale_num>20</pro_sale_num><server_amount>20.00</server_amount><express_amount>30.00</express_amount></node><node><pro_id>7</pro_id><pro_sale_num>30</pro_sale_num><server_amount>30.00</server_amount><express_amount>40.00</express_amount></node></reg>'declare @xml xml=@pro_xmlselect * 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_amountfrom @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"}]'selectJSON_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_amountfrom openjson(@pro_json) include_null_values
3: 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_numfrom openJson(@pro_json) as across 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 t
6:将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": "<3级","type": "晴","notice": "愿你拥有比阳光明媚的心情"},"forecast": [{"date": "22","sunrise": "07:49","high": "高温 10.0℃","low": "低温 -5.0℃","sunset": "18:03","aqi": 104.0,"ymd": "2019-01-22","week": "星期二","fx": "西南风","fl": "<3级","type": "晴","notice": "愿你拥有比阳光明媚的心情"},{"date": "23","sunrise": "07:48","high": "高温 9.0℃","low": "低温 -4.0℃","sunset": "18:04","aqi": 184.0,"ymd": "2019-01-23","week": "星期三","fx": "东北风","fl": "3-4级","type": "晴","notice": "愿你拥有比阳光明媚的心情"},{"date": "24","sunrise": "07:48","high": "高温 5.0℃","low": "低温 -3.0℃","sunset": "18:05","aqi": 262.0,"ymd": "2019-01-24","week": "星期四","fx": "南风","fl": "<3级","type": "多云","notice": "阴晴之间，谨防紫外线侵扰"},{"date": "25","sunrise": "07:48","high": "高温 4.0℃","low": "低温 -3.0℃","sunset": "18:06","aqi": 258.0,"ymd": "2019-01-25","week": "星期五","fx": "西风","fl": "<3级","type": "晴","notice": "愿你拥有比阳光明媚的心情"},{"date": "26","sunrise": "07:47","high": "高温 5.0℃","low": "低温 -1.0℃","sunset": "18:07","aqi": 195.0,"ymd": "2019-01-26","week": "星期六","fx": "东北风","fl": "<3级","type": "多云","notice": "阴晴之间，谨防紫外线侵扰"}]}'SELECT * FROM OPENJSON(@JsonStr)
小鱼儿
一时失言乱红尘
忘是亡心i
向右看齐
还没有评论，来说两句吧...