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