Read the Values from xml and Insert in to table

XML is a Technology , In which we can transfer a data from one system to another .

This Post will explain you about , fetch the values from xml element and insert in to the table in sql server.

Input Xml :

<Employees>
  <Employee>
    <Id>18</Id>
    <FirstName>fname1</FirstName>
    <LastName>lname1</LastName>
    <Company>rac</Company>
  </Employee>
  <Employee>
    <Id>15</Id>
    <FirstName>fname2</FirstName>
    <LastName>lname2</LastName>
    <Company>rac</Company>
  </Employee>
  <Employee>
    <Id>67</Id>
    <FirstName>fname3</FirstName>
    <LastName>lname3</LastName>
    <Company>dlf</Company>
  </Employee>
</Employees>

Step 1 : Create a Table

CREATE TABLE EMPLOYEE
(
   Id               int ,
   Firstname   varchar(20),
   Lastname   varchar(20),
   company    varchar(20)

)

Step 2 : Create a stored procedure

CREATE PROCEDURE Ins_Xml
(
@INPUT XML ,
@OUTPUT INT OUT
)
AS
BEGIN

SET NOCOUNT ON

BEGIN TRY
INSERT INTO  [Employee]
(
[Id],
[FirstName],
[LastName],
[company]
)

SELECT ISNULL(EMPS.EMP.value('Id[1]', 'int'),0) as 'ID',
EMPS.EMP.value('FirstName [1]', 'varchar(20)') as 'FirstName ',
EMPS.EMP.value('LastName[1]', 'varchar(20)') as 'LastName',
EMPS.EMP.value('Company[1]', 'varchar(50)') as 'Company'
FROM @INPUT.nodes('/Employees/Employee') as [EMPS](EMP)

SELECT @OUTPUT = 1

END TRY
BEGIN CATCH
/* Incase Of Error */
SELECT @OUTPUT = -1
END CATCH

SET NOCOUNT OFF


END

Step 3 : Exec the stored procedure

declare @inputxml xml
declare @result int

set @inputxml = N'<Employees>
  <Employee>
    <Id>18</Id>
    <FirstName>fname1</FirstName>
    <LastName>lname1</LastName>
    <Company>rac</Company>
  </Employee>
  <Employee>
    <Id>15</Id>
    <FirstName>fname2</FirstName>
    <LastName>lname2</LastName>
    <Company>rac</Company>
  </Employee>
  <Employee>
    <Id>67</Id>
    <FirstName>fname3</FirstName>
    <LastName>lname3</LastName>
    <Company>dlf</Company>
  </Employee>
</Employees>'

EXEC Ins_Xml @inputxml,@result out

select @result

On success insert 1 as select as output.