Thursday, 15 October 2015

Ways to SELECT XML string using Nodes and OPENXML()

XML reading is most probably way of reading data from file. Only for knowledge we are checking performance by two ways OPENXML() and Nodes().
Check following the queries at your end as part of "Performace Tuning ".
OpenXML() -
                      OpenXML() is a rowset provider. OpenXML can be used in T-SQL statements in which rowset providers such as a table, view, or the OPENROWSET function can appear.
Example :
DECLARE @idoc int, @doc varchar(1000),
@XMLString xml
SET @doc ='
<ROOT>
<Customer CustomerID="1" ContactName="Nihar">
<Order CustomerID="1" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="2" ContactName="HR">
<Order CustomerID="2" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>';
--Create an internal representation of the XML document.EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
-- Execute a SELECT statement that uses the OPENXML rowset provider.SELECT distinct *
FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',1)
WITH (CustomerID varchar(10) '../@CustomerID',
ContactName varchar(20) '../../@ContactName',
EmployeeID varchar(20) '../@EmployeeID',
ProductDetails varchar(20) '@ProductID'
);


Output
CustomerIDContactNameEmployeeIDProductDetails
1Nihar511
1Nihar542
2HR372


Here we represent same output with XML.Node() which prepares output faster in complex queries
DECLARE @idoc int, @doc varchar(1000),
@XMLString xml
SET @doc ='
<ROOT>
<Customer CustomerID="1" ContactName="Nihar">
<Order CustomerID="1" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="2" ContactName="HR">
<Order CustomerID="2" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>';
SELECT @XMLString = cast(@doc as xml)
;WITH CTE AS (
SELECT
CustomerID = Events.value('../@CustomerID', 'varchar(120)'),
ContactName = Events.value('../../@ContactName', 'varchar(120)'),
Employeeid = Events.value('../@EmployeeID','varchar(120)'),
ProductDetails =events.value('@ProductID','varchar(20)')
FROM
@XMLString.nodes('/ROOT/Customer/Order/OrderDetail') AS XTbl(Events)
)
select distinct * from cte
Output -
CustomerIDContactNameEmployeeIDProductDetails
1Nihar511
1Nihar542
2HR372


You can try with realtime example and check performance with Node() and OpenXML()

No comments:

Post a Comment