How to insert into table in XML which has more than table?

How to insert xml data into table in sql server 2005

• 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:

  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.