Selecting null value from XML in SQL Server

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 think if you use the number() function, you'll get null as expected. This only works for number types though:

select 
   ParamValues.TaskChainerTask.query('Property1').value('number(.)','int') as Property1,         
   ParamValues.TaskChainerTask.query('Property2').value('number(.)','int') as Property2
from @a.nodes('(/TestSet/Element)') as ParamValues(TaskChainerTask) 

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

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...

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)

I'm not sure if your particular case requires you to sub-query the nodes first, but if not you can request the .value and provide the xPath. Since the Property1 node exists, you want to evaluate the text() of the Property1 node and not the node itself:

 select  ParamValues.TaskChainerTask.value('Property1[1]/text()[1]','int') as Property1,
        ParamValues.TaskChainerTask.value('Property2[1]/text()[1]','int') as Property2

   from @a.nodes('(/TestSet/Element)') as ParamValues(TaskChainerTask)

In addition to make sure this works in other cases you can provide the most detailed element path in the @a.nodes xPath statment and walk up with "../" instead of sub-querying the node results.

A clever way of doing this would be to remove the Property1 node from the XML where null values are desired. So what ever node you want to be null in your result set just do not add it to the XML. In this way you will not have to add the xsi:nill attribute as well.

So Below will also result in a null:

declare @a xml
select @a = '<TestSet>
  <Element>
    <Property1>1</Property1>
    <Property2>1</Property2>
  </Element>
  <Element>
     //I have removed the property1 node and this will result in a null value
    <Property2>2</Property2>
  </Element>
  <Element>
    <Property1>3</Property1>
    <Property2>3</Property2>
  </Element>
</TestSet>'

 select ParamValues.TaskChainerTask.value('./Property1[1]','int') as Property1,
        ParamValues.TaskChainerTask.value('./Property2[1]','int') as Property2
   from @a.nodes('(/TestSet/Element)') as ParamValues(TaskChainerTask)

It can be seen in the above example that there is no Property1 node hence it will result in a null value

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.