Insert data from XML in SQL

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'<Root>
<Employees Dept="Marketing">
<Employee EmpID="1231" FirstName="Ethan"></Employee>
<Employee EmpID="1232" FirstName="Ashish"></Employee>
</Employees>
<Employees Dept="IT">
<Employee EmpID="1241" FirstName="Ajay"></Employee>
<Employee EmpID="1242" FirstName="Rahul"></Employee>
</Employees>
<Employees Dept="DBA">
<Employee EmpID="1251" FirstName="AJ"></Employee>
<Employee EmpID="1251" FirstName="Nilesh"></Employee>
</Employees>
</Root>'
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'<Root>
<Employees Dept="Marketing">
<Employee EmpID="1231">Ethans</Employee>
<Employee EmpID="1232">Ajay</Employee>
</Employees>
<Employees Dept="IT">
<Employee EmpID="1241">Rahul</Employee>
<Employee EmpID="1242">Sneha</Employee>
</Employees>
<Employees Dept="DBA">
<Employee EmpID="1251">Nilesh</Employee>
<Employee EmpID="1251">Matharu</Employee>
</Employees>
</Root>'
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

With values as elements,

select * from employees
for xml auto, elements

Output Data as Elements