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 @ntIdoc AS INTEGER
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" />
</Root>'

--Process one: Using OpenXML
EXEC SP_XML_PREPAREDOCUMENT @ntIdoc OUTPUT, @xml
SELECT *
FROM OPENXML (@ntIdoc, '/Root/Emp',2)
WITH (
EmpId BIGINT '@EmpId',
Name VARCHAR(200) '@Name',
Age INT '@Age',
Salary MONEY '@Salary'
)

EXECUTE SP_XML_REMOVEDOCUMENT @ntIdoc


--Process two: Using Xquery

SELECT
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. 

1 comment:

  1. Anonymous4/25/2016

    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...

    ReplyDelete