Tuesday, May 6, 2014

XPath: Query XML Exercises

It's important to have a solid understanding of XPath Expression. This will help us convert XML document to tabular format using both OPENXML function and XQuery. If you want to practice querying XML, use this online XPath Tester tool. Or download this XPath Visualizer and install on your computer. Note press Ctrl + N in the XPath Visualizer to create a new source widows then copy and paste the XML source.
For our example, I'll use this XML document. You can view a set of XPath exercises from MSDN.

<bookstore specialty="novel">
  <book style="autobiography">
      <award>Trenton Literary Review Honorable Mention</award>
  <book style="textbook">
      <publication>Selected Short Stories of
  <magazine style="glossy" frequency="monthly">
    <subscription price="24" per="year"/>
  <book style="novel" id="myfave">
      <degree from="Trenton U">B.A.</degree>
      <degree from="Harvard">Ph.D.</degree>
      <publication>Still in Trenton</publication>
      <publication>Trenton Forever</publication>
    <price intl="Canada" exchange="0.7">6.50</price>
      <p>It was a dark and stormy night.</p>
      <p>But then all nights in Trenton seem dark and
      stormy to someone who has gone through what
      <emph>I</emph> have.</p>
  <my:book xmlns:my="uri:mynamespace" style="leather" price="29.50">
    <my:title>Who's Who in Trenton</my:title>
    <my:author>Robert Bob</my:author>

Let's go over some XPath syntax
Expression                     Description
   /                           Root node(absolute path)
   Node                   The node (relative path)
   //                          All descendant nodes of the current node
   ./                          Current node
   ../                         Parent of current node
   ../../                      Grandparent of current node
  @                         Attribute nodes

Let's go over some examples using the XML document above. Note XML is case sensitive.
1. ALL elements in the XML document.

2. All <book> elements. 

3. All element with attribute.

4. All <book> element with author's <first-name> element value equals to Mary.
bookstore/book/author[first-name = "Mary"]
bookstore//author[first-name = "Mary"]
.//author[first-name = "Mary"]
*//author[first-name = "Mary"]

5. All <book>  element with author's [last-name] element value equal to Bob and editor's <first-name> element value equals to Preston.
bookstore/book/author[last-name = "Bob" and ../editor[first-name = "Preston"]]

6. All <book> element with author's 
[last-name] element value equals to Bob and <price> element >= 10. 
bookstore/book/author[last-name = "Bob" and ../price >= 10]

7. All <book> element with author's 
[last-name]  element value equals to Bob and <price>  element >=10 
bookstore/book[author[last-name = "Bob" and ../price >= 10]]

8. All <book> element with id attribute.

9. All <book> element with attribute style = textbook.
bookstore/book[@style = "textbook"]

10. All <book> element with <price> element value >= 10

Notice the examples above the two periods (..) syntax is equivalent to go up one level, and single period (.) is equivalent to current context. This just like in a file system. Try this, if you are using windows OS, launch command prompt and type this command and press enter: cd ../ (this command change the current directory to the parent folder of the current path) and this: dir . (this command lists all the file and folders in the current path).

Anyway, just like SQL, XPath only need a few days to learn but a lifetime to master.

No comments:

Post a Comment