Error converting datatype nvarchar to numeric for empty XML node in case of OPENXML sql

Question

I was getting an error

Error converting datatype nvarchar to numeric

when converting an empty node using OPENXML. I tried different ways of CAST and CONVERT methods but still was getting the same error then end up using the following way.

This is the SQL query:

 IF OBJECT_ID('tempdb..#TempData') IS NOT NULL   
     DROP TABLE #TempData   

 DECLARE @XML XML

 SET @XML = '<ArrayOfTempData>
                <TempData>
                    <StudentId>1418431</StudentId>
                    <Name>Bill</Name>
                    <DeptId>2130900</DeptId>
                    <Fees p3:nil="true" xmlns:p3="http://www.w3.org/2001/XMLSchema-instance" />
                </TempData>
                <TempData>
                    <StudentId>1418431</StudentId>
                    <Name>Samuel</Name>
                    <DeptId>2280971</DeptId>
                    <Fees>4628617.156</Fees>
                </TempData>
            </ArrayOfTempData>'

DECLARE @handle INT  
DECLARE @PrepareXmlStatus INT  

EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @XML  

SELECT 
    StudentId, Name, DeptId, Fees 
    -- nullif(Fees , '') as Fees,
    -- CASE WHEN Fees = '' THEN NULL ELSE CAST(Fees AS NUMERIC(13, 3)) END AS Fees,
    -- CASE WHEN ISNUMERIC(Fees) <> 1 THEN NULL ELSE CAST(Fees AS NUMERIC(13, 3)) END AS Fees,
    -- CONVERT(NUMERIC(13, 3), NULLIF(Fees, '')) AS Fees
INTO 
    #TempData 
FROM 
    OPENXML(@handle, '/ArrayOfTempData/TempData', 2)  
    WITH (StudentId INT,
          Name VARCHAR(50),
          DeptId INT,
          Fees NUMERIC(13, 3) 'Fees[.!=""]'
         )

SELECT * FROM #TempData

Commented lines are some other ways I tried then I used the SchemaDeclaration way.

Any better suggestions?

Answer 1

You can use nodes function to read the xml.

SELECT StudentId = n.value('(./StudentId)[1]', 'int'), 
       NAME = n.value('(./Name)[1]', 'Varchar(8000)'), 
       DeptId = n.value('(./DeptId)[1]', 'int'), 
       Fees = n.value('(./Fees)[1]', 'Varchar(8000)') 
FROM   @xml.nodes('/ArrayOfTempData/TempData') AS a(n)