Its been very long since I have written any technical post. So here I am, with small example of how to insert data from XML into SQL. This is extremely helpful if you want to insert multiple records in one go into SQL, you just need to create the XML and pass it to SQL. SQL will fetch the data from the XML and save it into its tables you have mapped.

Just to demonstrate I am creating a sample table. Since it is sample I have not created any Primary Key on it.

CREATE TABLE [dbo].[Employees](
	[EmpId] [int] NOT NULL,
	[FirstName] [varchar](50) NULL,
	[Dept] [varchar](50) NULL
)

Once table has been created you are ready to insert data into it,

declare @TestDoc int
declare @TestDoc int
exec sp_xml_preparedocument @TestDoc output,
N'












'
insert into employees(empid, firstname, dept)
select empid, firstname, dept
from openxml(@TestDoc, N'/Root/Employees/Employee')
With (EmpId varchar(5) '@EmpID',
    FirstName varchar(10) '@FirstName',
    Dept varchar(10) '../@Dept')
exec sp_xml_removedocument @TestDoc

Alternatively, you can change your XML format like this,

declare @TestDoc int
exec sp_xml_preparedocument @TestDoc output,
N'

Ethans
Ajay


Rahul
Sneha


Nilesh
Matharu

'
select *
from openxml(@TestDoc, N'/Root/Employees/Employee')
With (EmpId varchar(5) '@EmpID',
    FirstName varchar(10) '.',
    Dept varchar(10) '../@Dept')
exec sp_xml_removedocument @TestDoc

If you want to fetch your existing data from SQL in XML format,
With values as attributes,

select * from employees
for xml auto

Output Data as Attributes

select * from employees
for xml auto, elements

Output Data as Elements

This is the traditional method for inserting multiple values in a table.

USE YourDBName
GO
INSERT INTO MyTableName (FirstCol, SecondCol)
VALUES (‘First’,1);
INSERT INTO MyTableName (FirstCol, SecondCol)
VALUES (‘Second’,2);
INSERT INTO MyTableName (FirstCol, SecondCol)
VALUES (‘Third’,3);
INSERT INTO MyTableName (FirstCol, SecondCol)
VALUES (‘Fourth’,4);
INSERT INTO MyTableName (FirstCol, SecondCol)
VALUES (‘Fifth’,5);
GO

However this cannot be used if you want to insert multiple values into the table via your code because in this case you’ll have to write the code to insert the value in the DB in a for loop which is not at all feasible.

So you can write the code to generate the insert statement like,

USE YourDB
GO
INSERT INTO MyTableName (FirstCol, SecondCol)
SELECT ‘First’ ,1
UNION ALL
SELECT ‘Second’ ,2
UNION ALL
SELECT ‘Third’ ,3
UNION ALL
SELECT ‘Fourth’ ,4
UNION ALL
SELECT ‘Fifth’ ,5
GO

The above code inserts multiple values into DB with a single SQL insert query. This is the best means to use in your code as you’ll just have to create this query in your for loop and the query will run only once.

Also you can use the following query syntax for multiple inserts in SQL 2008

USE YourDB
GO
INSERT INTO MyTableName (FirstCol, SecondCol)
VALUES (‘First’,1),
(‘Second‘,2),
(‘Third‘,3),
(‘Fourth‘,4),
(‘Fifth‘,5)

Please let me know if you have any better ideas than this one.
Njoy coding!!!