在SQL Server中从XML选择空值 [英] Selecting null value from XML in SQL Server
本文介绍了在SQL Server中从XML选择空值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试从具有null作为属性之一的XML中进行选择.而不是返回null,而是返回0.我在做什么错?
请参见下面的代码进行复制:
I'm trying to select from XML that has a null as one of the attributes. Instead of returning a null, it returns a 0. What am I doing wrong?
See code below to replicate:
declare @a xml
select @a = '<TestSet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instace">
<Element>
<Property1>1</Property1>
<Property2>1</Property2>
</Element>
<Element>
<Property1 xsi:nil="true" />
<Property2>2</Property2>
</Element>
<Element>
<Property1>3</Property1>
<Property2>3</Property2>
</Element>
</TestSet>'
select ParamValues.TaskChainerTask.query('Property1').value('.','int') as Property1,
ParamValues.TaskChainerTask.query('Property2').value('.','int') as Property2
from @a.nodes('(/TestSet/Element)') as ParamValues(TaskChainerTask)
返回:
Property1 Property2
1 1
0 2
3 3
这将返回相同的内容:
declare @a xml
select @a = '<TestSet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instace">
<Element>
<Property1>1</Property1>
<Property2>1</Property2>
</Element>
<Element>
<Property1 xsi:nil="true" />
<Property2>2</Property2>
</Element>
<Element>
<Property1>3</Property1>
<Property2>3</Property2>
</Element>
</TestSet>'
select ParamValues.TaskChainerTask.query('Property1').value('.','int') as Property1,
ParamValues.TaskChainerTask.query('Property2').value('.','int') as Property2
from @a.nodes('(/TestSet/Element)') as ParamValues(TaskChainerTask)
先谢谢了.
推荐答案
http://go4answers.webhost4life.com/Example/includes-null-columns-empty-elements-125474.aspx
[not(@xsi:nil = "true")]
这将选择null.顺便说一下,作者代码有错字
This will select null. By the way author code has a typo
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instace"
实例被误拼为实例
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
作者代码的工作版本
declare @a xml
select @a = '<TestSet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Element>
<Property1>1</Property1>
<Property2>1</Property2>
</Element>
<Element>
<Property1 xsi:nil="true" />
<Property2>2</Property2>
</Element>
<Element>
<Property1>3</Property1>
<Property2>3</Property2>
</Element>
</TestSet>'
select ParamValues.TaskChainerTask.value('./Property1[1][not(@xsi:nil = "true")]','int') as Property1,
ParamValues.TaskChainerTask.value('./Property2[1][not(@xsi:nil = "true")]','int') as Property2
from @a.nodes('(/TestSet/Element)') as ParamValues(TaskChainerTask)
这篇关于在SQL Server中从XML选择空值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文