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

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

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!

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