我正在尝试将数据插入到表中,而这样做是我得到的
错误
Cannot find column name or user define function “Tbl.Col.value” or aggregate Tbl.Col.value or the name is ambiguous
这是我的存储过程：
ALTER PROCEDURE Ecal_InsertVerniercal_Sp
@exml XML
AS
BEGIN
INSERT INTO Vernier_Gauge_Calibration (Comment,Report_Number,Acceptance_Status,Calibration_Date,Approved_By)
SELECT
Tbl.Col.value('@Reamrk','varchar(200)') AS Reamrk,Tbl.Col.value('@ReportNo','nvarchar(255)') AS ReportNo,Tbl.Col.value('@Status','varchar(MAX)') AS Status,Tbl.Col.value('@CalDate','datetime') AS CalDate,Tbl.Col.value('@CalBy','datetime') AS CalBy
FROM
@exml.nodes('/CalibrationData/CalInfo') AS Tbl (Col)
RETURN;
END
GO
这是我的XML,它是在jQuery中动态生成的.
<calibrationdata>
<extreading>
<er>
</er>
</extreading>
<internalreading>
<ir>
</ir>
</internalreading>
<masterdata>
<mb1>110118</mb1>
<desc1>Vernier Caliper-Height-0-to-600</desc1>
<duedt1>02/06/2016</duedt1>
<rmk1>TL-14/VER-0154</rmk1>
<mb2>
</mb2>
<desc2>
</desc2>
<duedt2>
</duedt2>
<rmk2>
</rmk2>
<mb3>
</mb3>
<desc3>
</desc3>
<duedt3>
</duedt3>
<rmk3>
</rmk3>
<mb4>
</mb4>
<desc4>
</desc4>
<duedt4>
</duedt4>
<rmk4>
</rmk4>
<mb5>
</mb5>
<desc5>
</desc5>
<duedt5>
</duedt5>
<rmk5>
</rmk5>
<mb6>
</mb6>
<desc6>
</desc6>
<duedt6>
</duedt6>
<rmk16>
</rmk16>
</masterdata>
<calinfo>
<reamrk>fdg</reamrk>
<reportno>ALIL-2015-12-104-5</reportno>
<status1>Accepted</status1>
<caldate>31/12/2015</caldate>
<calby>Alok Sahu</calby>
</calinfo>
<visualpara>
<vp1>Clamp,Lock,Fine_Adjustment,Rusty,Damage,Dent_Marks,</vp1>
<vp2>External_Jaws,Shims,Jaw_Movement,</vp2>
</visualpara>
</calibrationdata>
解决方法
DECLARE @MyXML XML
SET @MyXML = '<calibrationdata>
<extreading>
<er></er>
</extreading>
<internalreading>
<ir></ir>
</internalreading>
<masterdata>
<mb1>110118</mb1>
<desc1>Vernier Caliper-Height-0-to-600</desc1>
<duedt1>02/06/2016</duedt1>
<rmk1>TL-14/VER-0154</rmk1>
<mb2></mb2>
<desc2></desc2>
<duedt2></duedt2>
<rmk2></rmk2>
<mb3></mb3>
<desc3></desc3>
<duedt3></duedt3>
<rmk3></rmk3>
<mb4></mb4>
<desc4></desc4>
<duedt4></duedt4>
<rmk4></rmk4>
<mb5></mb5>
<desc5></desc5>
<duedt5></duedt5>
<rmk5></rmk5>
<mb6></mb6>
<desc6></desc6>
<duedt6></duedt6>
<rmk16></rmk16>
</masterdata>
<calinfo>
<reamrk>fdg</reamrk>
<reportno>ALIL-2015-12-104-5</reportno>
<status1>Accepted</status1>
<caldate>31/12/2015</caldate>
<calby>Alok Sahu</calby>
</calinfo>
<visualpara>
<vp1>Clamp,</vp2>
</visualpara>
</calibrationdata>'
SELECT
Tbl.col.value('reamrk[1]',Tbl.col.value('reportno[1]',Tbl.col.value('status1[1]',convert(datetime,(Tbl.col.value('caldate[1]','varchar(50)')),103) AS CalDate,Tbl.col.value('calby[1]','varchar(100)') AS CalBy
FROM @MyXML.nodes('calibrationdata/calinfo') AS Tbl (col)
更改Tbl.col.value(‘caldate [1]’,’varchar(50)’)AS CalDate,
转换(datetime,(Tbl.col.value(‘caldate [1]’,’varchar(50)’)),103)AS CalDate,
