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

最新推荐文章于 2024-05-13 17:50:59 发布
最新推荐文章于 2024-05-13 17:50:59 发布
阅读量9.2k 收藏 8
点赞数 3
分类专栏： SqlServer 文章标签： sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/liuchang19950703/article/details/81015511
版权

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

二.解析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_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_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

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)

码码码码码码—农
关注
  • 3
    点赞
  • 踩
  • 8
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL Server解析XML数据的方法详解
01-19
本文实例讲述了SQL Server解析XML数据的方法。分享给大家供大家参考，具体如下： --5.读取XML --下面为多种方法从XML中读取EMAIL DECLARE @x XML SELECT @x = ' <People> <dongsheng> <Info Name=Email>dongsheng@xxyy.com</Info> <Info Name=Phone>678945546</Info> <Info Name=qq>36575</Info> </dongsheng> </People>' -- 方法1 SELECT @x.value
OpenXML文档,sqlserver处理XML,可用于存储过程数组传参
12-01
利用OpenXML获取XML文件中的数据 语法 OPENXML(idoc int [in],rowpattern nvarchar[in],[flags byte[in]]) [WITH (SchemaDeclaration | TableName)] 参数。。。。。
SQL SERVER 解析XML字符串
12-11 2211
SQL SQLSERVER MSSQL 解析 XML
【SQLSERVER】中JSON函数的用法详解
寒冰的暖
03-28 1617
（3）JSON_QUERY (Transact-SQL) 从 JSON 字符串中提取对象或数组。（2）JSON_VALUE (Transact-SQL) 从 JSON 字符串中提取标量值。（4）JSON_MODIFY (Transact-SQL) 更改 JSON 字符串中的值。（1） ISJSON (Transact-SQL) 测试字符串是否包含有效 JSON。
MySQL解析JSON格式字段并取出部分值
最新发布
ChinaLiaoTian的博客
05-13 660
MySQL JSON
sqlserver使用json数据
hdhdhdhdhdhdhdh的博客
11-16 952
通过确保插入的 JSON 数据是有效的，并符合 MySQL 的 JSON 格式要求，我们可以成功地将 JSON 数据插入到 MySQL 表中。在sqlserver 中存储json ，需要用字符串类型进行存储，一般用nvarchar()或 varchar()进行存储，不要用text进行存储，用text时候，json的函数不支持。（5）Json_Modify:更新Json字符串中的属性值，并返回更新的Json字符串。Json_Modify:更新Json字符串中的属性值，并返回更新的Json字符串。
SQL Server中将数据导出为XML和Json方法分享
09-10
在SQL Server中，有时我们需要将数据导出成XML或JSON格式，以便于数据交换、分析或其他系统的集成。本文将详细讲解如何使用SQL Server配合PowerShell的BCP命令来完成这一任务。 首先，让我们关注XML导出。从SQL ...
SQL SERVER 将XML变量转为JSON文本
09-10
在SQL Server中，将XML数据转换为JSON格式是常见的需求，特别是在处理Web服务或与JavaScript进行数据交互时。本文将详细介绍如何使用SQL Server内置函数将XML变量转换为JSON文本。 首先，我们需要创建一个用户定义...
SQL Server中将查询结果转换为Json格式脚本分享
09-10
在SQL Server中，有时我们需要将查询结果转换成JSON格式，以便于前端展示或者进行数据交换。这个场景在现代Web开发中非常常见，因为JSON是一种轻量级的数据交换格式，易于人阅读和编写，同时也易于机器解析和生成。...
SqlServer将查询结果转换为XML和JSON
09-09
在SQL Server中，将查询结果转换为XML和JSON格式是常见的需求，这在数据交换、API接口开发或者数据分析时尤其有用。下面将详细介绍如何实现这两种转换。 1. 查询结果转XML 在SQL Server中，你可以使用内置的`FOR ...
一种Android系统访问SQL Server数据库的方法.pdf
09-19
对于响应，Android客户端需要解析返回的数据，通常是XML或JSON格式，以便进一步处理和展示。 **3. SQL Server数据库操作** Web Service通常使用诸如Java，ASP.NET或其他服务器端语言实现，它们可以直接与SQL ...
SQL Server之JSON 函数详解
12-16
SQL Server 2005开始支持XML数据类型，提供原生的XML数据类型、XML索引及各种管理或输出XML格式的函数。随着JSON的流行，SQL Server2016开始支持JSON数据类型，不仅可以直接输出JSON格式的结果集，还能读取JSON格式的数据。 1 概述 本篇文件将结合MSND简要分析Sqlserver中JSON函数，主要包括ISJSON,JSON_VALUE,JSON_MODIFY,JSON_QUERY。 2 具体内容 2.1 JSON 函数 使用本节中页面中描述的功能来验证或更改JSON文本或提取简单或复杂的值。 有关在SQL Server中内置JSON支持的更多信息
SQL Server 2005 读取xml 文件
老高的博客
01-24 4306
使用函数 OPENROWSET 读取xml文件。函数 OPENROWSET 中，通过使用 SINGLE_BLOB，可以避免 XML 文档的编码（由 XML 编码声明所指定）与服务器隐含使用的字符串代码页不匹配的问题。实例1：INSERT INTO T(XmlCol)SELECT * FROM OPENROWSET(   BULK c:SampleFolderSampleDat
【SqlServer】JSON函数
weixin_34204722的博客
08-22 1334
1   概述  本篇文件将结合MSND简要分析Sqlserver中JSON函数，主要包括ISJSON,JSON_VALUE,JSON_MODIFY,JSON_QUERY。 2   具体内容 2.1  JSON 函数 使用本节中页面中描述的功能来验证或更改JSON文本或提取简单或复杂的值。 有关在SQL Server中内置JSON支持的更多信息，请参阅JSON数据（SQ...
【MSSQL】解析Json数据
qq_39941918的博客
03-15 1352
CREATE FUNCTION uf_parseJSON( @JSON NVARCHAR(MAX)) RETURNS @JsonType TABLE(element_id INT IDENTITY(1, 1) NOT NULL,sequenceNo INT NULL,parent_ID INT,Object_ID INT,NAME NVARCHAR(2000),StringValue NVARCHAR(MAX) NOT NULL,ValueType VARCHAR(10) NOT NULL ) AS
MSSQL解析多层嵌套json文件
weixin_44519072的博客
07-04 350
"cuisineName": "鱼香肉丝","cuisineName": "红烧茄子","cuisineName": "鱼香肉丝","cuisineName": "红烧茄子","cuisineName": "鱼香肉丝","cuisineName": "红烧茄子","cuisineName": "鱼香肉丝","cuisineName": "红烧茄子","cuisineName": "鱼香肉丝","cuisineName": "红烧茄子","cuisineName": "鱼香肉丝",
SQL Server 中的 JSON 数据
weixin_30797027的博客
06-26 1161
下面是 JSON 文本的示例 [{ "name": "John", "skills": ["SQL", "C#", "Azure"]}, { "name": "Jane", "surname": "Doe"}] 通过使用 SQL Server 内置函数和运算符，你可以对 JSON 文本执行以下操作： 分析 JSON 文本和读取或修改值。 将 JSON 对象数组转...
XML格式怎么在sqlserver数据解析转换
05-26
在SQL Server中，可以使用内置的XML函数和方法来解析和转换XML格式的数据。以下是一些常用的XML函数和方法： 1. CONVERT函数：可以将XML类型的数据转换为VARCHAR类型的数据。 2. CAST函数：可以将XML类型的数据...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值