Inserting Xml data into SQl table Using Stored Procedure in SQL 2008

Inserting Xml data into SQl table Using Stored Procedure in SQL 2008

Below is the Scripts of Inserting an xml Data into a table.Tested and verified in sql 2008 R2.

First we will create a table with some Records

--Table Script with Few Records.

CREATE TABLE [dbo].[Employee1](

[Empid] [int] IDENTITY(1,1) NOT NULL,

[EmpName] [nvarchar](50) NULL,

[EmpSalary] [float] NULL

) ON [PRIMARY]

GO

SET IDENTITY_INSERT [dbo].[Employee1] ON

INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (1, N'sumit', 5000)

INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (2, N'sumit', 6000)

INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'amit', 8000)

INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (4, N'amit', 9000)

INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'vijay', 10000)

INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (6, N'vijay', 5000)

INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (7, N'suresh', 5000)

INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (8, N'suresh', 5000)

INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (9, N'Mukesh', 130)

SET IDENTITY_INSERT [dbo].[Employee1] OFF

Now we will generate xml of these records.

Run below query to Generate xml of above table Employee1 Records.

--Test will be the Root Node of XML you can give any Name

select * from Employee1   for Xml AUTO,elements,root('Test')

Output--XML

<Test>

  <Employee1>

    <Empid>1</Empid>

    <EmpName>sumit</EmpName>

    <EmpSalary>5.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>2</Empid>

    <EmpName>sumit</EmpName>

    <EmpSalary>6.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>3</Empid>

    <EmpName>amit</EmpName>

    <EmpSalary>8.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>4</Empid>

    <EmpName>amit</EmpName>

    <EmpSalary>9.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>5</Empid>

    <EmpName>vijay</EmpName>

    <EmpSalary>1.000000000000000e+004</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>6</Empid>

    <EmpName>vijay</EmpName>

    <EmpSalary>5.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>7</Empid>

    <EmpName>suresh</EmpName>

    <EmpSalary>5.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>8</Empid>

    <EmpName>suresh</EmpName>

    <EmpSalary>5.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>9</Empid>

    <EmpName>Mukesh</EmpName>

    <EmpSalary>1.300000000000000e+002</EmpSalary>

  </Employee1>

</Test>

Now we will Truncate data of our Employee1 table to insert Records back using xml data

truncate table employee;

--Below is the procedure that will take xml as input string and will insert the Records back into Employee1 table after trucating the table.

--Procedure to insert xml data in sql table

Create PROCEDURE [dbo].[Proc_Insertxml] (

 @xmlstring XML 

)

AS

BEGIN

INSERT INTO  [Employee1](

EmpName ,

EmpSalary 

)

SELECT

[Table].[Column].value('EmpName [1]', 'varchar(50)') as ' EmpName ',

[Table].[Column].value(' EmpSalary [1]', 'varchar(100)') as ' EmpSalary'

 FROM @xmlstring.nodes('/ Test / Employee1') as [Table]([Column])

END

--Execute Procedure passing above xml to put all records back after truncating the table

exec [Proc_Insertxml] '<Test>

  <Employee1>

    <Empid>1</Empid>

    <EmpName>sumit</EmpName>

    <EmpSalary>5.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>2</Empid>

    <EmpName>sumit</EmpName>

    <EmpSalary>6.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>3</Empid>

    <EmpName>amit</EmpName>

    <EmpSalary>8.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>4</Empid>

    <EmpName>amit</EmpName>

    <EmpSalary>9.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>5</Empid>

    <EmpName>vijay</EmpName>

    <EmpSalary>1.000000000000000e+004</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>6</Empid>

    <EmpName>vijay</EmpName>

    <EmpSalary>5.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>7</Empid>

    <EmpName>suresh</EmpName>

    <EmpSalary>5.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>8</Empid>

    <EmpName>suresh</EmpName>

    <EmpSalary>5.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>9</Empid>

    <EmpName>Mukesh</EmpName>

    <EmpSalary>1.300000000000000e+002</EmpSalary>

  </Employee1>

</Test>'

--verify the Records

 Select * from Employee1

Output:

 Empid EmpName EmpSalary

1 sumit          5000

2 sumit         6000

3 amit           8000

4 amit           9000

5 vijay          10000

6 vijay          5000

7 suresh 5000

8 suresh       5000

9 Mukesh    130

Note: IN case your xml is very large its better to put the xml into a variable with xml data type and execute the procedure passing that parameter name to the Procedure like below

Declare @Textxml xml

set @Textxml='<Test>your xml.....<Test>'

  exec [Proc_Insertxml] Textxml