Jan 29, 2013

OPENXML Vs XQUERY which one is better according to query performance sql server

There are two ways to get the values from XML variables in sql server:

1. Using OpenXML
2. Using XQuery

From the query execution or performance point of view which one is better and when we should xquery and when openxml in sql server? Here we will test the both the processes and compare the execution cost.

--Declaring the variables

DECLARE @xml AS XML = '<Root>
<Emp EmpId = "1" Name = "Scott" Age = "30" Salary = "50" />
<Emp EmpId = "2" Name = "Greg" Age = "31" Salary = "50" />
<Emp EmpId = "3" Name = "Alan" Age = "34" Salary = "60" />
<Emp EmpId = "4" Name = "Alain" Age = "30" Salary = "60" />
<Emp EmpId = "5" Name = "Moti" Age = "32" Salary = "80" />
<Emp EmpId = "6" Name = "Usha" Age = "36" Salary = "80" />
<Emp EmpId = "7" Name = "Hashan" Age = "30" Salary = "80" />

--Process one: Using OpenXML
FROM OPENXML (@ntIdoc, '/Root/Emp',2)
EmpId BIGINT '@EmpId',
Name VARCHAR(200) '@Name',
Age INT '@Age',
Salary MONEY '@Salary'


--Process two: Using Xquery

Emp.E.value('@EmpId', 'BIGINT') EmpId,
Emp.E.value('@Name', 'VARCHAR(200)') Name,
Emp.E.value('@Age', 'INT') Age,
Emp.E.value('@Salary', 'MONEY') Salary
FROM @xml.nodes('/Root/Emp') AS Emp(E)

If we will check the actual execution plan of both queries:

We will observer the query cost, using openxml is 1% while using xquery is 99% compare to this batch. Which proves that in this situation using openxml is much more better than using xquery.

Limitation: We can use OPENXML to get the data from xml only if xml data has stored from variables but not from column of a tables while using XQuery it is possible. 


Anonymous said...

The most expensive on OPENXML are the calls to SP_XML_PREPAREDOCUMENT and SP_XML_REMOVEDOCUMENT. You cannot just ignore this! And then try the same with a more complex and deeper nested XML. The results will be the other way round...

BaskaranR said...

Nice Article.!
Also Refer below Articles:
Reading XML Elements and Attributes using OPENXML and XQuery in SQL Server

Top 15 Power BI interview Questions with detailed Answers

Implement Best Practice and Performance tuning tips in SSIS and ETL Development

Monitoring and Performance Tuning Tips for SQL Server database

SQL Server/Developer Interview Questions and Answers for Experienced Professional