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

I have the following (highly simplified) XML document that I 开发者_StackOverflow中文版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?

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

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