Let’s take a simple table (Employee) to perform this task.
/Employee%20Table.png)
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))
/Attribute%20Specific%20Result.png)
SELECT DISTINCT ItemId, Name, Price
FROM OPENXML (@docHandle, '/ROOT/Employee/Order/OrderItem',1)
WITH (ItemId INT, Name VARCHAR(MAX), Price MONEY)
/Attribute%20Specific%20Result%20with%20XPath.png)
-- 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%20Specific%20Result%20with%20Attribute%20Mapping.png)
-- 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%20Specific%20Result.png)
-- 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
/Element%20and%20Attribute%20Specific%20Result.png)