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

我试图插入数据到表中，而这样做是我得到一个错误

找不到列名称或用户定义函数“Tbl.Col.value”或集合Tbl.Col.value或名称不明确

这是我的存储过程：

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,,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)

更改Tbl.col.value（'caldate [1]'，'varchar（50）'）AS CalDate， 转换（datetime，（Tbl.col.value（'caldate [1]'，'varchar（50）'） ），103）AS CalDate，

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

链接地址: http://www.djcxy.com/p/89369.html

上一篇: Insert data in to table using xml and stored procedure in SQL Server 2008

下一篇: Start CSS3 transition after user views page?