CODEWITHSUNDEEP
sql-serverxmlnullxml-nil
asdf
asdf

Reputation: 141

Selecting null value from XML in SQL Server

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.

Upvotes: 14

Views: 21163

Answers (8)

Willy David Jr
Willy David Jr

Reputation: 9131

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.

Upvotes: 0

user_CC
user_CC

Reputation: 4776

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

Upvotes: 1

Pavlo Neiman
Pavlo Neiman

Reputation: 7536

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)

Upvotes: 2

Tom
Tom

Reputation: 11

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.

Upvotes: 1

Esen
Esen

Reputation: 973

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)

Upvotes: 13

pmbAustin
pmbAustin

Reputation: 3970

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)

Upvotes: 11

JonPayne
JonPayne

Reputation: 566

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

Upvotes: 6

gbn
gbn

Reputation: 432261

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

Upvotes: 2

Related Questions