Insert data in to table using xml and stored procedure in SQL Server 2008
I am trying to insert data in to table while doing this is I am getting an error
Cannot find column name or user define function "Tbl.Col.value" or aggregate Tbl.Col.value or the name is ambiguous
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 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>
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)
Change 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 solved, please vote up if resolve your issue
链接地址: http://www.djcxy.com/p/89370.html