XML Functions
You are looking at an older version of the documentation. The latest version is found here.
XML functions provide functionality for working with XML data.
JSONTOXML
This function returns an XML document from JSON.
Syntax
JSONTOXML(rootElementName, JSON)
rootElementName
is a string, JSON is in {clob, blob}
. Return value is xml
.
The appropriate UTF encoding (8, 16LE. 16BE, 32LE, 322625BE) will be detected for JSON blobs. If another encoding is used, see the to_chars
function.
The result is always a well-formed XML document.
The mapping to XML uses the following rules:
- The current element name is initially the
rootElementName
and becomes the object value name as the JSON structure is traversed; - All element names must be valid XML 1.1 names. Invalid names are fully escaped according to the SQLXML specification;
- Each object or primitive value will be enclosed in an element with the current name;
- Unless an array value is a root, it will not be enclosed in an additional element;
- Null values will be represented by an empty element with the attribute
xsi:nil="true"
.
Example
Sample JSON to XML for JSONTOXML('person', x)
JSON
"firstName" : "John" , "children" : [ "Randy", "Judy" ]
XML
<?xml version="1.0" ?>
<person>
<firstName>John</firstName>
<children>Randy</children>
<children>Judy<children>
</person>
Sample JSON to XML for JSONToXml('person', x) with a root array
JSON
[{"firstName" : "George" }, { "firstName" : "Jerry" }]
XML
<?xml version="1.0" ?>
<person>
<person>
<firstName>George</firstName>
</person>
<person>
<firstName>Jerry</firstName>
</person>
</person>
Please note that there is an extra "person" wrapping element in this example to keep the XML well-formed.
XMLCOMMENT
This function returns an XML comment.
Syntax
XMLCOMMENT(comment)
comment
is a string;- Return value is XML.
Example
SELECT XMLCOMMENT('Lorem ipsum ');;
This call will return the following:
<!-- Lorem ipsum -->
XMLCONCAT
This function returns an XML with the concatenation of the given XML elements.
Syntax
XMLCONCAT(content [, content]*)
content
is xml;- Return value is xml.
Example
SELECT XMLCONCAT(
XMLELEMENT("name", 'Dr Jekyll'),
XMLELEMENT("name", 'Mr Hyde')
);;
This call will return the following:
<name>Dr Jekyll</name><name>Mr Hyde</name>
If a value is null, it will be ignored. If all values are null, null will be returned.
XMLELEMENT
This function returns an XML element with the given name and content.
Syntax
XMLELEMENT([NAME] name [, <NSP>] [, <ATTR>][, content]*)
ATTR:=XMLATTRIBUTES (exp [AS name] [, exp [AS name]]*)
NSP:=XMLNAMESPACES ((uri AS prefix | DEFAULT uri | NO DEFAULT))+
- If the
content
value is of a type other than XML, it will be escaped when added to the parent element; - Null content values are ignored;
- Whitespace in XML or the string values of the content is preserved, but no whitespace is added between content values;
XMLNAMESPACES
is used to provide namespace information;NO DEFAULT
is equivalent to defining the default namespace to the null uri -xmlns=""
;- Only one
DEFAULT
orNO DEFAULT
namespace item may be specified; - The namespace prefixes
xmlns
andxml
are reserved; name
andprefix
are identifiers;uri
is a string literal;content
can be any type;- Return value is XML and is valid for use in places where an XML document (e.g. element content) is expected.
Example
SELECT
XMLELEMENT(NAME "MI6",
XMLNAMESPACES('uri' as ns1),
XMLELEMENT("head", 'M'),
XMLELEMENT("agent",
XMLATTRIBUTES('007' AS "id", 'Martini' AS "drink"),
'Bond, James'
)
);;
This call will return the following:
<MI6 xmlns:ns1="uri">
<head>M</head>
<agent id="007" drink="Martini">Bond, James</agent>
</MI6>
XMLESCAPENAME
This function can be used to escape a name according to ISO 9075 (a valid XML identifier).
Syntax
XMLESCAPENAME(name, true)
- Name is a string;
- The second argument denotes whether we need a full or partial escape;
- The return value is the escaped name.
Example
SELECT XMLESCAPENAME('xml_name_:XML_name_xls GetsEsc@ped', TRUE);;
This call returns the following:
_u0078_ml_name__u003A_XML_name_u005F_xls_u0020_GetsEsc_u0040_ped
XMLFOREST
This function returns a concatenation of XML elements for each content item.
Syntax
XMLFOREST(content [<NSP>] [, AS name] [, content [AS name]]*)
See XMLELEMENT for the definition of NSP - XMLNAMESPACES
name
is an identifier;content
can be any type;- Return value is XML;
- If a name is not supplied for a content item, the expression must be a column reference. In this case, the element name will be a partially escaped version of the column name.
Example
SELECT XMLFOREST(
XMLNAMESPACES('http://tempuri.org/' AS ns1),
XMLELEMENT(family_name, 'Huxley') AS family,
XMLELEMENT(person, 'Aldous Huxley') AS familyMember
);;
This call returns the following:
<family xmlns:ns1="uri">
<family_name>Huxley</family_name>
</family>
<familyMember>
<person>Aldous Huxley</person>
</familyMember>
XMLPARSE
This function returns an XML type representation of the string value expression.
Syntax
XMLPARSE((DOCUMENT|CONTENT) expr [WELLFORMED])
expr
in {string, clob, blob};- Return value is xml.
Examples
Simple XMLPARSE
SELECT XMLPARSE(CONTENT '
<chipmunk>Chip</chipmunk>
<chipmunk>Dale</chipmunk>
');;
This call returns the following:
<chipmunk>Chip</chipmunk>
<chipmunk>Dale</chipmunk>
XMLPARSE with DOCUMENT
and WELLFORMED
SELECT XMLPARSE(DOCUMENT '
<ResqueRangers xmlns:ns1="uri">
<chipmunk>Chip</chipmunk>
<chipmunk>Dale</chipmunk>
</ResqueRangers>
' WELLFORMED);;
This call returns the following:
<ResqueRangers xmlns:ns1="uri">
<chipmunk>Chip</chipmunk>
<chipmunk>Dale</chipmunk>
</ResqueRangers>
There are two points to keep in mind when using this form of XMLPARSE
:
- If
DOCUMENT
is specified, the expression must have a single root element and may or may not contain an XML declaration; - If
WELLFORMED
is specified, validation is skipped; this is especially useful forCLOB
andBLOB
types which are known to be already valid.
This function can also be used to pass a file as an expression:
XMLPARSE(DOCUMENT f.file)
XMLPI
This function returns an XML processing instruction.
Syntax
XMLPI([NAME] name [, content])
name
is an identifier;content
is a string- Return value is
XML
.
Example
SELECT XMLPI(NAME "Instruction", 'Push the red button');;
This call returns the following:
<?Instruction Push the red button?>
XMLQUERY
This function is part of the SQL/XML 2006 specification and returns the XML result from evaluating the given XQuery.
Syntax
XMLQUERY([<NSP>] xquery [<PASSING>] [(NULL|EMPTY) ON EMPTY]])
PASSING:=PASSING exp [AS name] [, exp [AS name]]*
- See XMLELEMENT for the definition of NSP - XMLNAMESPACES;
xquery
is a string;- Return value is XML.
Examples
Simple XMLQUERY
SELECT XMLQUERY(
'root/name/text()' PASSING CAST('
<root>
<name>John</name>
<name>Paul</name>
<name>George</name>
<name>Ringo</name>
</root>' AS xml)
);;
This call returns the following:
John Paul George Ringo
XMLQUERY with ON EMPTY clause
The ON EMPTY
clause is used to specify the result when the evaluated sequence is empty. EMPTY ON EMPTY
, the default, returns an empty XML result, and NULL ON EMPTY
returns a null result.
SELECT XMLQUERY(
'root/n/text()' PASSING CAST('
<root>
<name>John</name>
<name>Paul</name>
<name>George</name>
<name>Ringo</name>
</root>' AS xml)
NULL ON EMPTY);;
This call returns the following:
null
Namespaces may also be directly declared in the XQuery prologue.
The optional PASSING
clause is used to provide a context item which does not have a name and named global variable values. If the XQuery uses a context item and none is provided, then an exception will be raised. Only one context item may be specified, and it should be an XML type. All non-context non-XML passing values will be converted to an appropriate XML type.
Depending on the XQuery, it may be liable for document projection which will help to use memory more effectively - please see the chapter on XQuery Optimization for details.
XMLSERIALIZE
This function returns a character-type representation of the XML expression.
Syntax
XMLSERIALIZE([(DOCUMENT|CONTENT)] xml [AS datatype])
- Return value matches data type;
- The data type may be only a character type (string, varchar, clob);
content
is the default;- If
DOCUMENT
is specified, the expression must have a single root element; - If the XML is not a valid document or fragment, an exception is raised.
Example
SELECT XMLSERIALIZE(DOCUMENT '
<Wars-of-the-Roses xmlns:ns1="uri">
<House>Lancaster</House>
<House>York</House>
</Wars-of-the-Roses>'
AS CLOB);;
This call returns the following:
<Wars-of-the-Roses xmlns:ns1="uri"><House>Lancaster</House><House>York</House></Wars-of-the-Roses>
XSLTRANSFORM
This function applies an XSL stylesheet to the given document.
Syntax
XSLTRANSFORM(doc, xsl)
doc
,xsl
in {string, clob, xml};- The return value is a clob.
Example
SELECT XSLTRANSFORM(
'<bookstore>
<book genre="novel" ISBN="1-861003-11-0">
<title>Pride And Prejudice</title>
<author>
<first-name>Jane</first-name>
<last-name>Austen</last-name>
</author>
<price>8.99</price>
</book>
</bookstore>',
'<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:template match="book">
<book-data>
ISBN: <xsl:value-of select="@ISBN"/>
Title: <xsl:value-of select="title"/>
Author: <xsl:apply-templates select="author"/>
Price: <xsl:value-of select="price"/>
</book-data>
</xsl:template>
<xsl:template match="author">
<xsl:value-of select="first-name"/> <xsl:value-of select="first-last"/>
</xsl:template>
</xsl:stylesheet>'
);;
This call returns the following:
<book-data>
ISBN: 1-861003-11-0
Title: Pride And Prejudice
Author: Jane
Price: 8.99
</book-data>
Please note that if either argument is null, the result is null.
XPATHVALUE
This function applies the XPATH expression to the document and returns a string value for the first matching result.
Syntax
XPATHVALUE(doc, xpath)
doc
andxpath
in {string, clob, xml};- Return value is a string.
Please note that matching a non-text node will still produce a string result, which includes all descendant text nodes.
Example
XML value
<?xml version="1.0" ?>
<ns1:return xmlns:ns1="http://com.test.ws/exampleWebService">Hello<x> World</x></ns1:return>
Function
SELECT XPATHVALUE(value, '/*[local-name()="return"])
This call results in 'Hello World'.