Examples - Advanced SQL Engine - Teradata Database

These queries illustrate the XMLQUERY syntax.

'(1,2,3)' is an XQuery query that returns a sequence of three integers (1, 2, and 3).

SELECT XMLQUERY('(1,2,3)');

The result of the query is:

XMLQUERY('(1,2,3)')
-------------------
1 2 3

The following query returns a sequence of Item elements. The XQuery query, the path expression /Customer/Order/Item, is evaluated on the XML document in the customerXML column. The PASSING clause passes the document to the query as the context item. The path expression /Customer/Order/Item is evaluated relative to the context item.

SELECT XMLQUERY('/Customer/Order/Item'
   PASSING BY VALUE customer.customerXML
   RETURNING CONTENT
   NULL ON EMPTY) FROM customer WHERE customerID = 1;

Here are partial results from the query. The ellipsis (...) is not part of the query results. It indicates that the query returns additional results that are truncated in the example.

XMLQUERY('/Customer/Order/Item', PASSING BY VALUE customerXML ...
---------------------------------------------------------------------
<Item ID="001">    <Quantity>10</Quantity>    <PartNumber>F54709</PartNumber>
<Description>Motorola S10-HD Bluetooth Stereo
Headphones</Description> <UnitPrice>29.50</UnitPrice> <Price...

This query is similar to the previous one except that the parameter is passed by name, and it is referenced in the query by its name ($doc).

SELECT XMLQUERY('$doc/Customer/Order/Item'
   PASSING customerXML AS "doc") FROM customer WHERE customerID = 1;

The following is an identical query returning XML as a document node with multiple child Item elements. These results do not have a well-formed XML representation. If a well-formed XML document result is desired, wrap the query in a root element.

SELECT XMLQUERY('$i/Customer/Order/Item'
   PASSING customerXML AS i
   RETURNING CONTENT) FROM customer WHERE customerID = 1;

This query illustrates wrapping the sequence of Item elements in a root element:

SELECT XMLQUERY('<root>{$i/Customer/Order/Item}</root>'
   PASSING customerXML AS i
   RETURNING CONTENT) FROM customer WHERE customerID = 1;

Here are partial results from the query:

XMLQUERY('<root>{$i/Customer/Order/Item}</root>', PASSING BY ...'
----------------------------------------------------------------
<root><Item ID"001">    <Quantity>10</Quantity>    <PartNumber>F54709</PartNumber>
   <Description>Motorola S10-HD Bluetooth Stereo Headphones</Description>
    <UnitPrice>29.50</UnitPrice>...

This query returns the names of all customers who ordered an item with a particular item id. The query takes multiple parameters: one context item parameter (the XML document) and one named parameter (the item id).

SELECT XMLQUERY('for $i in /Customer/Order/Item
   where $i/@ID = $itemId
   return $i/../../Name'
   PASSING BY VALUE customer.customerXML,
   '001' AS itemId
   RETURNING SEQUENCE
   EMPTY ON EMPTY ) FROM customer WHERE customerID = 1;

Here are partial results from the query:

XMLQUERY('for $i in /Customer/Order/Item where $i/@ID = $itemId ...
-------------------------------------------------------------------
<Name>John Hancock</Name>

This query takes multiple named parameters ($i and $doc):

SELECT XMLQUERY('for $i in $doc/Customer/Order/Item
   where $i/@ID = $itemId
   return $i/../../Name'
   PASSING BY VALUE customer.customerXML AS "doc",
   '001' AS itemId
   RETURNING SEQUENCE
   EMPTY ON EMPTY ) FROM customer WHERE customerId=1;

Here are partial results from the query:

XMLQUERY('for $i in $doc/Customer/Order/Item where $i/@ID = $itemId ...
-----------------------------------------------------------------------
<Name>John Hancock</Name>