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)
returns:
Property1 Property2
1 1
0 2
3 3
This returns the same thing:
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)
Thanks in advance.
http://go4answers.webhost4life.com/Example/including-null-columns-empty-elements-125474.aspx
[not(@xsi:nil = "true")]
This will select null. By the way author code has a typo
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instace"
instance is misspelled as instace
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
Working version of author code
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)
I would sugest this approach:
DECLARE @a XML = '<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
.value('./Property1[not(./@*[local-name()="nil"] = "true")][1]', 'int') as Property1,
ParamValues.TaskChainerTask
.value('./Property2[not(./@*[local-name()="nil"] = "true")][1]', 'int') as Property2
FROM @a.nodes('//Element') ParamValues(TaskChainerTask)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With