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"&gt;
  <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"&gt;
  <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

I've simply used NULLIF to turn empty strings into NULLs as needed.

You can generate nil now using FOR XML, but I've never worked out how to parse it sorry...

Answer 2

Because you are setting the fields to INT you have the problem that both xsi:nil="true" fields and the value 0 will end up as 0 as the default value for INT Is 0.

You could convert to VARCHAR first to detect the empty string ('') that string fields containing xsi:nil="true" produce and then convert the result to INT.

This SELECT will give you the answer you are after

SELECT  CONVERT(INT,NULLIF(ParamValues.TaskChainerTask.query('Property1').value('.', 'varchar(5)'),'')) AS Property1
      , CONVERT(INT,NULLIF(ParamValues.TaskChainerTask.query('Property2').value('.', 'varchar(5)'),'')) AS Property2
FROM    @a.nodes('(/TestSet/Element)') AS ParamValues (TaskChainerTask) 

The result of this gives you:

Property1   Property2
1           1
NULL        2
3           3