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

CustomerID	ContactName	EmployeeID	ProductDetails
1	Nihar	5	11
1	Nihar	5	42
2	HR	3	72

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 -

CustomerID	ContactName	EmployeeID	ProductDetails
1	Nihar	5	11
1	Nihar	5	42
2	HR	3	72

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