Quick thought on optimization, as Alan mentioned, the text() method is faster.

😎

Same XML data sample as before

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @xml XML;

SET @xml =

'<?xml version="1.0" encoding="UTF-8"?>

<result resultID="200">

<status>OK</status>

<message></message>

<countryCode>US</countryCode>

<zoneName>America/Los_Angeles</zoneName>

<abbreviation>PDT</abbreviation>

<gmtOffset>-25200</gmtOffset>

<dst>1</dst>

<timestamp>1430141082</timestamp>

</result>'

Looking at the first nodes query without the text method

SELECT

Tbl.Col.value('(countryCode)[1]' , 'NVARCHAR(5)' ) AS countryCode,

Tbl.Col.value('(zoneName)[1]' , 'NVARCHAR(50)') AS zoneName,

Tbl.Col.value('(abbreviation)[1]' , 'NVARCHAR(6)' ) AS abbreviation,

Tbl.Col.value('(gmtOffset)[1]' , 'INT' ) AS gmtOffset,

Tbl.Col.value('(dst)[1]' , 'INT' ) AS dst

FROM @xml.nodes('result') Tbl(Col);

Execution Plan 1

we see two XML Reader functon calls for each value joined with a nested loop. Changing the query to use the text() method removes all but one of the extra functon calls. This reduces the cost of the query by approximately 75%.

SELECT

Col.value('(countryCode/text())[1]' , 'NVARCHAR(5)' ) AS countryCode,

Col.value('(zoneName/text())[1]' , 'NVARCHAR(50)') AS zoneName,

Col.value('(abbreviation/text())[1]' , 'NVARCHAR(6)' ) AS abbreviation,

Col.value('(gmtOffset/text())[1]' , 'INT' ) AS gmtOffset,

Col.value('(dst/text())[1]' , 'INT' ) AS dst,

Col.value('(@resultID)' , 'INT' ) AS resultID

FROM @xml.nodes('/result') Tbl(Col);

Execution Plan 2

Looking at the second execution plan we see there is still some room for improvements, the retrieval of the attribute @resultID is close to 50% of the cost, there is still an extra XML Reader function call and each value has a Filter operator which isn't really doing anything.

The last version of the query bypasses the nodes() method by directly calling the value() method on the XML variable.

SELECT

@xml.value('(/result/countryCode/text())[1]' , 'NVARCHAR(5)' ) AS countryCode,

@xml.value('(/result/zoneName/text())[1]' , 'NVARCHAR(50)') AS zoneName,

@xml.value('(/result/abbreviation/text())[1]' , 'NVARCHAR(6)' ) AS abbreviation,

@xml.value('(/result/gmtOffset/text())[1]' , 'INT' ) AS gmtOffset,

@xml.value('(/result/dst/text())[1]' , 'INT' ) AS dst,

@xml.value('(/result/@resultID)[1]' , 'INT' ) AS resultID

;

Execution Plan 3

Not much more to do here 😉

Comparing the costs of the three queries we see that the improvements are substantial, the third being close to 1% of the first.