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

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>

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...

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

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

An alternate solution would be to create a secondary field in your target table that accepts a string value. After populating the table with the XML data, execute a statement to update your integer field, setting it to NULL when the string field is blank.

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 respective Days values of 0 and Blank. Zero needs to be preserved, and blank needs to be saved as NULL. Here was my solution for achieving 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 (i.e. Days="") from zero to NULL
-- to accurately reflect the supplied values.
UPDATE  #Deadline
SET     DeadlineDays = NULL
WHERE   (DaysString = '')

SELECT * FROM #Deadline

To recap what's happening above. The code grabs the Days attribute twice. Put one in an INT column and one in a VARCHAR column. After the XML INSERT statement occurs, run an UPDATE statement to convert the targeted INT field to NULL when it's related VARCHAR column is blank.

This approach may offer better performance than XQuery. I'll leave that to someone else on this thread.