Selecting null value from XML in SQL Server

Question

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.

Answer 1

In my case, I am using data type boolean so all the answer here doesn't apply to me. If you are using boolean data type, you can try this one:

myXML.value('Property1[1] cast as xs:boolean?','BIT') AS Property1,

I added this code:

cast as xs:boolean?','BIT'

Which cast if the boolean is null, if it is, it will return null, or else it will return 1 or 0.

Answer 2

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)