XML Query in SQL Server 2008

Question

I am just starting to use XML within SQL Server 2008. I have the data in the XML, but I am having a hard time finding good examples of querying the data. Can someone point me to a good place to start? For example, I would want to find all rows within SQL server that contain a certain criteria within the XML like all rows where the XML contains an altitude > 10000.

Answer 1

i think this XML Support in Microsoft SQL Server 2005 page is your best place to start. it hasn't changed for 2008 version.

For specific answers you should provide your xml.

Answer 2

I really enjoy Alex Homer's intro SQL Server 2005 XQuery and XML-DML - Part 1 (quite a mouthful!) on 15seconds.com. Everything's still perfectly valid in SQL Server 2008, too.

Not knowing much about your structure (show us an example XML!), you could try to use a query like this:

select
  (list of fields)
from
  MyTable
where
  MyXmlField.value('(/root/altitude)[1]', 'int') > 10000

You can use the .value XML function to extract a given value from your XML field, based on a XPath expression (here: /root/altitude). You also need to define what type you're expecting to get back (possibly based on an XML schema), and this really gives you a value of that type which you can then use normally in any SQL statement and expression.

Marc

Answer 3

Generally you would use .exist() method if you want to find rows that match some xml criteria but don't need to return the xml document. This allows an optimized usage of the xml index (if you created one), whereas the engine has to touch the actual xml instance in most other cases.

Answer 4

Jacob Sebastian's XML Workshops is the Gold Standard of online free examples of SQL XML querying, here: http://beyondrelational.com/blogs/jacob/archive/2007/12/20/xml-workshops.aspx