How to insert xml data into table in sql server 2005

Question

my table structure is

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

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

the xml is

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

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.

thanks

Answer 1

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>

Assuming the following table:

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

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

Which you can then execute with:

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

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