SQL Server insert with XML parameter - empty string not converting to null for numeric

Question

I have a stored procedure that takes an XML parameter and inserts the "Entity" nodes as records into a table. This works fine unless one of the numeric fields has a value of empty string in the XML. Then it throws an "error converting data type nvarchar to numeric" error.

Is there a way for me to tell SQL to convert empty string to null for those numeric fields in the code below?

-- @importData XML <- stored procedure param
DECLARE @l_index INT

EXECUTE sp_xml_preparedocument @l_index OUTPUT, @importData
INSERT INTO dbo.myTable
(
     [field1]
    ,[field2]
    ,[field3]
)
SELECT
     [field1]
    ,[field2]
    ,[field3]
FROM OPENXML(@l_index, 'Entities/Entity', 1)
    WITH 
    (
         field1 int 'field1'
        ,field2 varchar(40) 'field2'
        ,field3 decimal(15, 2) 'field3'
    )
EXECUTE sp_xml_removedocument @l_index

EDIT: And if it helps, sample XML. Error is thrown unless I comment out field3 in the code above or provide a value in field3 below.

<?xml version="1.0" encoding="utf-16"?>
<Entities>
  <Entity>
    <field1>2435</field1>
    <field2>843257-3242</field2>
    <field3 />
  </Entity>
</Entities>

Answer 1

If you would use SQL Server XQuery, you can do something like this:

SELECT
   nodes.entity.value('(field1)[1]', 'int') 'Field1',
   nodes.entity.value('(field2)[1]', 'varchar(50)') 'Field 2',
   CAST(ISNULL(nodes.entity.value('(field3)[1]', 'varchar(50)'), '0.00') 
        AS DECIMAL(15,2)) 'Field 3'
FROM
   @importData.nodes('/Entities/Entity') AS nodes(entity)

Basically, convert the value of field3 to a string, if it's NULL, use 0.00 as the DECIMAL value instead.

I don't know if there's any way with the OPENXML approach to do something similar...

Answer 2

One way to do it would be to use the /text() function:

DECLARE @importData XML;
DECLARE @myTable TABLE
(
  field1 INT,
  field2 VARCHAR(40),
  field3 DECIMAL(15,2)
);

SET @importData =
'<?xml version="1.0"?>
<Entities>
  <Entity>
  <field1>2435</field1>
  <field2>843257-3242</field2>
  <field3 />
  </Entity>
</Entities>
';

INSERT INTO @myTable
(
  [field1],
  [field2],
  [field3]
)
SELECT
  t.tmp.value('(./field1)[1]', 'INT') AS field1,
  t.tmp.value('(./field2)[1]', 'VARCHAR(40)') AS field2,
  t.tmp.value('(./field3/text())[1]', 'DECIMAL(15,2)') AS field3
FROM
  @importData.nodes('//Entity') AS t(tmp);

SELECT * FROM @myTable;

Will result in:

field1 | field2      | field3
-----------------------------
2435   | 843257-3242 | NULL

Answer 3

NOTE: I would not have tried this but marc_s put me on the right track so I will give him credit for the answer.

I tried XQuery as suggested by Marc, but performance was miserable for the quantity of records I was dealing with. But I used a similar technique in my original code...

EXECUTE sp_xml_preparedocument @l_index OUTPUT, @importData
INSERT INTO dbo.myTable
(
     [field1]
    ,[field2]
    ,CASE
       WHEN [field3] = '' THEN NULL
       ELSE CAST([wl_llr] AS DECIMAL(15,2)) -- CHANGED TO CASE
     END
)
SELECT
     [field1]
    ,[field2]
    ,[field3]
FROM OPENXML(@l_index, 'Entities/Entity', 1)
    WITH 
    (
         field1 int 'field1'
        ,field2 varchar(40) 'field2'
        ,field3 varchar(50) 'field3' -- CHANGED TO VARCHAR
    )
EXECUTE sp_xml_removedocument @l_index