TABLE Customer (
CustomerId integer PRIMARY KEY,
CustomerName varchar(25),
ContactName varchar(25)
Address varchar(50),
City varchar(25),
PostalCode varchar(25),
Country varchar(25),
);
XML functions
XML functions provide functionality for working with XML data. For more information, see JSONTOXML in JSON functions.
Examples provided with XML functions use the following table structure
with Data
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
87 |
Wartian Herkku |
Pirkko Koskitalo |
Torikatu 38 |
Oulu |
90110 |
Finland |
88 |
Wellington Importadora |
Paula Parente |
Rua do Mercado, 12 |
Resende |
08737-363 |
Brazil |
89 |
White Clover Markets |
Karl Jablonski |
305 - 14th Ave. S. Suite 3B |
Seattle |
98128 |
USA |
Cast to or from XML.
XMLCAST(expression AS type)
Expression or type must be XML.
The return value will be typed as type
.
This is the same functionality that XMLTABLE
uses to convert values to the desired runtime type,
except that XMLCAST
does not work with array type targets.
Returns an XML comment.
XMLCOMMENT(comment)
Comment is a string. Return value is XML.
Returns an XML with the concatenation of the given XML types.
XMLCONCAT(content [, content]*)
Content is XML. Return value is XML.
If a value is null, it will be ignored. If all values are null, null is returned.
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>
Returns an XML element with the given name and content.
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 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 a 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.
Name, 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>
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>
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>
Returns an concatenation of XML elements for each content item.
XMLFOREST(content [AS name] [, <NSP>] [, content [AS name]]*)
For the definition of NSP - XMLNAMESPACES, see See XMLELEMENT in XML functions.
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 which case the element name will be a partially escaped version of the column name.
You can use the XMLFOREST to simplify the declaration of multiple XMLELEMENTS. The 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 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
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>
Returns an XML type representation of the string value expression.
XMLPARSE((DOCUMENT|CONTENT) expr [WELLFORMED])
expr in {string, clob, blob, 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);
Will return a SQLXML with contents
===============================================================
<customer><name>Wartian Herkku</name><contact>Pirkko Koskitalo</contact></customer>
Returns an XML processing instruction.
XMLPI([NAME] name [, content])
Name is an identifier. Content is a string. Return value is XML.
Returns the XML result from evaluating the given xquery.
XMLQUERY([<NSP>] xquery [<PASSING>] [(NULL|EMPTY) ON EMPTY]]
PASSING:=PASSING exp [AS name] [, exp [AS name]]*
For the definition of NSP - XMLNAMESPACES, see XMLELEMENT in XML functions.
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 will be returned if the context item evaluates to null.
The ON EMPTY clause is used to specify the result when the evaluted 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.
XMLQUERY is part of the SQL/XML 2006 specification.
For more information, see XMLTABLE in FROM clause.
Note
|
See also XQuery optimization. |
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]]*
For the definition of NSP - XMLNAMESPACES, see XMLELEMENT in XML functions.
Namespaces can 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.
Note
|
See also XQuery optimization. |
Returns a character type representation of the XML expression.
XMLSERIALIZE([(DOCUMENT|CONTENT)] xml [AS datatype] [ENCODING enc] [VERSION ver] [(INCLUDING|EXCLUDING) XMLDECLARATION])
Return value matches datatype. If no datatype is specified, then clob will be assumed.
The type may be character (string, varchar, clob) or binary (blob, varbinar). CONTENT is the default. If DOCUMENT is specified and the XML is not a valid document or fragment, then an exception is raised.
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.
See the following example that 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)
Returns XML text.
XMLTEXT(text)
text is a string. Return value is XML.
Applies an XSL stylesheet to the given document.
XSLTRANSFORM(doc, xsl)
Doc, XSL in {string, clob, xml}. Return value is a clob.
If either argument is null, the result is null.
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. For more information, see XMLQUERY in XML functions.
XPATHVALUE(doc, xpath)
Doc in {string, clob, blob, xml}. xpath is string. Return value is a string.
Matching a non-text node will still produce a string result, which includes all descendant text nodes. If a single element is matched that is marked with xsi:nil, then null will be returned.
When the input document utilizes namespaces, it is sometimes necessary to specify XPATH that ignores namespaces:
<?xml version="1.0" ?>
<ns1:return xmlns:ns1="http://com.test.ws/exampleWebService">Hello<x> World</x></return>
Function:
xpathValue(value, '/*[local-name()="return"]')
Results in Hello World
With following table and its contents
Table {
x string,
y integer
}
data like ['a', 1], ['a', 2], ['b', 3], ['b', 4], if you want generate a XML that looks like
<root>
<x>
a
<y>1</y>
<y>2</y>
</x>
<x>
b
<y>3</y>
<y>4</y>
</x>
</root>
use the SQL statement in Teiid as below
select xmlelement(name "root", xmlagg(p))
from (select xmlelement(name "x", x, xmlagg(xmlelement(name "y", y)) as p from tbl group by x)) as v
For more examples, see http://oracle-base.com/articles/misc/sqlxml-sqlx-generating-xml-content-using-sql.php