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>