3.4. Scalar Functions

XMLCOMMENT(comment)

XMLCONCAT(content [, content]*)

					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>
				

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

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

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

				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>

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

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>

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

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

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

XMLPI([NAME] name [, content])

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

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

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

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

XSLTRANSFORM(doc, xsl)

XPATHVALUE(doc, xpath)

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

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

JSONTOXML(rootElementName, json)

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

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

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

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

{"/invalid" : "abc" }

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

JSONARRAY(value...)

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

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

JSONARRAY(value [as name] ...)

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

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

JSONPARSE(value, wellformed)

jsonParse('"a"')

hasRole([roleType,] roleName)

array_get(array, index)

array_length(array)

uuid()