SqlServer解析XML，解析JSON数据格式

忘是亡心i 2022-05-20 04:39 561阅读 0赞

**一：解析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_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

![70][]

**二.解析JSON（SQL2016版本开始支持）**

SqlServer对json的功能主要包含 IsJson, Json\_Value, Json\_Modify, Json\_Query

1: IsJson 解析该字符串是否是合法的json格式

格式：IsJson(@express)

![70 1][]

合法返回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_values

3: Json\_Query从Json字符串中提取对象或数组

格式：Json\_Query(value,'$.object') from openjson(@express)

![70 2][]

4: json\_Modify 更新Json字符串中的属性值，并返回更新的Json字符串

格式：json\_Modify(value,'$.column',newValue) from openjson(@express)

![70 3][]

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 t

![70 4][]

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)

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdWNoYW5nMTk5NTA3MDM_size_16_color_FFFFFF_t_70][]

[70]: /images/20220520/3995978ccba546d9be7e9e918e48bb95.png
[70 1]: /images/20220520/4ba380c247644a048a93d4a3d503c208.png
[70 2]: /images/20220520/7e95433534e847febdc9062fb2bcea3d.png
[70 3]: /images/20220520/2255ea4f6b9542c3bdabf71d7ab11bf9.png
[70 4]: /images/20220520/4d4e54dcc1dd4c4f8e7b40ed1b878b22.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdWNoYW5nMTk5NTA3MDM_size_16_color_FFFFFF_t_70]: /images/20220520/1ea93dad05c9416283bceda04c05516c.png