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

score 9 · Accepted Answer · answered Jan 21 '11 at 13:22

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.

If you are looking to do a similar things with multiple elements, this related question should have the answer: http://stackoverflow.com/questions/1554792/iterating-xml-data-in-sql-server-2005 — infojolt, May 23 '12 at 13:34

Adriaan Stander · Answer 2 · 2011-01-21T13:15:49.990

Have a look at something like

DECLARE @Xml XML

DECLARE @Emp TABLE(
        [ID] [int] NOT NULL, 
        [EmpName] varchar NOT NULL, 
        [Sal] [int] NULL
)

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

SELECT  @Xml.value('(/Record/Emp/ID)[1]', 'int' ) ID,
        @Xml.value('(/Record/Emp/EmpName)[1]', 'VARCHAR(MAX)' ) EmpName,
        @Xml.value('(/Record/Emp/Sal)[1]', 'int' ) Sal

And if you were to have multiple rows you can try something like

DECLARE @Xml XML

SELECT @Xml = '<Record><Emp><ID>3</ID><EmpName>Dibyendu</EmpName><Sal>3500</Sal></Emp><Emp><ID>4</ID><EmpName>TADA</EmpName><Sal>5</Sal></Emp></Record>'

SELECT  A.B.value('(ID)[1]', 'int' ) ID,
        A.B.value('(EmpName)[1]', 'VARCHAR(MAX)' ) EmpName,
        A.B.value('(Sal)[1]', 'int' ) Sal
FROM    @Xml.nodes('/Record/Emp') A(B)

How to insert xml data into table in sql server 2005

2 Answers2

Linked