使用SQL Server 2008中的xml和存储过程将数据插入到表中

我正在尝试将数据插入到表中,而这样做是我得到的
错误

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,

在这里你的问题解决了,如果解决你的问题,请投票

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!