Jeff Puckett II Jeff Puckett II - 3 months ago 23
SQL Question

Empty XML node causes Error converting data type varchar to numeric

How do I simply insert

NULL
if the node is empty or absent?

CREATE TABLE myxml (
"hours" DECIMAL(11,2)
);

DECLARE @xml XML =
'<?xml version="1.0" encoding="UTF-8"?>
<Data>
<Employee>
<NUMHOURS>0.5</NUMHOURS>
</Employee>
<Employee/>
<Employee>
<NUMHOURS>5</NUMHOURS>
</Employee>
<Employee>
<NUMHOURS/>
</Employee>
</Data>';

INSERT INTO myxml ("hours")
SELECT
t.c.value('NUMHOURS[1]','DECIMAL(11,2)' )
FROM @xml.nodes('/Data/Employee') t(c)


The empty node
<NUMHOURS/>
causes:


Error converting data type nvarchar to numeric.


I have tried:

NULLIF(t.c.value('NUMHOURS[1]','DECIMAL(11,2)' ),'')


but that seems to get processed after the fact resulting in the same error.

Answer

You could provide a default value for invalid numbers:

select  case
            when ISNUMERIC(t.c.value('NUMHOURS[1]', 'nvarchar(100)')) <> 1 then NULL
            else t.c.value('NUMHOURS[1]', 'decimal(11,2)')
        end
    from @xml.nodes('/Data/Employee') t(c)