XML .value simple syntax

There are several ways to get .value to extract an item of information from xml.  in the following article I will show some of the syntax and reasons why some methods are better than others.

All the calls in this post will all reference the following blob.  This is a simple blob for demonstration purposes only.

<xmlblob>

<personlist>

<person>Phil</person>

<person>Stina</person>

</personlist>

</xmlblob>

or

The Following 3 methods of .value calls all work and will all bring back the same value (which is the first person element in the blob)

DECLARE @xml xml = N'<xmlblob><personlist><person>Phil</person><person>Stina</person></personlist></xmlblob>’

SELECT @xml.value(‘(xmlblob/personlist/person)[1]’,‘varchar(50)’)

 SELECT @xml.value(‘(xmlblob/personlist/person/text())[1]’,‘varchar(50)’)

 SELECT @xml.value(‘(xmlblob[1]/personlist[1]/person[1])’,‘varchar(50)’)

 The first query is potentially the kind you would want to have if you were using typed xml via a schema,  however in this case without a schema the second query is far better especially with reference to the Query plans generated. this is due to the /text() on the end of the element name that directs the xml to be processed as text.

100% to untyped xml, 0% for typed xml

The third option is interesting as it is the slowest to run under the above conditions and would normally be avoided however the interesting thing with this syntax is that when other elements are accessed the query is seemingly scaled.

The following queries look at the second element in the person list.

SELECT @xml.value(‘(xmlblob/personlist/person)[2]’,‘varchar(50)’)

 SELECT @xml.value(‘(xmlblob/personlist/person/text())[2]’,‘varchar(50)’)

 SELECT @xml.value(‘xmlblob[1]/personlist[1]/person[2]’,‘varchar(50)’)

  Still the second method remains the best with the third following closely behind. 

 Improvements can be made to the third query by using the text() method as in the second query, but the QP cost is still above that of the second query.

Advertisements
This entry was posted in SQL, XML. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s