Logo
HOW TO
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
 
 
Windows Server

SQL Server 2012 : XML and the Relational Database - Querying XML Data Using XQuery (part 1) - Understanding XQuery Expressions and XPath

8/18/2013 9:33:41 PM

Storing XML in the database is one thing; querying it efficiently is another. Prior to the xml data type in SQL Server 2005, you had to deconstruct the XML and move element and attribute data into relational columns to perform a query on the XML data residing in the text column. You could also resort to some other searching mechanism, such as character pattern matching or full-text search, neither of which provides completely reliable parsing capability. Today, XQuery provides a native and elegant way to parse and query XML data in SQL Server.

1. Understanding XQuery Expressions and XPath

XQuery is a language used to query and process XML data. XQuery is a W3C standard, and its specification is located at http://www.w3.org/TR/xquery/. The XQuery specification contains several descriptions of requirements, use cases, and data models. We encourage you to review the specification to get a full understanding of what XQuery is all about. For now, we will explain enough to cover the basics. After reading this section, you will be able to select, filter, and update XML data using XQuery.

Because XQuery is an XML language, all the rules of XML apply. XQuery uses lowercase element names (“keywords”), and because XML itself is case-sensitive, you must take this into account when writing queries. Although XQuery has some powerful formatting and processing commands, it is primarily a query language (as its name suggests), so we will focus here on writing queries. The body of a query consists of two parts: an XPath expression and a FLWOR (pronounced “flower”) expression. (FLWOR is an acronym based on the primitive XQuery keywords for, let, where, order by, and return.)

XPath Expressions

XPath, another W3C standard (http://www.w3.org/TR/xpath), uses path expressions to identify specific nodes and attributes in an XML document. These path expressions are similar to the syntax you see when you work with a computer file system (for example, C:\folder\myfile.doc). Take a look at the following XML document:

<catalog>
  <book category="ITPro">
    <title>Windows Step By Step</title>
    <author>Jeff Hay</author>
    <price>49.99</price>
  </book>
  <book category="Developer">
    <title>Learning ADO .NET</title>
    <author>Holly Holt</author>
    <price>39.93</price>
  </book>
  <book category="ITPro">
    <title>Administering IIS</title>
    <author>Jed Brown</author>
    <price>59.99</price>
  </book>
</catalog>

The following XPath expression selects the root element catalog:

/catalog

This XPath expression selects all the book elements of the catalog root element:

/catalog/book

And this XPath expression selects all the author elements of all the book elements of the catalog root element:

/catalog/book/author

XPath enables you to specify a subset of data within the XML (via its location within the XML structure) that you want to work with. XQuery is more robust and allows you to perform more complex queries against the XML data using FLWOR expressions combined with XPath.

FLWOR Expressions

Just as SELECT, FROM, WHERE, GROUP BY, and ORDER BY form the basis of the SQL selection logic, the for, let, where, order by, and return (FLWOR) keywords form the basis of every XQuery query you write. You use the for and let keywords to assign variables and iterate through the data within the context of the XQuery query. The where keyword works as a restriction and outputs the value of the variable.

For example, the following basic XQuery query uses the XPath expression /catalog/book to obtain a reference to all the <book> nodes, and the for keyword initiates a loop, but only of elements where the category attribute is equal to “ITPro”. This simple code snippet iterates through each /catalog/book node using the $b variable with the for statement only where the category attribute is “ITPro” and then returns as output the resulting information in descending order by the author’s name using the order keyword:

for $b in /catalog/book
 where $b/@category="ITPro"
 order by $b/author[1] descending
 return ($b)

Example 1 shows a simple example that uses this XQuery expression on an xml data type variable. XML is assigned to the variable, and then the preceding XQuery expression is used in the query method (explained in the next section) of the xml data type.

Example 1. A simple XQuery example.

DECLARE @Books xml = '
<catalog>
  <book category="ITPro">
    <title>Windows Step By Step</title>
    <author>Jeff Hay</author>
    <price>49.99</price>
  </book>
  <book category="Developer">
    <title>Learning ADO .NET</title>
    <author>Holly Holt</author>
    <price>39.93</price>
  </book>
  <book category="ITPro">
    <title>Administering IIS</title>
    <author>Ted Bremer</author>
    <price>59.99</price>
  </book>
</catalog>'

SELECT @Books.query('
  <ITProBooks>
    {
      for $b in /catalog/book
      where $b/@category="ITPro"
      order by $b/author[1] descending
      return ($b)
    }
  </ITProBooks>')

The results are as follows:

<ITProBooks>
  <book category="ITPro">
    <title>Administering IIS</title>
    <author>Ted Bremer</author>
    <price>59.99</price>
  </book>
  <book category="ITPro">
    <title>Windows Step By Step</title>
    <author>Jeff Hay</author>
    <price>49.99</price>
  </book>
</ITProBooks>

Notice that Ted’s record is first because the order is descending by the author element. Holly’s record is not in the output because the category element is restricted to “ITPro”. There is a root element wrapped around the XQuery statement with <ITProBooks> and </ITProBooks>, so all the results for IT books extracted from source XML having a catalog root element are contained inside of an ITProBooks root element.

Other -----------------
- SQL Server 2012 : XML and the Relational Database - Shredding XML Using OPENXML
- Maintaining Windows Home Server 2011 : Defragmenting the System Drive, Reviewing Event Viewer Logs, Setting Up a Maintenance Schedule
- Maintaining Windows Home Server 2011 : Deleting Unnecessary Files from the System Drive
- Extending Dynamics GP with Free Software : Capturing Screenshots and sending an E-mail for support
- Extending Dynamics GP with Free Software : Extending Dynamics GP with the Support Debugging Tool, Coloring windows by company
- BizTalk 2006 : Getting Started with Pipeline Development (part 3) - Configuring Recoverable Interchanges, Using the Default Pipelines
- BizTalk 2006 : Getting Started with Pipeline Development (part 2) - Understanding Pipeline Execution, Understanding Interchanges
- BizTalk 2006 : Getting Started with Pipeline Development (part 1) - Pipeline Stages
- System Center Configuration Manager 2007 : Creating and Modifying Configurations (part 5) - External Authoring, Authoring with CP Studio
- System Center Configuration Manager 2007 : Creating and Modifying Configurations (part 4) - Console Authoring - Validation Criteria
 
 
REVIEW
- First look: Apple Watch

- 10 Amazing Tools You Should Be Using with Dropbox

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
 
VIDEO TUTORIAL
- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
 
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS
Popular keywords
HOW TO Swimlane in Visio Visio sort key Pen and Touch Creating groups in Windows Server Raid in Windows Server Exchange 2010 maintenance Exchange server mail enabled groups Debugging Tools Collaborating
Top 10
- Microsoft Excel : How to Use the VLookUp Function
- Fix and Tweak Graphics and Video (part 3) : How to Fix : My Screen Is Sluggish - Adjust Hardware Acceleration
- Fix and Tweak Graphics and Video (part 2) : How to Fix : Text on My Screen Is Too Small
- Fix and Tweak Graphics and Video (part 1) : How to Fix : Adjust the Resolution
- Windows Phone 8 Apps : Camera (part 4) - Adjusting Video Settings, Using the Video Light
- Windows Phone 8 Apps : Camera (part 3) - Using the Front Camera, Activating Video Mode
- Windows Phone 8 Apps : Camera (part 2) - Controlling the Camera’s Flash, Changing the Camera’s Behavior with Lenses
- Windows Phone 8 Apps : Camera (part 1) - Adjusting Photo Settings
- MDT's Client Wizard : Package Properties
- MDT's Client Wizard : Driver Properties
 
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro