Insert data into a table using xml and stored procedure in SQL Server 2008

Question

I am trying to insert data into a table by doing this. I get an error

Cannot find column name or user definition function "Tbl.Col.value" or fill Tbl.Col.value or name ambiguously

This is my stored procedure:

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 

Here is my XML, it is dynamically generated in 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> 

Answer 1

 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,,Lock,,Fine_Adjustment,,Rusty,,Damage,,Dent_Marks,,</vp1> <vp2>External_Jaws,,Shims,,Jaw_Movement,,</vp2> </visualpara> </calibrationdata>' SELECT Tbl.col.value('reamrk[1]', 'varchar(200)') AS Reamrk , Tbl.col.value('reportno[1]', 'nvarchar(255)') AS ReportNo, Tbl.col.value('status1[1]', 'varchar(MAX)') AS Status, 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) 

Modify Tbl.col.value ('caldate [1]', 'varchar (50)') AS CalDate, to convert (datetime, (Tbl.col.value ('caldate [1]', 'varchar (50)') ), 103) AS CalDate,

here your problem is solved, please vote if you solve your problem.