XML functions provide functionality for working with XML data.

JSONTOXML

This function returns an XML document from JSON.

Syntax

JSONTOXML(rootElementName, JSON)
XML

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

<?xml version="1.0" ?>
	<person>
		<firstName>John</firstName>
		<children>Randy</children>
		<children>Judy<children>
	</person>
XML


Sample JSON to XML for JSONToXml('person', x) with a root array

JSON

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

XML

<?xml version="1.0" ?>
<person>
	<person>
		<firstName>George</firstName>
	</person>
	<person>
		<firstName>Jerry</firstName>
	</person>
</person>
XML

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)
SQL
  • comment is a string;
  • Return value is XML.

Example

SELECT XMLCOMMENT('Lorem ipsum ');;
XML


This call will return the following:

<!-- Lorem ipsum -->
XML

XMLCONCAT

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

Syntax

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

Example

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

This call will return the following:

<name>Dr Jekyll</name><name>Mr Hyde</name>
XML

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))+
XML
  • 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

SELECT
	XMLELEMENT(NAME "MI6",
		XMLNAMESPACES('uri' as ns1),
		XMLELEMENT("head", 'M'),
		XMLELEMENT("agent",
			XMLATTRIBUTES('007' AS "id", 'Martini' AS "drink"),
			'Bond, James' 
		)
	);;
SQL

This call will return the following:

<MI6 xmlns:ns1="uri">
	<head>M</head>
	<agent id="007" drink="Martini">Bond, James</agent>
</MI6>
XML

XMLESCAPENAME

This function can be used to escape a name according to ISO 9075 (a valid XML identifier).

Syntax

XMLESCAPENAME(name, true)
XML
  • The 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);;
SQL

This call returns the following:

_u0078_ml_name__u003A_XML_name_u005F_xls_u0020_GetsEsc_u0040_ped
XML

XMLFOREST

This function returns a concatenation of XML elements for each content item.

Syntax

XMLFOREST(content [<NSP>] [, AS name] [, content [AS name]]*)
XML

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, which means 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
);;
XML

This call returns the following:

<family xmlns:ns1="uri">
	<family_name>Huxley</family_name>
</family>
<familyMember>
	<person>Aldous Huxley</person>
</familyMember>
XML

XMLPARSE

This function returns an XML-type representation of the string value expression.

Syntax

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

Examples

Simple XMLPARSE

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

This call returns the following:

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

XMLPARSE with DOCUMENT and WELLFORMED

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

This call returns the following:

<ResqueRangers xmlns:ns1="uri">
	<chipmunk>Chip</chipmunk>
	<chipmunk>Dale</chipmunk>
</ResqueRangers>
XML

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:

XMLPARSE(DOCUMENT f.file)
XML

XMLPI

This function returns an XML processing instruction.

Syntax

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

Example

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

This call returns the following:

<?Instruction Push the red button?>
XML

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]]*
XML
  • 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)
);;
SQL

This call returns the following: 

John Paul George Ringo
XML

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);;
SQL

This call returns the following:

null
XML

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.

XMLSERIALIZE

This function returns a character-type representation of the XML expression.

Syntax

XMLSERIALIZE([(DOCUMENT|CONTENT)] xml [AS datatype])
XML
  • 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);;
SQL

This call returns the following:

<Wars-of-the-Roses xmlns:ns1="uri"><House>Lancaster</House><House>York</House></Wars-of-the-Roses>
XML

XSLTRANSFORM

This function applies an XSL stylesheet to the given document.

Syntax

XSLTRANSFORM(doc, xsl)
XML


  • 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>'
);;
SQL

This call returns the following: 

<book-data>
	ISBN: 1-861003-11-0
	Title: Pride And Prejudice
	Author: Jane
	Price: 8.99
</book-data>
XML

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)
XML
  • doc and xpath in {string, clob, xml};
  • The 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>
XML

Function 

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

This call results in 'Hello World'.