SQL Server insert with XML parameter - empty string not converting to null for numeric
- by Mayo
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>
