3.4. Scalar Functions

XMLCOMMENT

XMLCOMMENT(comment)

Copy to Clipboard Toggle word wrap

Returns an XML comment.

comment is a string. Return value is XML.

XMLCONCAT

XMLCONCAT(content [, content]*)

Copy to Clipboard Toggle word wrap

Returns XML with the concatenation of the given XML types. If a value is null, it will be ignored. If all values are null, null is returned. This is how you concatenate two or more XML fragments:

					SELECT XMLCONCAT( XMLELEMENT("name", CustomerName), XMLPARSE(CONTENT '
					<a>
						b
					</a>' WELLFORMED) ) FROM Customer c WHERE c.CustomerID = 87; ========================================================== 
					<name>
						Wartian Herkku
					</name>
					<a>
						b
					</a>
				

Copy to Clipboard Toggle word wrap

content is XML. Return value is XML.

XMLELEMENT

XMLELEMENT([NAME] name [, <NSP>] [, <ATTR>][, content]*)
ATTR:=XMLATTRIBUTES(exp [AS name] [, exp [AS name]]*)
NSP:=XMLNAMESPACES((uri AS prefix | DEFAULT uri | NO DEFAULT))+

Copy to Clipboard Toggle word wrap

Returns an XML element with the given name and content. 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.

If an attribute name is not supplied, the expression must be a column reference, in which case the attribute name will be the column name. Null attribute values are ignored.

For example, with an xml_value of <doc/>,

XMLELEMENT(NAME "elem", 1, '<2/>', xml_value)

Copy to Clipboard Toggle word wrap

returns

<elem>1&lt;2/&gt;<doc/><elem/>

Copy to Clipboard Toggle word wrap

name and prefix are identifiers. uri is a string literal. content can be any type. Return value is XML. The return value is valid for use in places where a document is expected.

				SELECT XMLELEMENT("name", CustomerName)
FROM   Customer c
WHERE  c.CustomerID = 87;
 
==========================================================
<name>Wartian Herkku</name>
"Multiple Columns"
SELECT XMLELEMENT("customer", 
          XMLELEMENT("name", c.CustomerName),
          XMLELEMENT("contact", c.ContactName))
FROM   Customer c
WHERE  c.CustomerID = 87;
 
==========================================================
<customer><name>Wartian Herkku</name><contact>Pirkko Koskitalo</contact></customer>
"Columns as Attributes"
SELECT XMLELEMENT("customer", 
          XMLELEMENT("name", c.CustomerName,
            XMLATTRIBUTES(
                  "contact" as c.ContactName,
                  "id" as c.CustomerID
            )
          )
       )
FROM   Customer c
WHERE  c.CustomerID = 87;
 
==========================================================
<customer><name contact="Pirkko Koskitalo" id="87">Wartian Herkku</name></customer>

Copy to Clipboard Toggle word wrap

XMLFOREST

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

Copy to Clipboard Toggle word wrap

Returns an concatenation of XML elements for each content item. See XMLELEMENT for the definition of NSP. If a name is not supplied for a content item, the expression must be a column reference, in which case the element name will be a partially escaped version of the column name.

name is an identifier. content can be any type. Return value is XML.

You can use XMLFORREST to simplify the declaration of multiple XMLELEMENTS, XMLFOREST function allows you to process multiple columns at once:

SELECT XMLELEMENT("customer", 
          XMLFOREST(
             c.CustomerName AS "name",
             c.ContactName AS "contact"
          ))
FROM   Customer c
WHERE  c.CustomerID = 87;
 
==========================================================
<customer><name>Wartian Herkku</name><contact>Pirkko Koskitalo</contact></customer>
XMLAGG

XMLAGG is an aggregate function, that takes a collection of XML elements and returns an aggregated XML document.
XMLAGG(xml)

From above example in XMLElement, each row in the Customer table table will generate row of XML if there are multiple rows matching the criteria. That will generate a valid XML, but it will not be well formed, because it lacks the root element. XMLAGG can used to correct that
"Example"
SELECT XMLELEMENT("customers",
         XMLAGG(
           XMLELEMENT("customer", 
             XMLFOREST(
               c.CustomerName AS "name",
               c.ContactName AS "contact"
             )))
FROM   Customer c
 
 
==========================================================
<customers>
<customer><name>Wartian Herkku</name><contact>Pirkko Koskitalo</contact></customer>
<customer><name>Wellington Importadora</name><contact>Paula Parente</contact></customer>
<customer><name>White Clover Markets</name><contact>Karl Jablonski</contact></customer>
</customers>

Copy to Clipboard Toggle word wrap

XMLPARSE

XMLPARSE((DOCUMENT|CONTENT) expr [WELLFORMED])

Copy to Clipboard Toggle word wrap

Returns an XML type representation of the string value expression. If DOCUMENT is specified, then the expression must have a single root element and may or may not contain an XML declaration. If WELLFORMED is specified then validation is skipped; this is especially useful for CLOB and BLOB known to already be valid.

expr in {string, clob, blob and varbinary}. Return value is XML.

If DOCUMENT is specified then the expression must have a single root element and may or may not contain an XML declaration. If WELLFORMED is specified then validation is skipped; this is especially useful for CLOB and BLOB known to already be valid.

SELECT XMLPARSE(CONTENT '<customer><name>Wartian Herkku</name><contact>Pirkko Koskitalo</contact></customer>' WELLFORMED);
 

Copy to Clipboard Toggle word wrap

Will return a SQLXML with contents:

<customer><name>Wartian Herkku</name><contact>Pirkko Koskitalo</contact></customer>
 

Copy to Clipboard Toggle word wrap

XMLPI

XMLPI([NAME] name [, content])

Copy to Clipboard Toggle word wrap

Returns an XML processing instruction.

name is an identifier. content is a string. Return value is XML.

XMLQUERY

XMLQUERY([<NSP>] xquery [<PASSING>] [(NULL|EMPTY) ON EMPTY]]
PASSING:=PASSING exp [AS name] [, exp [AS name]]*

Copy to Clipboard Toggle word wrap

Returns the XML result from evaluating the given xquery. See XMLELEMENT for the definition of NSP. Namespaces may also be directly declared in the XQuery prolog.

The optional PASSING clause is used to provide the 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 should be an XML type. All non-context non-XML passing values will be converted to an appropriate XML type.

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. NULL ON EMPTY returns a null result.

xquery in string. Return value is XML.

Note

XMLQUERY is part of the SQL/XML 2006 specification.

See also XMLTABLE.

XMLEXISTS

Returns true if a non-empty sequence would be returned by evaluating the given xquery.

XMLEXISTS([<NSP>] xquery [<PASSING>]]
 
PASSING:=PASSING exp [AS name] [, exp [AS name]]*

Copy to Clipboard Toggle word wrap

Namespaces may also be directly declared in the xquery prolog.

The optional PASSING clause is used to provide the 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 should be an XML type. All non-context non-XML passing values will be converted to an appropriate XML type. Null/Unknown will be returned if the context item evaluates to null.

xquery in string. Return value is boolean.

XMLEXISTS is part of the SQL/XML 2006 specification.

XMLSERIALIZE

XMLSERIALIZE([(DOCUMENT|CONTENT)] xml [AS datatype] [ENCODING enc] [VERSION ver] [(INCLUDING|EXCLUDING) XMLDECLARATION])

Copy to Clipboard Toggle word wrap

Returns a character type representation of the XML expression.

datatype may be character (string, varchar, clob) or binary (blob, varbinary). CONTENT is the default. If DOCUMENT is specified and the XML is not a valid document or fragment, then an exception is raised.

Return value matches data type. If no data type is specified, then CLOB will be assumed.

The encoding enc is specified as an identifier. A character serialization may not specify an encoding. The version ver is specified as a string literal. If a particular XMLDECLARATION is not specified, then the result will have a declaration only if performing a non UTF-8/UTF-16 or non version 1.0 document serialization or the underlying XML has an declaration. If CONTENT is being serialized, then the declaration will be omitted if the value is not a document or element.

The following example produces a BLOB of XML in UTF-16 including the appropriate byte order mark of FE FF and XML declaration:

XMLSERIALIZE(DOCUMENT value AS BLOB ENCODING "UTF-16" INCLUDING XMLDECLARATION)

Copy to Clipboard Toggle word wrap

XSLTRANSFORM

XSLTRANSFORM(doc, xsl)

Copy to Clipboard Toggle word wrap

Applies an XSL stylesheet to the given document.

doc and xsl in {string, clob, xml}. Return value is a CLOB. If either argument is null, the result is null.

XPATHVALUE

XPATHVALUE(doc, xpath)

Copy to Clipboard Toggle word wrap

Applies the XPATH expression to the document and returns a string value for the first matching result. For more control over the results and XQuery, use the XMLQUERY function.

Matching a non-text node will still produce a string result, which includes all descendant text nodes.

doc and xpath in {string, clob, xml}. Return value is a string.

When the input document utilizes namespaces, it is sometimes necessary to specify XPATH that ignores namespaces. For example, given the following XML,

<?xml version="1.0" ?>
  <ns1:return xmlns:ns1="http://com.test.ws/exampleWebService">Hello<x> World</x></return>

Copy to Clipboard Toggle word wrap

the following function results in 'Hello World'.

xpathValue(value, '/*[local-name()="return"])

Copy to Clipboard Toggle word wrap

JSONTOXML

JSONTOXML(rootElementName, json)

Copy to Clipboard Toggle word wrap

Returns an XML document from JSON. The appropriate UTF encoding (8, 16LE. 16BE, 32LE, 32BE) will be detected for JSON BLOBS. If another encoding is used, see the TO_CHARS function (see Section 3.4.5, “String Functions”).

rootElementName is a string, json is in {clob, blob}. Return value is XML. 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 the 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"
• Boolean and numerical value elements will have the attribute xsi:type set to boolean and decimal respectively.

Example 3.1. Sample JSON to XML for jsonToXml('person', x)

JSON:

{ "firstName" : "John" , "children" : [ "Randy", "Judy" ] }

Copy to Clipboard Toggle word wrap

XML:

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

Copy to Clipboard Toggle word wrap

Example 3.2. Sample JSON to XML for jsonToXml('person', x) with a root array.

JSON:

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

Copy to Clipboard Toggle word wrap

XML (Notice there is an extra "person" wrapping element to keep the XML well-formed):

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

Copy to Clipboard Toggle word wrap

JSON:

Example 3.3. Sample JSON to XML for jsonToXml('root', x) with an invalid name.

{"/invalid" : "abc" }

Copy to Clipboard Toggle word wrap

XML:

Example 3.4. Sample JSON to XML for jsonToXml('root', x) with an invalid name.

<?xml version="1.0" ?>
<root>
  <_u002F_invalid>abc</_u002F_invalid>
</root>

Copy to Clipboard Toggle word wrap

JSONARRAY

JSONARRAY(value...)

Copy to Clipboard Toggle word wrap

Returns a JSON array.

value is any object convertable to a JSON value (see Section 3.4.17, “Conversion to JSON”). Return value is a CLOB marked as being valid JSON. Null values will be included in the result as null literals.

For example:

jsonArray('a"b', 1, null, false, {d'2010-11-21'})

Copy to Clipboard Toggle word wrap

returns

["a\"b",1,null,false,"2010-11-21"]

Copy to Clipboard Toggle word wrap

JSONOBJECT

JSONARRAY(value [as name] ...)

Copy to Clipboard Toggle word wrap

Returns a JSON object.

value is any object convertable to a JSON value (see Section 3.4.17, “Conversion to JSON”). Return value is a clob marked as being valid JSON.

Null values will be included in the result as null literals.

If a name is not supplied and the expression is a column reference, the column name will be used otherwise exprN will be used where N is the 1-based index of the value in the JSONARRAY expression.

For example:

jsonObject('a"b' as val, 1, null as "null")

Copy to Clipboard Toggle word wrap

returns

{"val":"a\"b","expr2":1,"null":null}

Copy to Clipboard Toggle word wrap

JSONPARSE

JSONPARSE(value, wellformed)

Copy to Clipboard Toggle word wrap

Validates and returns a JSON result.

value is blob with an appropriate JSON binary encoding (UTF-8, UTF-16, or UTF-32) or clob. wellformed is a boolean indicating that validation should be skipped. Return value is a CLOB marked as being valid JSON.

A null for either input will return null.

jsonParse('"a"')

Copy to Clipboard Toggle word wrap

Conversion Functions

ST_GeomFromText

Returns a geometry from a Clob in WKT format.

 ST_GeomFromText(text [, srid]) 

Copy to Clipboard Toggle word wrap

text is a clob, srid is an optional integer. Return value is a geometry.

ST_GeomFromWKB/ST_GeomFromBinary

Returns a geometry from a blob in WKB format.

 
ST_GeomFromWKB(bin [, srid])

Copy to Clipboard Toggle word wrap

bin is a blob, srid is an optional integer. Return value is a geometry.

ST_GeomFromGeoJSON

Returns a geometry from a Clob in GeoJSON format.

 
ST_GeomFromGeoJson(text [, srid]) 

Copy to Clipboard Toggle word wrap

text is a clob, srid is an optional integer. Return value is a geometry.

ST_GeomFromGML

Returns a geometry from a Clob in GML2 format.

 
ST_GeomFromGML(text [, srid]) 

Copy to Clipboard Toggle word wrap

text is a clob, srid is an optional integer. Return value is a geometry.

ST_AsText

 
ST_GeomAsText(geom) 

Copy to Clipboard Toggle word wrap

geom is a geometry. Return value is clob in WKT format.

ST_AsBinary

 
ST_GeomAsBinary(geom) 

Copy to Clipboard Toggle word wrap

geom is a geometry. Return value is a blob in WKB format.

ST_AsGeoJSON

 
ST_GeomAsGeoJSON(geom) 

Copy to Clipboard Toggle word wrap

geom is a geometry. Return value is a clob with the GeoJSON value.

ST_AsGML

 
ST_GeomAsGML(geom) 

Copy to Clipboard Toggle word wrap

geom is a geometry. Return value is a clob with the GML2 value.

ST_AsEWKT

 
ST_AsEWKT(geom) 

Copy to Clipboard Toggle word wrap

geom is a geometry. Return value is a clob with the EWKT value. The EWKT value is the WKT value with the SRID prefix.

ST_AsKML

 
ST_AsKML(geom) 

Copy to Clipboard Toggle word wrap

geom is a geometry. Return value is a clob with the KML value. The KML value is effectively a simplified GML value and projected into SRID 4326.

Relationship Functions

ST_CONTAINS

Returns true if geom1 contains geom2 contains another.

 
ST_CONTAINS(geom1, geom2) 

Copy to Clipboard Toggle word wrap

geom1, geom2 are geometries. Return value is a boolean.

ST_CROSSES

Returns true if the geometries cross.

 
ST_CROSSES(geom1, geom2) 

Copy to Clipboard Toggle word wrap

geom1, geom2 are geometries. Return value is a boolean.

ST_DISJOINT

Returns true if the geometries are disjoint.

 
ST_DISJOINT(geom1, geom2) 

Copy to Clipboard Toggle word wrap

geom1, geom2 are geometries. Return value is a boolean.

ST_DISTANCE

Returns the distance between two geometries.

 
ST_DISTANCE(geom1, geom2) 

Copy to Clipboard Toggle word wrap

geom1, geom2 are geometries. Return value is a double.

ST_EQUALS

Returns true if the two geometries are spatially equal - the points and order may differ, but neither geometry lies outside of the other.

 
ST_EQUALS(geom1, geom2) 

Copy to Clipboard Toggle word wrap

geom1, geom2 are geometries. Return value is a boolean.

ST_INTERSECTS

Returns true if the geometries intersect.

 
ST_INTERSECT(geom1, geom2) 

Copy to Clipboard Toggle word wrap

geom1, geom2 are geometries. Return value is a boolean.

ST_OVERLAPS

Returns true if the geometries overlap.

 
ST_OVERLAPS(geom1, geom2) 

Copy to Clipboard Toggle word wrap

geom1, geom2 are geometries. Return value is a boolean.

ST_TOUCHES

Returns true if the geometries touch.

 
ST_TOUCHES(geom1, geom2) 

Copy to Clipboard Toggle word wrap

geom1, geom2 are geometries. Return value is a boolean.

Miscellaneous Functions

ST_SRID

Returns the SRID for the geometry.

 
ST_SRID(geom) 

Copy to Clipboard Toggle word wrap

geom is a geometry. Return value is an integer. A 0 value rather than null will be returned for an unknown SRID on a non-null geometry.

ST_SetSRID

Set the SRID for the given geometry.

 
ST_SetSRID(geom, srid) 

Copy to Clipboard Toggle word wrap

geom is a geometry. srid is an integer. Return value is a geometry. Only the SRID metadata of the geometry is modified.

ST_TRANSFORM

Transforms the geometry value from one coordinate system to another.

 
ST_TRANSFORM(geom, srid) 

Copy to Clipboard Toggle word wrap

geom is a geometry. srid is an integer. Return value is a geometry. The srid value and the srid of the geometry value must exist in the SPATIAL_REF_SYS view.

HASROLE

hasRole([roleType,] roleName)

Copy to Clipboard Toggle word wrap

Whether the current caller has the JBoss Data Virtualization data role roleName.

roleName must be a string, the return type is boolean.

The two argument form is provided for backwards compatibility. roleType is a string and must be 'data'.

Role names are case-sensitive and only match JBoss Data Virtualization data roles (see Section 7.1, “Data Roles”). JAAS roles/groups names are not valid for this function, unless there is corresponding data role with the same name.

array_get

array_get(array, index)

Copy to Clipboard Toggle word wrap

Returns the object value at a given array index.

array is the object type, index must be an integer, and the return type is object.

One-based indexing is used. The actual array value must be a java.sql.Array or Java array type. An exception will be thrown if the array value is the wrong type of the index is out of bounds.

array_length

array_length(array)

Copy to Clipboard Toggle word wrap

Returns the length for a given array.

array is the object type, and the return type is integer.

The actual array value must be a java.sql.Array or Java array type. An exception will be thrown if the array value is the wrong type.

uuid

uuid()

Copy to Clipboard Toggle word wrap

Returns a universally unique identifier.

The return type is string.

Generates a type 4 (pseudo randomly generated) UUID using a cryptographically strong random number generator. The format is XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX where each X is a hex digit.