Xml parsing in MS SQL Server

OpenXml:

1.The OPENXML function provides a rowset view over an XML document.

2.OPENXML allows the data in XML document to be treated just like the columns and rows of your database table i.e., xml tags as columns and the value as rows.

Advantages:

Data can be inserted / updated very quickly and efficiently without multiple trips to the database.

Example:

If 100 records is to inserted/updated, then the traditional SQL method is using 100 insert/update methods,which will result in degradation in performance.Using XML, these 100 trips can be reduced to 1 trip. This increases the performance of your application.

Basic Syntax

OPENXML ( i_doc int [in], row_pattern nvarchar[in], [flags byte[in]] )

[WITH(SchemaDeclaration|TableName) tblalias]

Arguments
i_doc is the document handle of the internal representation of an XML document.
This handle is obtained by calling the system stored procedure sp_xml_preparedocument(will be discussed)
row_pattern is the XPath query used to identify the nodes to be processed as rows.
flags indicates the mapping between the XML data and the relational rowset. (optional parameter)

0 - Defaults to attribute-centric mapping.
1 - Attribute-centric mapping. (Combined with XML_ELEMENTS)
2 - Element-centric mapping. (Combined with XML_ATTRIBUTES)
3 - Combined with XML_ATTRIBUTES or XML_ELEMENTS

SchemaDeclaration is the schema definition of the form:

ColName ColType [ColPattern | MetaProperty][, ColName ColType [ColPattern | MetaProperty]...]

Eg: WITH (EMPLOYEENAME VARCHAR(30), EMPLOYEESALARY INTEGER)

TableName is the table name that can be given, instead of Schema Declaration, if a table exists.

The WITH clause provides a table format using either SchemaDeclaration or specifying an existingTableName.

System Stored Procedures for OpenXML

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

• sp_xml_preparedocument
• sp_xml_removedocument

---->To write queries using OPENXML, you must first create an internal representation of the XML document by calling sp_xml_preparedocument

syntax: sp_xml_preparedocument hdoc OUTPUT,@xmlText

---->Removes the internal representation of the XML document specified by the document handle usingsp_xml_removedocument

syntax: sp_xml_removedocument hdoc

Examples

CREATE PROCEDURE dbo.TestOpenXML
(  @strXML VARCHAR(2000)
)
AS
DECLARE @XMLDocPointer INT
EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @strXML

BEGIN TRANSACTION

INSERT INTO Employees(EmployeeName, EmployeeSalary, DeptID)
SELECT NAME, SALARY, DEPTID
FROM OPENXML(@XMLDocPointer,'/RECORD/EMPLOYEE',2)
WITH (NAME VARCHAR(30) ‘@NAME’, SALARY INT ‘@SALARY’, DEPTID INT '@DEPTID')
COMMIT

EXEC sp_xml_removedocument @XMLDocPointer
RETURN

THank YOu VEry MUch, hope you enjoyed. if have any query all are always welcome,don't hesitate to leave a comment.Good Day.