我正在尝试从具有 null 作为属性之一的 XML 中进行选择。它没有返回 null,而是返回 0。我做错了什么?
请参阅下面的代码进行复制:
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)
returns:
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/include-null-columns-empty-elements-125474.aspx http://go4answers.webhost4life.com/Example/including-null-columns-empty-elements-125474.aspx
[not(@xsi:nil = "true")]
这将选择空。顺便说一句,作者代码有一个拼写错误
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)
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)