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

Question

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>

hi all i have added the xml in my question as I don't know how to share it here plz check it — Trilochan Sahu
– Trilochan Sahu, Commented Dec 23, 2015 at 6:40

Ryan M · Accepted Answer · 2021-04-27 03:35:00Z

4

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,

edited Apr 27, 2021 at 3:35

Ryan M♦

20.6k35 gold badges75 silver badges85 bronze badges

answered Dec 22, 2015 at 18:49

Sudhir Panda

7841 gold badge7 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

Trilochan Sahu Over a year ago

Thanks for your help . I tried it but its still not working and 'Tbl.col.value' show 'cannot find column name or user define function "Tbl.Col.value" or aggregate Tbl.Col.value or the name is ambiguous' error.

Sudhir Panda Over a year ago

share sample of your xml data so that i can better help

Sudhir Panda Over a year ago

the xml you genereted is not in well format, first check and generate a well formatted xml then try

Sudhir Panda Over a year ago

@@ Trilochan Sahu check my answer , I m sure this will resolve the issue

Trilochan Sahu Over a year ago

Thanks for ur help but following error are coming in sql server:-Msg 515, Level 16, State 2, Procedure Ecal_InsertVerniercal_Sp, Line 5 Cannot insert the value NULL into column 'Calibration_Date', table 'New_DB.dbo.Vernier_Gauge_Calibration'; column does not allow nulls. INSERT fails. The statement has been terminated.

|

Collectives™ on Stack Overflow

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

1 Answer 1

16 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

16 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related