Best applications for XQuery or XPath

XPath provides a subset of the XQuery language. You can write better pureXML applications if you understand when it is better to use XQuery and when it is better to use XPath.

When to use XPath

If you need only to identify qualifying documents in an XML column using an XMLEXISTS predicate, XPath is more efficient than XQuery. XPath has the necessary function to identify the documents. In addition, you can use an XML index with an XPath expression in an XMLEXISTS predicate, but you cannot use an XML index with an XQuery expression.

When to use XQuery

Use XQuery when you need constructors or you need to use conditional logic.

XQuery code is more readable than:
  • Code that uses XPath with the XMLDOCUMENT, XMLELEMENT, and XMLATTRIBUTES functions to construct documents
  • Code that uses XPath with the XMLTABLE, XMLDOCUMENT, XMLELEMENT, and XMLATTRIBUTES, and XMLAGG functions to perform conditional logic

Example: Construction of documents with XPath and XQuery:

Suppose that the PURCHASEORDERS table is defined like this:

CREATE TABLE PURCHASEORDERS (
 PONUMBER VARCHAR(10) NOT NULL,   
 STATUS VARCHAR(10) NOT NULL WITH DEFAULT 'New',
 XMLPO XML)

The PORDER column of the PURCHASEORDER table contains this document, which is associated with PONUMBER '200300001':

<purchaseOrder 
   xmlns="xmlns="http://posample.org"
   orderDate="2009-12-01">
  <shipTo exportCode="1">
    <name>Helen Zoe</name>
    <street>55 Eden Street</street>
    <city>San Jose</city>
    <state>CA</state>
    <postcode>CB1 1JR</postcode>
  </shipTo>
  <shipTo exportCode="1">
    <name>Joe Lee</name>
    <street>66 University Avenue</street>
    <city>Palo Alto</city>
    <state>CA</state>
    <postcode>CB1 1JR</postcode>
  </shipTo>
  <billTo>
    <name>Robert Smith</name>
    <street>8 Oak Avenue</street>
    <city>Old Town</city>
    <state>PA</state>
    <zip>95819</zip>
  </billTo>
  <items>
    <item partNum="833-AA">
      <productName>Lapis necklace</productName>
      <quantity>1</quantity>
      <USPrice>99.95</USPrice>
      <ipo:comment>Want this for the holidays!</ipo:comment>
      <shipDate>2008-12-05</shipDate>
    </item>
    <item partNum="945-ZG">
      <productName>Sapphire Bracelet</productName>
      <quantity>2</quantity>
      <USPrice>178.99</USPrice>
      <shipDate>2009-01-03</shipDate>
    </item>
  </items>
</purchaseOrder>

You need to construct this document:

<invoice invoiceNo="12345">
 <name xmlns="http://posample.org">Robert Smith</name>
 <purchaseOrderNo>200300001</purchaseOrderNo>
 <amount>278.94</amount>
</invoice>

XPath code to construct the document looks similar to this code:

SELECT XMLDOCUMENT(
  XMLELEMENT(NAME "invoice",
   XMLATTRIBUTES( '12345' as "invoiceNo"),
    XMLQUERY('declare default element namespace "http://posample.org";
     /purchaseOrder/billTo/name' PASSING XMLPO),
     XMLELEMENT(NAME “purchaseOrderNo”,
      PONUMBER),
     XMLELEMENT(NAME "amount",
      XMLQUERY('declare default element namespace "http://posample.org";
       fn:sum(/purchaseOrder/items/item/xs:decimal(USPrice))'
       PASSING XMLPO)
      )
  )
 )
 FROM PURCHASEORDERS PO
 WHERE PONUMBER = '200300001'

You can construct the same document using the following XQuery code, which is easier to interpret:

SELECT XMLQUERY(
 'let $x := /purchaseOrder
   return
    <invoice invoiceNo= "12345">
      {$x/billTo/name}
      <purchaseOrderNo> { $y } </purchaseOrderNo>
      <amount> { fn:sum($x/items/item/xs:decimal(USPrice)) } </amount>
    </invoice>' PASSING XMLPO, PO.PONUMBER as "y")
FROM PurchaseOrders PO
WHERE PONUMBER = '200300001'