CODEWITHSUNDEEP
sql-serverxmlt-sqlnullopenxml
Kevin Albrecht
Kevin Albrecht

Reputation: 7014

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

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?

Upvotes: 6

Views: 9665

Answers (5)

GwennethAthena
GwennethAthena

Reputation: 1

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

Upvotes: 0

InkIcthus
InkIcthus

Reputation: 11

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

Upvotes: 1

Vaibhav
Vaibhav

Reputation: 1166

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!

Upvotes: 6

Factor Mystic
Factor Mystic

Reputation: 26790

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

Upvotes: 4

Kevin Albrecht
Kevin Albrecht

Reputation: 7014

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

Upvotes: 7

Related Questions