Treating empty elements as nulls in SQL Server's OPENXML function

Question

I have the following (highly simplified) XML document that I am reading into my database using the OPENXML function:

<root>
    <row>
        <bar>123</bar>
    </row>
    <row>
        <bar>0</bar>
    </row>
    <row>
        <bar></bar>
    </row>
</root>

I am importing in into the database like so:

insert into [Foo]
    ([bar])
select
    ds.[bar]
from openxml(@xmlHandle, 'root/row', 2)
with ([bar] int) ds

The problem is that OPENXML converts empty fields with the int data type to zero, so this gets inserted into my table:

bar
----
123
0
0

What I want inserted into my table is:

bar
----
123
0
NULL

How do I get the OPENXML function to treat empty fields as NULL and not convert it to zero by default?

Cannula

Answer 1

Since no one has any ideas, here is how I am "solving" it, though it seems like a hack to me:

insert into [Foo]
    ([bar])
select
    isnull(ds.[bar], '') when '' then null else CAST(ds.[bar] as int) end
from openxml(@xmlHandle, 'root/row', 2)
with ([bar] nvarchar(20)) ds

Cannula

Hi Kevin, Did you face any issue with the NULLIF operator as mentioned in my answer above? The obvious advantage of the NULLIF operator is the increased readability and avoidance of the WHEN... THEN clauses. – Proletariat

FYI: If you provide an empty node then you get the conversion to a default value. If you remove the empty node you get a NULL – Convexoconcave

Answer 2

Just faced a similar problem and solved it with the NULLIF function in SQL.

NULLIF on MSDN

I am sure you would have overlooked it as well :)

insert into [Foo]
    ([bar])
select
    NULLIF(ds.[bar], '')
from openxml(@xmlHandle, 'root/row', 2)
with ([bar] nvarchar(20)) ds

Gets rid of the clutter created by the CASE... END statements.

Hope it helps!

Proletariat

But if empty string is a proper value, it will be converted to null, wrongly – Claudio

@PmDuda - the answer was to suggest NULLIF as a viable solution, which it is for the question asked above. Its usage is dependent on the comparison one wants to make in the arguments. – Proletariat

Answer 3

Not sure how 'highly simplified' your xml is, but if you have an attribute that specifies null, you can also do something similar to the answer here; basically:

<root>
<row>
    <bar>123</bar>
</row>
<row>
    <bar>0</bar>
</row>
<row>
    <bar nil="true"></bar>
</row>

select ds.bar
from openxml(@xmlHandle, 'root/row', 2) with (
  [bar] nvarchar(20) 'bar[not(@nil = "true")]'
) ds

This scenario has worked for me

Vocabulary

Interesting solution, but of course this only works if you can modify the XML input. – Cannula

True- in my case I was producing it myself in another app layer – Vocabulary

Brilliant answer, mate! this is useful also many years later! :) – Niggerhead

Answer 4

I originally used NULLIF as already suggested, but I found another option in the WITH schema:

SELECT bar
FROM openxml(@xmlHandle, 'root/row', 2)
WITH (
  [bar] nvarchar(20) 'bar[string-length(.)>0]'
)

I'm looking at hundreds of columns and thousands of rows, so a decent size but not huge. I've found performance to be basically the same with this or NULLIF, but I like keeping the definition in the WITH clause so the SELECT can stay clean (because I tend to have plenty of other stuff happening there).

YMMV

Snake

Brilliant! I was looking for this thank you! – Intransitive

Answer 5

If you omit the Bar element entirely, you will get your desired result.

<root>
    <row>
        <bar>123</bar>
    </row>
    <row>
        <bar>0</bar>
     </row>
     <row>
     </row>
</root>

and then

select
    ds.[bar]
from openxml(@xmlHandle, 'root/row', 2)
with ([bar] int) ds

results in

bar
----
123
0
NULL

Reddish