XML Processing Functions

XML processing functions create and transform values of type xml.

XML functions supported by Virtual DataPort

XMLQUERY

Description

The XMLQUERY function extracts information from an XML document using the XQuery language (XML Query (XQuery)).

Syntax

XMLQUERY( <XQuery expression:text>, <is XQuery file:boolean> ):xml

XMLQUERY( <XQuery expression:text>, <is XQuery file:boolean>, <xml value:xml> ):xml

XMLQUERY( <XQuery expression:text>, <is XQuery file:boolean>, <xml value:text> , <is XML file:boolean> ):xml
  • XQuery expression. XQuery expression used to query xml data.
  • is XQuery file. True, if the parameter “XQuery expression” is a path to a file containing an XQuery expression. False, if “XQuery expression” is a literal or is the name of a field that contains an expression.
  • xml value. The XML to manipulate.
  • is XML File. True, if the parameter “xml value” is a path to a file containing an XML document. If “is XML File” is false or is missing, “xml value” is a literal or the name of an XML field.

Examples

Consider the view V that only has one column of type “xml” and one row:

books_xml
<BOOKLIST xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <BOOKS>
      <ITEM CAT="MMP">
         <TITLE>Pride and Prejudice</TITLE>
         <AUTHOR>Jane Austen</AUTHOR>
         <PUBLISHER>Modern Library</PUBLISHER>
      </ITEM>
      <ITEM CAT="P">
         <TITLE>Wuthering Heights</TITLE>
         <AUTHOR>Emily Brontë</AUTHOR>
         <PUBLISHER>Penguin Classics</PUBLISHER>
      </ITEM>
    </BOOKS>
    <CATEGORIES DESC="Miscellaneous categories">
        <CATEGORY CODE="P" DESC="Paperback"/>
        <CATEGORY CODE="MMP" DESC="Mass-market Paperback"/>
        <CATEGORY CODE="H" DESC="Hard Cover"/>
   </CATEGORIES>
</BOOKLIST>

Consider the file C:/books_info.xml with the same content as the view V.

Consider the file C:/books.xq with the following XQuery expression:

<ul>
   {
   for $b in //BOOKS/ITEM
   order by $b/TITLE return
   <li>
      <i> { string($b/TITLE) } </i> by { string($b/AUTHOR) }
   </li>
   }
</ul>

And consider the file C:/books2.xq with an XQuery expression that transform the XML document of the file C:/books_info.xml:

<ul>
   {
   for $b in doc('c:/books_info.xml')//BOOKS/ITEM
   order by $b/TITLE return
   <li>
     <i> { string($b/TITLE) } </i> by { string($b/AUTHOR) }
   </li>
   }
</ul>

Example 1

The following queries have the same result:

Query 1

SELECT XMLQUERY('
<ul>
   {
   for $b in doc(''C:/books_info.xml'')//BOOKS/ITEM
   order by $b/TITLE return
    <li>
        <i> { string($b/TITLE) } </i> by { string($b/AUTHOR) }
    </li>
   }
</ul>', false)
FROM Dual();

Query 2

SELECT XMLQUERY ('C:/books2.xq', true)
FROM Dual();

Query 3

SELECT XMLQUERY('<ul>
{
for $b in //BOOKS/ITEM
order by $b/TITLE return
<li>
<i> { string($b/TITLE) } </i> by { string($b/AUTHOR) }
</li>
}
</ul>', false, booksxml)
FROM xQuery_sample_view;

Query 4

SELECT XMLQUERY('C:/books.xq', true, booksxml, false)
FROM xQuery_sample_view
xmlquery
<ul>
  <li>
    <i>Pride and Prejudice</i> by Jane Austen
  </li>
  <li>
    <i>Wuthering Heights</i> by Emily Brontë
  </li>
</ul>

In “Query 1” the XQuery expression is passed as a parameter and in “Query 2” the parameter is the path to a file containing the same expression. That is why in the “Query 2”, the second parameter is true. This expression reads the content of the file “C:/books_info.xml”.

In “Query 3” and “Query 4” the XML document is obtained from the field booksxml of the view V.

XPATH

Description

The XPATH function returns the nodes from an XML document selected by an XPath expression (XPath Language).

This function supports XPath 2.0 at the basic level of conformance defined by W3C. This means that the function provides all XPath features that do not involve schema-awareness.

Syntax

XPATH( <xml value:xml>, <XPath expression:text> [, <xml header:boolean> ]):xml
  • xml value. Required. XML data-type which you want to apply the XPath expression on.
  • XPath expression. Required. XPath expression.
  • xml header. Optional. If true and the selected value is an XML node, the result includes the XML declaration (<?xml version="1.0"…). If false or the selected value is not an XML node, the result will not include this declaration.

If the function is delegated to the Oracle database, the parameter xml header is ignored and the function only returns the result of applying the XPath expression.

Examples

Example 1

SELECT XPATH ( CAST ('xml' ,
'<?xml version="1.0" encoding="ISO-8859-1"?>
<a>
<b>Hello</b>
<b>World</b>
</a>' ) , '/a/b', true) as xpath_results
FROM Dual();
xpath_results
<?xml version=”1.0” encoding=”UTF-8”?><b>Hello</b><b>World</b>

Example 2

SELECT xpath ( cast ('xml' ,
'<?xml version="1.0" encoding="ISO-8859-1"?>
<a>
<b>Hello</b>
<b>World</b>
</a>' ) , '/a/b/text()', true) as xpath_results
FROM Dual();
xpath_results
HelloWorld

Example 3

SELECT xpath ( cast ('xml',
'<?xml version="1.0" encoding="ISO-8859-1"?>
<a>
<b>Hello</b>
<b>World</b>
</a>' ) , '/a/b/text()', false) as xpath_results
FROM Dual();
xpath_results
HelloWorld

The result of “Example 2” and “Example 3” is the same even though in “Example 2” the value of the parameter xml header is true and in “Example 3” is false. The reason is that the result of the XPath function is not an XML document.

XSLT

Description

The XSLT function returns the result of applying an XSL transformation to an XML.

Syntax

XSLT( <XML value:xml>, <XSL value:xml> ):xml

XSLT( <XML value:{xml\|text}>, <xslValue:{xml\|text}>, [, <is path to XML:boolean> ] [, <is path to XSLT:boolean> ]:xml
  • XML value. Required. XML literal, XML field or file to transform.
  • XSL value. Required. XSL literal, field containing an XSL or file containing an XSL.
  • is path to XML. Required only if the type of xml value is text. true if xml value is a path to the xml file. false otherwise.
  • is path to XSLT. Required only if the type of XSL value is text. true if is a path to the XSL file. false otherwise.

Examples

Consider the view V:

xml_sample xsl_sample
  <?xml version='1.0'
  encoding='UTF-8'?>
  <shop>
  <products>
    <product>
      <id>1</id>
      <name>Virtual DataPort</name>
    </product>
    <product>
      <id>2</id>
      <name>ITPilot</name>
    </product>
    <product>
      <id>3</id>
      <name>Scheduler</name>
    </product>
    <product>
      <id>4</id>
      <name>Aracne</name>
    </product>
  </products>
</shop>
<?xml version='1.0'
encoding='UTF-8'?>
<xsl:transform version='1.0'
xmlns:xsl='http://www.w3.org/1999/
XSL/Transform'>

<xsl:template match='/shop/products'>
  <shop>
    <xsl:for-each select='product'>
      <product>
        <xsl:value-of select='name'/>
      </product>
    </xsl:for-each>
  </shop>
</xsl:template>
</xsl:transform >

Example 1

SELECT XSLT(xml_sample, xsl_sample, false, false)
FROM V
xslt
<?xml version="1.0" encoding="UTF-8"?>
<shop>
  <products>
    <product>Virtual DataPort</product>
    <product>ITPilot</product>
    <product>Scheduler</product>
    <product>Aracne</product>
  </products>
</shop>

The same result could be obtained with the following queries:

SELECT XSLT(xml_sample, CAST('xml', xsl_sample), false)
FROM V
SELECT XSLT(CAST('xml', xml_sample), xsl_sample, false)
FROM V
SELECT XSLT(CAST('xml', xml_sample), CAST('xml', xsl_sample))
FROM V

Example 2

Convert the file books.xml using the XSL file books.xsl. Note the two last parameters indicating that the first and the second parameters are paths to files.

SELECT XSLT ('../test/xml/books.xml', '../test/xml/books.xsl', true, true)
FROM V

Example 3

Convert the cells of the columns xml_sample using the XSL file books.xsl.

SELECT XSLT (CAST ('xml', xml_sample), '../test/xml/books.xsl', true)
FROM V

Example 4

Convert the file books.xml using the XSL of the column xsl_sample.

SELECT XSLT ('../test/xml/books.xml', CAST ('xml', xsl_sample), true)
FROM V

Converting XML Data into Virtual DataPort Compound Types

By combining the CAST and CREATETYPEFROMXML functions you can create fields with compound types (register or array) in a view from XML data.

Note

The function CREATETYPEFROMXML is deprecated and may be removed in future versions of the Denodo Platform. Instead, create an XML data source with a route of type from variable and pass the XML document to this data source.

For example: suppose we have a view V with a field of type xml called PERSONAL_DATA_XML. The data contained in this field has the following structure:

<person>
    <name> </name>
    <age> </age>
</person>

Now consider the following expression:

CREATE VIEW PERSON AS
    SELECT CAST(
        CREATETYPEFROMXML(
              'personaldata_type'
            , '<person><name> John Smith </name><age>25</age></person>'
        ), PERSONAL_DATA_XML
    ) PERSONALDATA
FROM V

The type of the derived field PERSONALDATA of the new view PERSON is personaldata_type. This type is a register type made up of the fields name (text type) and age (long type).

The second parameter of the CREATETYPEFROMXML function must be an example of the values contained in the PERSONAL_DATA_XML field of the view V.

CREATETYPEFROMXML can also create array types. This will happen when the XML data passed to the first parameter and second parameters has repeated elements. E.g.:

<titles>
   <title lang="en"> </title>
   <title lang="en"> </title>
</titles>

In this case, the type created by CREATETYPEFROMXML is a register of arrays. Each component of the array is a register with two components: title and lang.

Converting XML-type data into Virtual DataPort compound-type data allows the data in XML code to be combined with data from other relations. For example, suppose you have a view RISK_LEVEL with two fields called age (long type) and risk (double type), which includes some type of risk index calculated according to the age of an individual. It would be possible to run a join operation between the PERSON view and the RISK_LEVEL view using the age field of RISK_LEVEL and the age field of the PERSONALDATA field in the PERSON view.