Saving multiple records in SQL database through XML file

XML has been widely accepted as a platform independent mechanism for transferring the data between loosely coupled systems.For example, if documents are represented in XML, it is very easy to write a DOM or XPATH query to extract the contents of  each section,element and the value.The most common practice is to store preferences of the user record in the database.
If a registered user of a website configures his porfile.Like Theme,Background Images,Accessibilty settings.

Using below code snippet,you can pass a XML document to the stored procedure and save it into the database.This approch greatly improves the performance over inserting single record at a time.It avoids the over head of opening and closing the connection multiple times.

Sample XML document.

<employees>

<employee>

<id>1</id>

<firstname>shree</firstname>

<lastname>gaanji</lastname>

</employee>

<employee>

<id>2</id>

<firstname>manjunath</firstname>

<lastname>gaanji</lastname>

</employee>

<employee>

<id>3</id>

<firstname>sunita</firstname>

<lastname>gaanji</lastname>

</employee>

</employees>

And the below SP is used for parsing the sample XML document and insert it into the database.

CREATE  PROCEDURE [dbo].[insertemployees]
(
  @employees xml
)
as
    BEGIN
        INSERT INTO dbo.employees(id, firstname, lastname)
        SELECT
                      e.value('(id)[1]', 'int') as 'ID',
                      e.value('(firstname)[1]', 'Varchar(50)') as 'firstname',
                      e.value('(lastname)[1]', 'varchar(50)') as 'lastname'
        FROM @employees.nodes('/employees/employee') as emp(e)
    END