从xml中的字符串转换日期和/或时间时，转换失败

Question

我知道这种类型的问题已经问过了，但没有一个是完全满足我的要求，这里是我的简单代码插入数据到表从XML，但不断给出错误。我已经把日期改成了很多种格式，但是错误没有解决。

这里是存储过程

ALTER PROCEDURE  [dbo].[SP_PCMarkAttendance]
    @XMLData xml,   
    @Result Bit output
AS
Begin
 Create table #tempMarkAttendance(
  StSubAssID numeric(18,0) ,  
  Dat Date,
  IsP Bit ,
  IsL Bit   
 ); 
 insert into #tempMarkAttendance(StSubAssID,Dat,IsP,IsL)
    Select 
    attendance.query('StSubAssID').value('.', 'numeric(18,0)') as StSubAssID,
    attendance.query('Dat').value('.', 'Date') as Dat,
    attendance.query('IsP').value('.', 'Bit') as IsP,  
    attendance.query('IsL').value('.', 'Bit') as IsL    
    FROM
    @XMLData.nodes('/StudentList/Student')AS xmlData(attendance)  
    select * from #tempMarkAttendance
set @Result  = 1
End

，这里正在执行

DECLARE @return_value int,
        @Result bit
EXEC    @return_value = [dbo].[SP_PCMarkAttendance]     
        @XMLData = N'<?xml version="1.0" ?>
<StudentList>
<Student>
<StSubAssId>2</StSubAssId>
<Dat>
2014-01-01</Dat>
<IsP>False</IsP>
<IsL>False</IsL>
</Student>
</StudentList>',
        @Result = @Result OUTPUT
SELECT  @Result as N'@Result'
SELECT  'Return Value' = @return_value
GO

，错误是:

Msg 241, Level 16, State 1, Procedure SP_PCMarkAttendance, Line 13
Conversion failed when converting date and/or time from character string.

另一个错误是当我将stsubbassid类型更改为numeric(18,0)

Msg 8114, Level 16, State 5, Procedure SP_PCMarkAttendance, Line 12
Error converting data type nvarchar to numeric.

Answer 1

在源xml中，<Dat>元素的值有额外的换行符:

<Dat>
2014-01-01</Dat>

所以实际上它的值不是你期望的2014-01-01，而是char(13) + char(10) + '2014-01-01'。

因此，您可以从原始xml中删除该换行符，或者(如果由于某种原因不可能)—您可以将存储过程中的此元素值作为varchar，而不是date，然后去掉换行符并转换为日期。

cast(replace(
      replace(
              attendance.query('Dat').value('.', 'nvarchar(20)'),
              char(13), ''), 
      char(10), '') 
as date)  as Dat