SQLSERVER NULL值的XML处理

已于 2022-07-14 15:47:07 修改
阅读量885 收藏 1
点赞数 1
分类专栏： SQL SERVER 数据库 文章标签： xml sqlserver
于 2022-07-14 15:44:36 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xiaojia_boke/article/details/125784238
版权

1、使用for xml语句将数据库中的的数据生成xml文档时，有些值为null的列没有生成到xml文档；

如下图：

使用语句：

SELECT TOP 1 tp.ID, tp.machine_id, tp.plan_time, tp.product_num, tp.art_num,
       tp.start_time, tp.end_time, tp.arrange_main_iden
FROM DADXDB.dbo.table_plan AS tp
ORDER BY tp.plan_time DESC
FOR XML PATH('plan_rec') ,ELEMENTS

生成xml:

<plan_rec>
  <ID>18700</ID>
  <machine_id>DX-5</machine_id>
  <plan_time>2022-07-14T14:10:29</plan_time>
  <product_num>D470219A</product_num>
  <art_num>D470219A</art_num>
  <arrange_main_iden>52233</arrange_main_iden>
</plan_rec>

这里不包含start_time和end_time。

如何才能让xml中包含这两列呢？

使用下面的方法：

SELECT TOP 1 tp.ID, tp.machine_id, tp.plan_time, tp.product_num, tp.art_num,
       tp.start_time, tp.end_time, tp.arrange_main_iden
FROM DADXDB.dbo.table_plan AS tp
ORDER BY tp.plan_time DESC
FOR XML PATH('plan_rec') ,ELEMENTS XSINIL

生成的xml如下：

<plan_rec xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ID>18700</ID>
  <machine_id>DX-5</machine_id>
  <plan_time>2022-07-14T14:10:29</plan_time>
  <product_num>D470219A</product_num>
  <art_num>D470219A</art_num>
  <start_time xsi:nil="true" />
  <end_time xsi:nil="true" />
  <arrange_main_iden>52233</arrange_main_iden>
</plan_rec>

请注意上面的xml中已经包含了start_time和end_time列，另外也在开始时增加了一个namespace的定义，这些就是使用xsinil后起的效果；

2、如何从xml文档中正确读出null值到sqlserver中

上面的方法说明如何将null值生成到xml中，那么在xml中的null值如何能被正确的识别处理呢？特别时对于日期类型值，默认情况读出时，null值往往被转换为1900-1-1 00:00:00，这些不是一个很好的地方；

如，下面的xml:

<plan_rec xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ID>18700</ID>
  <machine_id>DX-5</machine_id>
  <plan_time>2022-07-14T14:10:29</plan_time>
  <product_num>D470219A</product_num>
  <art_num>D470219A</art_num>
  <start_time xsi:nil="true" />
  <end_time xsi:nil="true" />
  <arrange_main_iden>52233</arrange_main_iden>
</plan_rec>

(1)、使用xml的value语句：

DECLARE @tmpXml XML
SELECT @tmpXml='<plan_rec xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ID>18700</ID>
  <machine_id>DX-5</machine_id>
  <plan_time>2022-07-14T14:10:29</plan_time>
  <product_num>D470219A</product_num>
  <art_num>D470219A</art_num>
  <start_time xsi:nil="true" />
  <end_time xsi:nil="true" />
  <arrange_main_iden>52233</arrange_main_iden>
</plan_rec>'

SELECT @tmpXml.value('(/plan_rec/start_time)[1]','datetime')

执行结果如下：

注意这里的datetime 没有正确的被转换成null;

DECLARE @tmpXml XML 
SELECT @tmpXml='<plan_rec xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ID>18700</ID>
  <machine_id>DX-5</machine_id>
  <plan_time>2022-07-14T14:10:29</plan_time>
  <product_num>D470219A</product_num>
  <art_num>D470219A</art_num>
  <start_time xsi:nil="true" />
  <end_time xsi:nil="true" />
  <arrange_main_iden>52233</arrange_main_iden>
</plan_rec>'

SELECT @tmpXml.value('(/plan_rec/start_time[not(@xsi:nil = "true")])[1]','datetime')

注意上面代码中在xpath部分，增加了一个属性过滤条件：[not(@xsi:nil = "true")]，这样会报标识为nil的元素过滤掉，所以，会正确返回null,如下：

 （2）、使用openxml语句

尝试1：


DECLARE @tmpXml VARCHAR(8000) 
SELECT @tmpXml= '<plan_rec xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ID>18700</ID>
  <machine_id>DX-5</machine_id>
  <plan_time>2022-07-14T14:10:29</plan_time>
  <product_num>D470219A</product_num>
  <art_num>D470219A</art_num>
  <start_time xsi:nil="true" />
  <end_time xsi:nil="true" />
  <arrange_main_iden>52233</arrange_main_iden>
</plan_rec>'
DECLARE @hdoc INT
EXEC sp_xml_preparedocument @hdoc OUTPUT,@tmpXml
SELECT *
FROM OPENXML(@hdoc,'/plan_rec',2)
WITH(
	ID INT ,
	machine_id VARCHAR(10),
	plan_time DATETIME,
	product_num VARCHAR(10),
	art_num VARCHAR(20),
	start_time DATETIME,
	end_time DATETIME,
	arrange_main_iden INT
)

执行结果：

从执行结果中可以看到start_time和end_time没有被正确读取；

尝试2，在with列定义处，指明xpath路径采用类似value方法的属性过滤


DECLARE @tmpXml VARCHAR(8000) 
SELECT @tmpXml= '<plan_rec xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ID>18700</ID>
  <machine_id>DX-5</machine_id>
  <plan_time>2022-07-14T14:10:29</plan_time>
  <product_num>D470219A</product_num>
  <art_num>D470219A</art_num>
  <start_time xsi:nil="true" />
  <end_time xsi:nil="true" />
  <arrange_main_iden>52233</arrange_main_iden>
</plan_rec>'
DECLARE @hdoc INT
EXEC sp_xml_preparedocument @hdoc OUTPUT,@tmpXml
SELECT *
FROM OPENXML(@hdoc,'/plan_rec',2)
WITH(
	ID INT ,
	machine_id VARCHAR(10),
	plan_time DATETIME,
	product_num VARCHAR(10),
	art_num VARCHAR(20),
	start_time DATETIME '/start_time[not(@xsi:nil = "true")]',
	end_time DATETIME,
	arrange_main_iden INT
)

EXEC sp_xml_removedocument @hdoc

但是这时执行，会报如下错误;

 尝试3：

在使用sp_xml_preparedocument过程时，指明命名空间，如下：

DECLARE @tmpXml VARCHAR(8000) 
SELECT @tmpXml= '<plan_rec xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ID>18700</ID>
  <machine_id>DX-5</machine_id>
  <plan_time>2022-07-14T14:10:29</plan_time>
  <product_num>D470219A</product_num>
  <art_num>D470219A</art_num>
  <start_time xsi:nil="true" />
  <end_time xsi:nil="true" />
  <arrange_main_iden>52233</arrange_main_iden>
</plan_rec>'
DECLARE @hdoc INT
EXEC sp_xml_preparedocument @hdoc OUTPUT,@tmpXml,'<plan_rec xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>'
SELECT *
FROM OPENXML(@hdoc,'/plan_rec',2)
WITH(
	ID INT ,
	machine_id VARCHAR(10),
	plan_time DATETIME 'plan_time[not(@xsi:nil = "true")]' ,
	product_num VARCHAR(10),
	art_num VARCHAR(20),
	start_time DATETIME '/start_time[not(@xsi:nil = "true")]',
	end_time DATETIME  '/end_time[not(@xsi:nil = "true")]',
	arrange_main_iden INT
)

EXEC sp_xml_removedocument @hdoc

执行结果如下：

这个才是理想的解析结果

3、使用select for xml查询生成xml后，直接使用value函数报错问题；

如下面语句：

SELECT 
(
	SELECT TOP 1 tp.ID, tp.machine_id, tp.plan_time, tp.product_num, tp.art_num,
	       tp.start_time, tp.end_time, tp.arrange_main_iden
	FROM DADXDB.dbo.table_plan AS tp
	ORDER BY tp.plan_time DESC
	FOR XML PATH('plan_rec') ,ELEMENTS XSINIL  
).value('(/plan_rec/start_time)[1]','datetime')

会报下图错误：

这时我们只需要对上面sql语句做如下修改就可以解决这个问题：

SELECT 
(
	SELECT TOP 1 tp.ID, tp.machine_id, tp.plan_time, tp.product_num, tp.art_num,
	       tp.start_time, tp.end_time, tp.arrange_main_iden
	FROM DADXDB.dbo.table_plan AS tp
	ORDER BY tp.plan_time DESC
	FOR XML PATH('plan_rec') ,ELEMENTS XSINIL,TYPE  
).value('(/plan_rec/start_time)[1]','datetime')

 注意：上面代码中增加了Type关键字，表示当前这个查询结果是一个xml类型，这时就不会报错了。

对于上面的内容，对于有特殊要求时可以按照上面的方式处理；，如果没有的话完全可以使用sqlserver的默认行为处理：

1、sqlserver中的null数据不会生成到xml中；

2、在使用value/openxml读取xml时，这些的对应信息也自然会是null

3、如果sqlserver中有值，那么就会出现到xml中；

4、这样后续读取时，也自然会读取到。

当然，对于一些非字符串类型，sqlserver会有一些默认值，而这些默认值往往不是我们想要的，所以简单一点，就是将所有的类就是声明成字符串类型，这样如果有null值时，会解释为空串，这样可能会更符合我们的理解习惯。

jiatongtong
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
sqlserver 不能将值NULL插入列id(列不允许有空值解决)
12-15
错误现象：  Microsoft OLE DB Provider for SQL Server 错误 ‘80040e2f’不能将值 NULL 插入列 ‘id’，表 ‘web.dbo.dingdan’；列不允许有空值。... 您可能感兴趣的文章:SqlServer下通过XML拆分字符串的方
SQL Server中的NULL值处理：判断与解决方案
2301_78834737的博客
07-07 4059
其实可以用一种常识来解释，比如你编写一本字典，但是呢，你这个字典就只有一个或者几个值，换句话说就是数据量很小，当你查字典的时候，直接翻字典，第一页就是，比你再去查什么拼音，偏旁啥的来得快，这就是因为sqlserver数据库引擎通过判断，扫描全表的效率表走索引块，但是对于nulll列是否会走索引，一直都有分歧，这个我们就不多做赘述，还是回到正题。本文将介绍SQL Server中判断和处理NULL值的不同方法，以及一些解决方案，帮助您更好地处理数据库中的NULL值情况。这可能增加代码的复杂性和维护成本。
java语言-JAXB在XML序列化时去除默认命名空间xmlns:xsd和xmlns:xsi
西京刀客
10-21 4125
文章目录在XML序列化时去除默认命名空间xmlns:xsd和xmlns:xsi 在XML序列化时去除默认命名空间xmlns:xsd和xmlns:xsi Jaxb marshaller总是写xsi：nil（即使在@XmlElement（required = false，nillable = true）时）(Jaxb marshaller always writes xsi:nil (even when @XmlElement(required=false, nillable=true))) 参考URL: ht
Mybatis——xml文件映射sql返回结果为null或者一次查询出现多个结果问题
student_hwj的博客
11-04 1154
使用mybatis的xml映射sql查询时，如果查询结果没有数据，就返回为空，不会生成对象（正常现象是无论查询结果如何，都会返回生成一个对象），如果模糊查询有数据，就会报错，错误：一个查询出现两个结果。
SQLServer XML数据的五种基本操作
12-15
0表示不存在，1表示存在，Null表示输入为空 2.xml.value 输入为XQuery表达式，返回一个SQL Server标量值 3.xml.query 输入为XQuery表达式，返回一个SQL Server XML类型流 4.xml.nodes 输入为XQuery表达式，返回一个...
SQLServer2005 XML数据操作代码
01-21
示例： 创建Table 代码如下: CREATE TABLE [dbo].[xmlTable]( [id] [int] IDENTITY(1,1) NOT NULL, [doc] [xml] NULL ) 一。插入数据 1.通过XML文件插入 1.xml 代码如下: <?xml version=’1.0′ encoding=’utf-8...
实现SQL Server 原生数据从XML生成JSON数据的实例代码
01-21
实现SQL Server 原生数据从XML生成JSON数据的实例代码  SQL Server 是关系数据库，查询结果通常都是数据集，但是在一些特殊需求下，我们需要XML数据，最近这些年，JSON作为WebAPI常用的交换数据格式，那么数据库...
dbForge Studio 2023 for SQL Server Enterprise 6.5.1 x64【亲测官网最新版】
最新发布
08-16
截止到目前（2023-08-16）官网最新版本，本次新增功能如下： 添加了对 GENERATE_SERIES 和 OPENQUERY ...改进了数据导入功能中的 JSON 文件读取、NULL 值检测和错误处理 修复了数据编辑器中错误地指示缺少 SQL Server
SQL Server Bulk Insert 只需要部分字段时的方法
12-15
根据一般做法的话，导出...以下是测试记录： 代码如下: USE master GO CREATE DATABASE [db_test] GO USE db_test GO CREATE TABLE dbo.T_test( ID [int] IDENTITY(1,1) NOT NULL, Code varchar(10) , Name varchar(10
Sqlserver2000经典脚本
05-23
介绍就不多说了，下边是部分目录，觉得有用的话就顶一个 C:. │ sqlserver2000.txt │ ├─第01章 │ 1.9.1 设置内存选项.sql │ 1.9.2(2) 使用文件及文件组.sql │ 1.9.2(3) 调整...
SQL Server 2008编程入门经典(第3版)
08-29
第1章 RDBMS基础：SQLServer数据库的构成 1.1 数据库对象概述 1.1.1 数据库对象 1.1.2 事务日志 1.1.3 最基本的数据库对象：表 1.1.4 文件组 1.1.5 数据库关系图 1.1.6 视图 1.1.7 存储过程 1.1.8 用户自定义函数 ...
SQL Server 2008管理员必备指南(超高清PDF)Part3
07-17
10.5.4 允许和不允许Null值 10.5.5 使用默认值 10.5.6 使用稀疏列 10.5.7 使用标识和全局唯一标识符 10.5.8 使用用户定义的表类型 10.6 使用视图 10.6.1 操作视图 10.6.2 创建视图 10.6.3 修改视图 10.6.4 使用可...
SQL.Server.2008管理员必备指南.part2.rar（2/4）
05-04
　SQL Server专家的呕心力作，数据库管理员的实战宝典，全面、深入地剖析SQL Server2008新特性，结构独特，实例丰富，操作性强。 编辑本段 目录 　第Ⅰ部分 SQL Server 2008管理基础 　第1章 SQL Server 2008管理...
SQL Server : FOR XML PATH 函数用法以及转义字符正常显示
枫的专栏
04-14 5320
For XML,就是将查询结果作为XML来检索。XML有4中模式，官方文档上有这4种模式的使用方法。模式描述RAWSELECT 语句返回的行集中为每个行生成一 元素AUTO将基于指定 SELECT 语句的方式来使用试探性方法在 XML 结果中生成嵌套。（说的是什么？是能够生成嵌套的XML吗？EXPICIT模式允许对 XML 的形状进行更多控制。PATH任务比较简单的方式。仅当执行设置了这些模式的查询时，这些模式才有效。它们不会影响以后执行的任何查询的结果。
OPENXML解析sp_xml_preparedocument获取的XML句柄
weixin_30402343的博客
09-21 275
1、sp_xml_preparedocument与sp_xml_removedocument （1）sp_xml_preparedocument返回可用于访问新创建的内部表示形式的 XML 文档的句柄。 sp_xml_preparedocument hdoc OUTPUT [ , xmltext ] [ , xpath_namespaces ] hdoc新建文档的句柄...
sp_xml_preparedocument sp_xml_removedocument 使用 实例 解析
firetaker的专栏
05-10 2020
--声明一个xml对象字符串declare @studentxml as varchar(200)--设置处理xml实例set @studentxml= --声明@dociddeclare @docid int  exec sp_xml_preparedocument @docid output, @studentxml  --检索相应数据保存到对应数据列中
sqlserver null知多少
Hehuyi_In的博客
11-24 1877
一、 null是什么 不知道。我是说，它的意思就是不知道（unknown）。null与true、false组成谓词的三个逻辑值，但它没有明确的值，在不同的场景下，它能代表不同的含义。 下文以例子的方式给大家分享下null使用的典型场景及对应的用法。 二、 check约束与null 之前在SQL ServerCentral.com上看到一个关于check约束的null问题。 作者创建...
xml模糊查询sqlserver
07-28
在SQL Server中进行XML模糊查询可以使用XPath表达式和LIKE运算符的结合。根据引用\[1\]中的示例，可以使用以下语法进行查询： ```sql SELECT <column_name> FROM MyTable WHERE <column_name>.value('(/root/sub-tag)\[1\]', 'varchar(max)') LIKE 'StringToSearchFor' ``` 其中，`<column_name>`是要查询的列名，`MyTable`是表名，`(/root/sub-tag)\[1\]`是XPath表达式，用于指定要查询的XML节点路径，`StringToSearchFor`是要模糊匹配的字符串。 另外，根据引用\[2\]中的示例，如果要在`patient_info`表中进行模糊查询，可以使用以下语法： ```sql SELECT * FROM patient_info WHERE create_time LIKE '%' + ${searchTime} + '%' ``` 其中，`${searchTime}`是要查询的日期字符串，使用`LIKE`运算符和`%`通配符来实现模糊匹配。 请根据具体的需求选择适合的查询语法进行XML模糊查询。 #### 引用[.reference_title] - *1* [SQLSERVER XML 类型列的模糊查询](https://blog.csdn.net/weixin_34306676/article/details/94133268)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insert_down1,239^v3^insert_chatgpt"}} ] [.reference_item] - *2* [关于在xml文件中拼接模糊查询SQL语句问题](https://blog.csdn.net/XuDream/article/details/122177326)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insert_down1,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值