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

Question

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?

Kevin Albrecht · Accepted Answer · 2010-05-21 16:25:40Z

7

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

answered May 21, 2010 at 16:25

Kevin Albrecht

7,0247 gold badges47 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vaibhav Over a year ago

Hi Kevin, Did you face any issue with the NULLIF operator as mentioned in my answer above? The obvious advantage of the NULLIF operator is the increased readability and avoidance of the WHEN... THEN clauses.

Jay Over a year ago

FYI: If you provide an empty node then you get the conversion to a default value. If you remove the empty node you get a NULL

Vaibhav · Accepted Answer · 2011-11-21 10:08:20Z

6

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!

answered Nov 21, 2011 at 10:08

Vaibhav

1,1662 gold badges13 silver badges27 bronze badges

2 Comments

Pm Duda Over a year ago

But if empty string is a proper value, it will be converted to null, wrongly

Vaibhav Over a year ago

@PmDuda - the answer was to suggest NULLIF as a viable solution, which it is for the question asked above. Its usage is dependent on the comparison one wants to make in the arguments.

Factor Mystic · Accepted Answer · 2011-02-17 19:57:00Z

4

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

answered Feb 17, 2011 at 19:57

Factor Mystic

26.9k16 gold badges82 silver badges96 bronze badges

3 Comments

Kevin Albrecht Over a year ago

Interesting solution, but of course this only works if you can modify the XML input.

Factor Mystic Over a year ago

True- in my case I was producing it myself in another app layer

Marian Over a year ago

Brilliant answer, mate! this is useful also many years later! :)

InkIcthus · Accepted Answer · 2020-10-23 21:01:51Z

1

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

answered Oct 23, 2020 at 21:01

InkIcthus

112 bronze badges

Comments

GwennethAthena · Accepted Answer · 2024-06-20 09:35:21Z

0

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

answered Jun 20, 2024 at 9:35

GwennethAthena

1

Collectives™ on Stack Overflow

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

5 Answers 5

2 Comments

2 Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

2 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related