如何在sql server 2005中将xml数据插入表中 [英] How to insert xml data into table in sql server 2005

问题描述

我的表结构是

CREATE TABLE [dbo].[Emp](
    [ID] [int] NOT NULL,
    [EmpName] [varchar](50)  NOT NULL,
    [Sal] [int] NULL,
) 

在这个 emp 表中，我想从 xml 字符串中插入数据

in this emp table i want to insert data from a xml string

xml 是

<Record xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Emp>
<ID>3</ID><EmpName>Dibyendu</EmpName><Sal>3500</Sal></Emp></Record>

假设此 xml 存储在我的存储过程中的变量中，我只想以这样的方式插入此 xml，即在 EMP 表中，结果 ID 数据将插入 ID 列，EmpName 数据将插入 EmpName 列和Sal 数据将插入到 Sal 列中.

suppose this xml is stored in a variable in my store procedure and i just want to insert this xml in such a way that in EMP table as a result ID data will insert into ID column, EmpName data will insert into EmpName column and Sal data will insert into Sal column.

所以请告诉我如何在存储过程中编写代码.

so please tell me how to write the code in store procedure.

谢谢

推荐答案

假设 XML 示例如上:

Assuming XML sample as above:

<Record xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Emp>
        <ID>3</ID>
        <EmpName>Dibyendu</EmpName>
        <Sal>3500</Sal>
    </Emp>
</Record>

假设下表:

CREATE TABLE Employee
(
    [ID] [int] NOT NULL,  
    [EmpName] varchar(max) NOT NULL,   
    [Sal] [int] NULL
)

以下使用 xpaths 的存储过程应该可以解决问题

The following stored procedure, that uses xpaths, should do the trick

CREATE PROCEDURE AddEmployee
    @empXml xml
AS

INSERT INTO Employee
(
    ID,
    EmpName,
    Sal
)
VALUES
(
    @empXml.value('(/Record/Emp/ID)[1]', 'int'),
    @empXml.value('(/Record/Emp/EmpName)[1]', 'varchar(max)'),
    @empXml.value('(/Record/Emp/Sal)[1]', 'int')
)

然后你可以执行:

exec AddEmployee '<Record xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Emp><ID>3</ID><EmpName>Dibyendu</EmpName><Sal>3500</Sal></Emp></Record>' 

如果 Record XML 可能包含多个Emp"元素，您将需要进行一些重构.

You will need to do a little refactoring if the Record XML could potentially include multiple 'Emp' elements.

这篇关于如何在sql server 2005中将xml数据插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！