在OPENXML sql情况下，将空XML节点的数据类型nvarchar转换为数值时出错

Question

我搞错了

错误将数据类型nvarchar转换为数字

当使用OPENXML转换空节点时。我尝试了不同的CAST方法和CONVERT方法，但仍然得到相同的错误，然后使用以下方法结束。

这是SQL查询：

 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

注释行是我尝试过的其他一些方法，然后我使用了SchemaDeclaration方法。

有更好的建议吗？

Answer 1

您可以使用nodes函数读取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)