XML has become a popular data format for exchanging information between systems. In SQL Server, the OPENXML feature provides a convenient way to use XML documents as a data source for your procedures. With OPENXML, you can treat the data in an XML document as if it were columns and rows in a database table. This allows for efficient insertion and updating of data without multiple trips to the database, resulting in improved performance.

Basic Syntax

The basic syntax of OPENXML is as follows:

OPENXML ( idoc int [in], rowpattern nvarchar[in],[ flags byte[in]]) [WITH SchemaDeclaration | TableName )]

The idoc parameter is the document handle of the internal representation of the XML document. This handle is obtained by calling the system stored procedure sp_xml_preparedocument. The rowpattern parameter is the XPath query used to identify the nodes to be processed as rows. The flags parameter indicates the mapping between the XML data and the relational rowset.

You can provide a SchemaDeclaration or a TableName to specify the structure of the resulting table. If the optional WITH clause is not specified, the results are returned in an edge table format, which represents the fine-grained XML document structure.

System Stored Procedures for OPENXML

SQL Server provides two system stored procedures that are used in conjunction with OPENXML:

• sp_xml_preparedocument: This procedure creates an internal representation of the XML document and returns a handle to it.
• sp_xml_removedocument: This procedure removes the internal representation of the XML document.

Before writing queries using OPENXML, you must first call sp_xml_preparedocument to create the internal representation of the XML document. This handle is then passed to OPENXML to provide a tabular view of the document based on the XPath query. Once you are done with the XML document, you can call sp_xml_removedocument to free up the memory.

Example Usage

Let’s consider an example where we have two tables: Employees and Departments. The Employees table has columns EmployeeID, EmployeeName, EmployeeSalary, and DeptID. The Departments table has columns DeptID and DeptName.

If we have an XML file with employee data, we can use OPENXML to insert the data into the Employees table:

INSERT INTO Employees(EmployeeName, EmployeeSalary, DeptID)
SELECT EMPLOYEENAME, EMPLOYEESALARY, DEPTID
FROM OPENXML(@XMLDocPointer,'/RECORD/EMPLOYEE',2)
WITH (EMPLOYEENAME VARCHAR(30), EMPLOYEESALARY INTEGER, DEPTID INTEGER)

In this example, we are selecting the values from the XML document and mapping them to the corresponding columns in the Employees table.

We can also use OPENXML to update records in the Employees table. For example, if we want to transfer employees from the Production Department to the Stores Department:

UPDATE Employees
SET DeptID = xmlTable.DEPTID 
FROM OPENXML(@XMLDocPointer,'/RECORD/EMPLOYEE',2)
WITH (EMPLOYEEID INTEGER, DEPTID INTEGER) XmlTable 
WHERE XmlTable.EMPLOYEEID = Employees.EmployeeID

In this case, we are updating the DeptID column in the Employees table based on the values from the XML document.

Conclusion

OPENXML is a powerful feature in SQL Server that allows you to work with XML data as if it were relational data. By using OPENXML, you can efficiently insert and update data from XML documents, reducing the number of trips to the database and improving performance. In this article, we explored the basic syntax of OPENXML and provided examples of its usage. In future articles, we will discuss other XML features offered by SQL Server.