Let’s take a simple table (Employee) to perform this task.

Now for performing the CRUD operation on this table we need to pass input data in XML format as given below in sample code. Note that we already have one record in the table (i.e. Bhavin Patel) & we are intended to add new employee (i.e. Carlos Gozlez); initially we don’t have its EmployeeId, so we can pass 0 (zero) as EmployeeId in the XML string. For updating just need to pass the updated XML data with EmployeeId. For deletion just need to remove employee’s XML string from the below given sample code.

Sample data:

<ROOT>

<Employee>

   <EmployeeId>2</EmployeeId>

   <FirstName>Bhavin</FirstName>

   <LastName>Patel</LastName>

   <Designation>Sr. Software Engineer</Designation>

</Employee>

<Employee>  

   <EmployeeId>0</EmployeeId>

   <FirstName>Carlos</FirstName>

   <LastName>Gonzlez</LastName>

   <Designation>Team Leader</Designation>

</Employee>

</ROOT>

Now let’s see the actual code which performs the CRUD operation in one go

DECLARE @docHandle INT,

@employeeXml VARCHAR(MAX),

@LastModifiedBy VARCHAR(MAX),

@LastModifiedOn SMALLDATETIME

SET @LastModifiedBy = 'Bhavin'

SET @LastModifiedOn = '03/16/2011 3:30 PM'

SET @employeeXml = N'

<ROOT>

<Employee>

   <EmployeeId>2</EmployeeId>

   <FirstName>Bhavin</FirstName>

   <LastName>Patel</LastName>

   <Designation>Sr. Software Engineer</Designation>

</Employee>

<Employee>  

   <EmployeeId>3</EmployeeId>

   <FirstName>Carlos</FirstName>

   <LastName>Gonzlez</LastName>

   <Designation>Team Leader</Designation>

</Employee>

</ROOT>'

-- Begin the transaction for CRUD operations

BEGIN TRANSACTION

-- Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @docHandle OUTPUT, @employeeXml

-- Insert new Employee if Not Exist

INSERT INTO Employee(FirstName,LastName,Designation,Deleted,LastModifiedBy,LastModifiedOn)

SELECT DISTINCT A.FirstName, A.LastName, A.Designation, 0, @LastModifiedBy, @LastModifiedOn

FROM OPENXML(@docHandle, 'ROOT/Employee', 2)

WITH (EmployeeId  INT, FirstName VARCHAR(50), LastName VARCHAR(50), Designation VARCHAR(50)) AS A

LEFT OUTER JOIN Employee B ON A.EmployeeId=B.EmployeeId

WHERE B.EmployeeId IS NULL AND ISNULL(B.Deleted,0)=0

-- Update Existing Employee Data

UPDATE Employee

SET FirstName=A.FirstName, LastName=A.LastName, Designation=A.Designation,

Employee.LastModifiedBy=@LastModifiedBy, Employee.LastModifiedOn=@LastModifiedOn

FROM OPENXML(@docHandle, 'ROOT/Employee', 2)

WITH (EmployeeId  INT, FirstName VARCHAR(50), LastName VARCHAR(50), Designation VARCHAR(50)) AS A

RIGHT OUTER JOIN Employee B ON A.EmployeeId=B.EmployeeId

WHERE A.EmployeeId IS NOT NULL AND ISNULL(B.Deleted,0)=0

-- Delete Employee Data

UPDATE Employee

SET Employee.Deleted=1, Employee.LastModifiedBy=@LastModifiedBy, Employee.LastModifiedOn=@LastModifiedOn

SELECT *

FROM OPENXML(@docHandle, 'ROOT/Employee', 2)

WITH (EmployeeId  INT, FirstName VARCHAR(50), LastName VARCHAR(50), Designation VARCHAR(50)) AS A

RIGHT OUTER JOIN Employee B ON A.EmployeeId=B.EmployeeId

WHERE A.EmployeeId IS NULL AND ISNULL(B.Deleted,0)=0

-- Removes the internal representation of the XML document specified by the document handle and invalidates the document handle                 

EXEC sp_xml_removedocument @docHandle

IF(@@ERROR=0)

            COMMIT TRANSACTION

      ELSE

            ROLLBACK TRANSACTION

Let’s see some more on OPENXML

Syntax:

OPENXML(idoc int [in],rowpattern nvarchar[in],[flags byte[in]])

[WITH (SchemaDeclaration | TableName)]

-- idoc is handler to the xmldoc which contains raw converted xml data in tabular form.

-- rowpattern (/ROOT/Employee) identifies the <Employee> nodes to process.

-- flags (1 = attribute centric, 2 = element centric, 3 = both element & attribute centric)

-- (in case of flag = 3, the attribute-centric mapping is applied first, and then element-centric mapping is applied)

-- SchemaDeclaration | TableName is declaration of temporary table which carries the data from XML string.

DECLARE @docHandle INT,

@employeeXml VARCHAR(MAX)

SET @employeeXml = N'

<ROOT>

<Employee EmployeeId="2" FirstName="Bhavin" LastName="Patel" Designation="Sr. Software Engineer" >

      <Order OrderId="1" EmployeeId="2" OrderDate="03/16/2011 05:45 AM">

            <OrderItem ItemId="1" OrderId="1" Name="Item 1" Price="12.00" />

            <OrderItem ItemId="2" OrderId="1" Name="Item 2" Price="19.00" />

      </Order>

      <Order OrderId="2" EmployeeId="2" OrderDate="03/10/2011 05:45 AM">

            <OrderItem ItemId="3" OrderId="2" Name="Item 3" Price="11.00" />

            <OrderItem ItemId="4" OrderId="2" Name="Item 4" Price="15.00" />

      </Order>

</Employee>

<Employee EmployeeId="3" FirstName="Carlos" LastName="Gonzlez" Designation="Team Leader" >

      <Order OrderId="3" EmployeeId="3" OrderDate="03/03/2011 01:40 AM">

            <OrderItem ItemId="1" OrderId="1" Name="Item 1" Price="12.00" />

      </Order>

</Employee>

</ROOT>'

EXEC sp_xml_preparedocument @docHandle OUTPUT, @employeeXml

-- Attribute Specific --

SELECT DISTINCT EmployeeId, FirstName, LastName

FROM OPENXML (@docHandle, '/ROOT/Employee',1)

WITH (EmployeeId INT, FirstName VARCHAR(MAX), LastName VARCHAR(MAX))

SELECT DISTINCT ItemId, Name, Price

FROM OPENXML (@docHandle, '/ROOT/Employee/Order/OrderItem',1)

WITH (ItemId INT, Name VARCHAR(MAX), Price MONEY)

-- Element Specific with Attribute Mapping --

SELECT *

FROM OPENXML (@docHandle, '/ROOT/Employee/Order/OrderItem',2)

WITH (OrderId INT '../@OrderId', EmployeeId VARCHAR(10) '../@EmployeeId', EmployeeFName VARCHAR(10) '../../@FirstName',

OrderDate DATETIME '../@OrderDate', ItemId INT '@ItemId', Name VARCHAR(MAX) '@Name')

-- Element Specific --

SET @employeeXml = N'

<ROOT>

<Employee>

   <EmployeeId>2</EmployeeId>

   <FirstName>Bhavin</FirstName>

   <LastName>Patel</LastName>

   <Designation>Sr. Software Engineer</Designation>

</Employee>

<Employee>  

   <EmployeeId>3</EmployeeId>

   <FirstName>Carlos</FirstName>

   <LastName>Gonzlez</LastName>

   <Designation>Team Leader</Designation>

</Employee>

</ROOT>'

EXEC sp_xml_preparedocument @docHandle OUTPUT, @employeeXml

SELECT *

FROM OPENXML (@docHandle, '/ROOT/Employee',2)

WITH (EmployeeId VARCHAR(50), FirstName VARCHAR(50), LastName VARCHAR(50))

-- Element & Attribute Specific --

SET @employeeXml = N'

<ROOT>

<Employee EmployeeId="2" FirstName="Bhavin" LastName="Patel" Designation="Sr. Software Engineer" >

   <FullName>Bhavin Patel</FullName>

   <Order OrderId="1" EmployeeId="2" OrderDate="03/16/2011 05:45 AM">

            <OrderItem ItemId="1" OrderId="1" Name="Item 1" Price="12.00" />

            <OrderItem ItemId="2" OrderId="1" Name="Item 2" Price="19.00" />

      </Order>

      <Order OrderId="2" EmployeeId="2" OrderDate="03/10/2011 05:45 AM">

            <OrderItem ItemId="3" OrderId="2" Name="Item 3" Price="11.00" />

            <OrderItem ItemId="4" OrderId="2" Name="Item 4" Price="15.00" />

      </Order>

</Employee>

<Employee EmployeeId="3" FirstName="Carlos" LastName="Gonzlez" Designation="Team Leader" >

   <FullName>Carlos Gonzlez</FullName>

   <Order OrderId="3" EmployeeId="3" OrderDate="03/03/2011 01:40 AM">

            <OrderItem ItemId="1" OrderId="1" Name="Item 1" Price="12.00" />

      </Order>

</Employee>

</ROOT>'

EXEC sp_xml_preparedocument @docHandle OUTPUT, @employeeXml

SELECT *

FROM OPENXML (@docHandle, '/ROOT/Employee',3)

WITH (EmployeeId INT, FullName VARCHAR(150))

EXEC sp_xml_removedocument @docHandle