XML Functions¶
The XML functions create and transform values of type xml
.
The XML functions are:
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.
By default the XQuery expression allows any protocol such as ‘ftp’, ‘file’, ‘http’, etc. To restrict this behavior set
the property com.denodo.vdb.server.xmlFunctions.allowedProtocols
to a comma separated list of allowed schemas, for example:
SET 'com.denodo.vdb.server.xmlFunctions.allowedProtocols'='file, http';
To disable all protocols execute:
SET 'com.denodo.vdb.server.xmlFunctions.allowedProtocols'='';
To enable all protocols execute:
SET 'com.denodo.vdb.server.xmlFunctions.allowedProtocols'='all';
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. Iftrue
and the selected value is an XML node, the result includes the XML declaration (<?xml version="1.0"
…). Iffalse
or the selected value is not an XML node, the result will not include this declaration.
If the function is delegated to a Oracle, DB2 or SQL Server 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 ofxml value
is text.true
ifxml value
is a path to the xml file.false
otherwise.is path to XSLT
. Required only if the type ofXSL 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>Data Catalog</name>
</product>
<product>
<id>3</id>
<name>Scheduler</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>Data Catalog</product>
<product>Scheduler</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.