Skip to main content
Skip table of contents

XML Functions

XML functions provide functionality for working with XML data.

JSONTOXML

This function returns an XML document from JSON.

Syntax

XML 
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

XML 
"firstName" : "John" , "children" : [ "Randy", "Judy" ]

XML

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

SQL 
[{"firstName" : "George" }, { "firstName" : "Jerry" }]

XML

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

SQL 
XMLCOMMENT(comment)
  • comment is a string;
  • Return value is XML.

Example

XML 
SELECT XMLCOMMENT('Lorem ipsum ');;


This call will return the following:

XML 
<!-- Lorem ipsum -->

XMLCONCAT

This function returns an XML with the concatenation of the given XML elements.

Syntax

SQL 
XMLCONCAT(content [, content]*)
  • content is xml;
  • Return value is xml.

Example

SQL 
SELECT XMLCONCAT(
	XMLELEMENT("name", 'Dr Jekyll'),
	XMLELEMENT("name", 'Mr Hyde')
);;

This call will return the following:

XML 
<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

XML 
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 or NO DEFAULT namespace item may be specified;
  • The namespace prefixes xmlns and xml are reserved;
  • name and prefix 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

SQL 
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:

XML 
<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

XML 
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

SQL 
SELECT XMLESCAPENAME('xml_name_:XML_name_xls GetsEsc@ped', TRUE);;

This call returns the following:

XML 
_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

XML 
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

XML 
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:

XML 
<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

XML 
XMLPARSE((DOCUMENT|CONTENT) expr [WELLFORMED])
  • expr in {string, clob, blob};
  • Return value is xml.

Examples

Simple XMLPARSE

SQL 
SELECT XMLPARSE(CONTENT '
	<chipmunk>Chip</chipmunk>
	<chipmunk>Dale</chipmunk>
');;

This call returns the following:

XML 
<chipmunk>Chip</chipmunk>
<chipmunk>Dale</chipmunk>

XMLPARSE with DOCUMENT and WELLFORMED

SQL 
SELECT XMLPARSE(DOCUMENT '
	<ResqueRangers xmlns:ns1="uri">
		<chipmunk>Chip</chipmunk>
		<chipmunk>Dale</chipmunk>
	</ResqueRangers>
' WELLFORMED);;

This call returns the following:

XML 
<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 for CLOB and BLOB types which are known to be already valid.

This function can also be used to pass a file as an expression:

XML 
XMLPARSE(DOCUMENT f.file)

XMLPI

This function returns an XML processing instruction.

Syntax

XML 
XMLPI([NAME] name [, content])
  • name is an identifier;
  • content is a string
  • Return value is XML.

Example

SQL 
SELECT XMLPI(NAME "Instruction", 'Push the red button');;

This call returns the following:

XML 
<?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

XML 
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

SQL 
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: 

XML 
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.

SQL 
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:

XML 
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

XML 
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

SQL 
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:

XML 
<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

XML 
XSLTRANSFORM(doc, xsl)


  • doc, xsl in {string, clob, xml};
  • The return value is a clob.

Example

SQL 
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: 

XML 
<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

XML 
XPATHVALUE(doc, xpath)
  • doc and xpath 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 
<?xml version="1.0" ?>
	<ns1:return xmlns:ns1="http://com.test.ws/exampleWebService">Hello<x> World</x></ns1:return>

Function 

SQL 
SELECT XPATHVALUE(value, '/*[local-name()="return"])

This call results in 'Hello World'.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.

Contact Support Close