SQL Server insert with XML parameter is an empty string that does not convert to null for numeric

Question

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

Is there a way to get SQL to convert an empty string to zero for these number 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 that helps, try XML. The error is caused if I do not comment on field3 in the code above or did not provide the value in field 3 below.

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

Answer 1

If you are using 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 field3 value to a string if it is NULL, instead use the value 0.00 as the DECIMAL value.

I don't know if there is a way with the OPENXML approach to do something like this ...

Answer 2

One way to do this is 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 try this, but marc_s set me on the right path, so I will give him credit for an answer.

I tried XQuery as Mark suggested, but the performance was terrible for the number of records I dealt with. But I used a similar technique in my source 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 

Answer 4

An alternative solution would be to create a secondary field in your target table that takes a string value. After filling the table with XML data, run the statement to update the whole field, setting it to NULL when the row field is empty.

For example, here is my XML string.

 '<xml> <d MetricID="1" Days="15" Sort="1" /> <d MetricID="2" Days="45" Sort="2" /> <d MetricID="3" Days="29" Sort="3" /> <d MetricID="4" Days="119" Sort="4" /> <d MetricID="5" Days="59" Sort="5" /> <d MetricID="6" Days="179" Sort="6" /> <d MetricID="7" Days="0" Sort="7" /> <d MetricID="8" Days="" Sort="8" /> <d MetricID="9" Days="60" Sort="9" /> </xml>' 

Nodes 7 and 8 have the corresponding values ​​of days 0 and Blank. Zero must be saved, and empty must be saved as NULL. Here is my solution to achieve this.

 DECLARE @idoc INT EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlDeadline CREATE TABLE #Deadline ( TimeframeMetricID INT , DeadlineDays INT NULL , DaysString VARCHAR(5) -- Hack for identifying DeadlineDays that were blank (null). , SortOrder INT ) INSERT INTO #Deadline (TimeframeMetricID , DeadlineDays , DaysString , SortOrder ) SELECT MetricID , Days , DaysString , Sort FROM OPENXML ( @iDoc -- Handle to the XML doc. , 'xml/d' -- Map to the node that contains the attributes. , 1 -- Attribute centric mapping (1), instead of Element centric mapping (2). ) WITH ( MetricID INT '@MetricID' -- Select attribute. When selecting element remove @ sign. , Days INT '@Days' , DaysString VARCHAR(5) '@Days' -- Hack for identifying DeadlineDays that were blank (null). , Sort INT '@Sort' ) a -- Release the XML parser EXEC sp_xml_removedocument @idoc -- Now convert Days that arrived as blank (ie Days="") from zero to NULL -- to accurately reflect the supplied values. UPDATE #Deadline SET DeadlineDays = NULL WHERE (DaysString = '') SELECT * FROM #Deadline 

Recall what happens above. The code captures the Days attribute twice. Put one in the INT column and one in the VARCHAR column. After executing the XML INSERT statement, run the UPDATE statement to convert the target INT field to NULL when it is associated with the VARCHAR column.

This approach can provide better performance than XQuery. I will leave it to someone else in this thread.