Treat empty elements as zeros in OPENXML SQL Server function

Question

I have the following (very simplified) XML document that I read in my database using the OPENXML function:

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

I import into the database like this:

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 int data type to zero, so it is inserted into my table:

bar
----
123
0
0

What I want to add to my table:

bar
----
123
0
NULL

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

Answer 1

Since no one has any ideas, here's how I “solve” them, although it seems to me that this is a hack:

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

Answer 2

NULLIF SQL.

NULLIF MSDN

, :)

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

, CASE... END.

, !

Answer 3

, " " xml, , null, - ; :

<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