This section describes the XML value functions including their purpose, syntax, parameters, and examples.
Returns an XML value that is computed from a collection of rows. The result is the XML concatenation of a list of XML elements, aggregated in the statement containing the XMLAGG-function.
XMLAGG ─── ( ─── XML-value-expression ─────────────────────────► ►─┬───────────────────────────────────────────────────────┬─)──►◄ │ ┌─────────────────── , ──────────────────┐ │ └─ ORDER BY ─▼─┬─┬─────────────┬─col-nm ─┬─┬────────┬─┴─┘ │ ├ table-name. ┤ │ ├─ ASC ◄─┤ │ └ alias. ─────┘ │ └─ DESC ─┘ └─ column-number ─────────┘
Before the aggregation takes place, the XML elements, specified by XML-value-expression, are sorted in ascending or descending order by the values in the specified columns. XML elements are ordered first by the first column specified, then by the second column specified within the ordering established by the first column, then by the third column specified, and so on.
Specifies the name of column.
Specifies the table, view, procedure, or table procedure that includes the named column. For expanded table-name syntax, see Expansion of Table-name.
Specifies the alias associated with the table, view, procedure, or table procedure that includes the named column. The alias must be defined in the FROM parameter of the subquery, query specification, or SELECT statement that includes the XMLAGG function.
Specifies a column number. You can specify from 1 through 254 columns. Multiple columns must be separated by commas.
Example 1
Use of the XMLAGG function to display all employees belonging to each department.
SELECT XMLSERIALIZE(CONTENT
XMLELEMENT(NAME "dept",
XMLATTRIBUTES(e.DEPT_ID AS "id"),
XMLAGG(XMLELEMENT(NAME "lname",
e.EMP_LNAME)))
AS VARCHAR(256)) AS "EMP_NAME_COL"
FROM DEMOEMPL.EMPLOYEE e GROUP BY DEPT_ID ;
The result is similar to the following. Note that the content of the EMP_NAME_COL column has been formatted for convenience.
EMP_NAME_COL ------------ <dept id="1100"> <lname>Fordman</lname> <lname>Halloran</lname> <lname>Hamel</lname> </dept> <dept id="1110"> <lname>Widman</lname> <lname>Alexander</lname> </dept> <dept id="1120"> <lname>Umidy</lname> <lname>White</lname> <lname>Johnson</lname> </dept>
Example 2
Use of the XMLAGG function to display all employees belonging to each department. For each employee, the positions and jobs are included. This example shows that the use of the XMLAGG function together with the ability to specify subqueries as arguments for the SQL/XML functions allows creating very complex XML structures.
select xmlpointer (
xmlelement
( Name "Employees"
, xmlagg
( xmlelement
( NAME "Department"
, xmlattributes(DEPT_ID as "DeptId")
, xmlelement
( NAME "EmployeesInDepartment"
, select xmlagg
( xmlelement
( name "Employee"
, xmlattributes(EMP_ID as "EmpId")
, EMP_FNAME
, EMP_LNAME
, xmlelement
( name "Address"
, XMLFOREST
( e.STREET as "Street"
, e.CITY as "City"
, e.STATE as "State"
)
)
, xmlelement
( name "Positions"
, select xmlagg
( xmlelement
( name "Position"
, xmlattributes
( p.JOB_ID as "JobId" )
, JOB_TITLE
, SALARY_AMOUNT
, BONUS_PERCENT
)
)
from DEMOEMPL.POSITION p, DEMOEMPL.JOB j
where p.EMP_ID = e.EMP_ID
and p.JOB_ID = j.JOB_ID
)
)
)
from DEMOEMPL.EMPLOYEE e
where d.DEPT_ID = e.DEPT_ID
)
)
)
)
)from DEMOEMPL.DEPARTMENT d
The result is similar to the following. It has been formatted and displayed with an "XML-enabled" Web browser that allows collapsing and expanding XML elements in an XML tree.
- <Employees> + <Department DeptId=">1120"> - <Department DeptId="5000"> - <EmployeesInDepartment> - <Employee EmpId="3449"> Cynthia Taylor - <Address> <Street>201 Washington St</Street> <City>Concord</City> <State>MA</State> </Address> - <Positions> <Position JobId="4023">Accountant 74776.0</Position> </Positions> </Employee> + <Employee EmpId="5103"> &invellip. </EmployeesInDepartment> </Department> + <Department DeptId="4500"> </Employees>
Example 3
Use of the XMLAGG function and subqueries to display part of an SQL catalog as an XML document.
select xmlpointer (
xmlelement
( Name "Catalog"
, xmlagg
( xmlelement
( Name "Schema"
, xmlattributes
( s.NAME as "Name"
, s.TYPE as "Type"
)
, 'Referencing SQL Schema:'
, s.REFDSQLSCHEMA
, 'Referencing Non SQL Schema:'
, s.NTWKSCHEMA
, select
xmlagg
( xmlelement
( Name "TablesInSchema"
, xmlattributes
( t.NAME as "Name"
, t.TYPE as "Type"
, t.LENGTH as "Length"
)
, SEGMENT
, '.'
, 'AREA'
, xmlelement
( Name "TableStats"
, xmlattributes
( t.NUMCOLS as "NumCols"
, t.NUMINDEXES as "NumIndexes"
, t.NUMREFERENCING as "NumReferencing"
, t.NUMROWS as "NumRows"
, t.NUMPAGES as "NumPages"
, t.NUMSYNTAX as "NumSyntax"
, t.ESTROWS as "EstRows"
)
)
, select
xmlagg
( xmlelement
( Name "ColumnsInTable"
, xmlattributes
( c.NAME as "Name"
, c.NUMBER as "Nr"
)
, TYPE
, xmlelement
( Name "DataTypeDetails"
, xmlattributes
( c.TYPECODE as "Code"
, c.PRECISION as "Precision"
, c.SCALE as "Scale"
)
)
, xmlelement
( Name "OtherDetails"
, xmlattributes
( c.NULLS as "Null"
, c.DEFAULT as "Default"
, c.VOFFSET as "VOffset"
, c.VLENGTH as "VLength"
, c.NOFFSET as "NOffset"
, c.NLENGTH as "NLength"
, c.NUMVALUES as "NumValues"
)
)
)
)
from SYSTEM.COLUMN c
where c.TABLE = t.NAME
and c.SCHEMA = t.SCHEMA
)
)
from SYSTEM.TABLE t
where t.SCHEMA = s.NAME
and TYPE = 'T'
)
)
)
)from SYSTEM.SCHEMA s
The result is similar to the following. It has been formatted and displayed with an "XML-enabled" Web browser that allows collapsing and expanding XML elements in an XML tree.
- <Catalog> <Schema Name="EMPSCHM" Type="N"> Referencing SQL Schema: Referencing Non SQL Schema:EMPSCHM</Schema> - <Schema Name="DEMOEMPL" Type="R"> Referencing SQL Schema:Referencing Non SQL Schema: + <TablesInSchema Name="DEPARTMENT" Type="T" Length="68"> + <TablesInSchema Name="DIVISION" Type="T" Length="56"> + <TablesInSchema Name="EMPL_MANAGER_INFO" Type="T" Length="56"> + <TablesInSchema Name="EMPLOYEE" Type="T" Length="204"> SQLDEMO .AREA <TableStats NumCols="15" NumIndexes="4" NumReferencing="2" NumRows="55" NumPages="40" NumSyntax="1" EstRows="0" /> - <ColumnsInTable Name="DEPT_ID" Nr="5"> UNSIGNED NUMERIC <DataTypeDetails Code="128" Precision="4" Scale="0" /> <OtherDetails Null="N" Default="N" VOffset="49" VLength="4" NOffset="0" NLength="0" NumValues="14" /> </ColumnsInTable> - <ColumnsInTable Name="EMP_FNAME" Nr="3"> CHARACTER <DataTypeDetails Code="1" Precision="0" Scale="0" /> <OtherDetails Null="N" Default="N" VOffset="9" VLength="20" NOffset="0" NLength="0" NumValues="0" /> </ColumnsInTable>
- <ColumnsInTable Name="EMP_ID" Nr="1">
UNSIGNED NUMERIC
<DataTypeDetails Code="128" Precision="4" Scale="0" />
<OtherDetails Null="N" Default="N" VOffset="0" VLength="4" NOffset="0"
NLength="0" NumValues="0" />
</ColumnsInTable>
&invellip.
</TablesInSchema>
&invellip.
+ <TablesInSchema Name="INSURANCE_PLAN" Type="T" Length="168">
+ <TablesInSchema Name="JOB" Type="T" Length="188">
+ <TablesInSchema Name="POSITION" Type="T" Length="64">
</Schema>
&invellip.
</Catalog>
Returns an XML value that is an XML comment, generated from string-value-expression. The XML value consists of an XML root information item with one child, an XML comment information item whose [content] property is string-value-expression.
Syntax
XMLCOMMENT ─── ( ── string-value-expression ── ) ─────────────►◄
Parameters
Specifies a character string value-expression, that is a value-expression that returns a value of type character.
If string-value-expression is NULL, XMLCOMMENT returns a NULL value. string-value-expression cannot contain a hyphen (--) sequence of characters and cannot end with a hyphen (-) character.
Example
The following statement returns a single XML comment:
SELECT XMLSERIALIZE(CONTENT XMLCOMMENT('My personal opinion')
AS CHAR(80)) as "Comment Only"
FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';
The result is similar to the following:
Comment Only ------------ <!--My personal opinion-->
Returns an XML value that is the concatenation of all the XML-value-expressions. If all the XML-value-expressions are NULL or empty, a NULL value is returned.
Syntax
XMLCONCAT ── ( XML-value-expression ────────────────────────────► ┌───────────────────────────────┐ ►──────▼─,── XML-value-expression ─────┴── ) ───────────────────►◄
Example
Use of the XMLCONCAT function to concatenate two XML elements defined using the XMLELEMENT function.
SELECT e.EMP_ID,
XMLSERIALIZE(CONTENT
XMLCONCAT(XMLELEMENT(NAME "fname",
e.EMP_FNAME),
XMLELEMENT(NAME "lname",
e.EMP_LNAME))
AS VARCHAR(64)) AS "EMP_NAME_COL"
FROM DEMOEMPL.EMPLOYEE e WHERE EMP_ID < 1500 ;
The result is similar to the following:
EMP_ID EMP_NAME_COL ------ ------------ 1003 <fname>James</fname><lname>Baldwin</lname> 1034 <fname>James</fname><lname>Gallway</lname> 1234 <fname>Thomas</fname><lname>Mills</lname>
Returns an XML value that is a single XML element information item as a child of its XML root information. Provided are an XML element name, an optional list of XML namespace declarations, an optional list of attributes, and an optional list of values as the content of the new element.
The XMLATTRIBUTES pseudo function can be used to specify XML attributes in an XML element. The XMLNAMESPACES pseudo function can be used to declare XML namespace in an XML element.
Syntax
XMLELEMENT ───(NAME ── XML-element-name ───────────────────────► ►─┬────────────────────────────────┬───┬─────────────────────┬─► └─ , XML-namespace-declaration ──┘ └─ , XML-attributes ──┘ ►─┬──────────────────────────────────────────────────────────┬─►◄ │ ┌───────── , ─────────┐ │ └,─▼ XML-content-val-exp ┴─┬──────────────────────────────┬┘ └─ OPTION ┬ NULL ON NULL ─────┬┘ ├ EMPTY ON NULL ◄───┤ ├ ABSENT ON NULL ───┤ ├ NIL ON NULL ──────┤ └ NIL ON NO CONTENT ┘
Expansion of XML-namespace-declaration
┌───────────── , ──────────────────┐ XMLNAMESPACES ──(─▼─ XML-namespace-declaration-item ─┴─)──────►◄
Expansion of XML-namespace-declaration-item
►─┬ XML-namespace-URI-char-lit ─ AS ─ XML-namespace-prefix-id ┬►◄ │ │ ├── DEFAULT ── XML-namespace-URI-char-lit ──────────────────┤ │ │ └── NO DEFAULT ─────────────────────────────────────────────┘
Expansion of XML-attributes
┌─────────────────── , ──────────────────┐ XMLATTRIBUTES ─(─▼─ XML-att-val-exp ─┬───────────────────┬┴─)─►◄ └ AS ─ XML-att-name ┘
Parameters
Specifies an identifier that is used as an XML element name. This name must be an XML QName. If the name is qualified, the namespace prefix must be declared within the scope. The maximum length of the identifier is 128 characters.
Specifies a value-expression or an XML-value-expression that after mapping according to Mapping SQL Data Type Values to XML Schema Data Type Values, is used as the content of the generated XML element.
Specifies a character string literal of an XML namespace through a URI. For example, http://www.w3.org/2001/XMLSchema. This character string literal can be empty when used with the DEFAULT option only.
Specifies an identifier that is used as a namespace prefix that is bound to the XML namespace given by XML-namespace-URI-char-lit. The maximum length of the identifier is 128 characters.
This identifier must be an XML NCName. It cannot be equal to "xml" or "xmlns", and it cannot start with the characters "xml" (in any combination). Be sure that no duplicate namespace prefixes are declared in the same XMLNAMESPACES function call.
Specifies an identifier that is used as the XML attribute name. The maximum length of the identifier is 128 characters. The attribute name must be an XML QName. It cannot be equal to "xmlns" or start with "xmlns:". Be sure that no duplicate attribute names are declared in the same XMLATTRIBUTES function call.
Specifies a value-expression that is used as the value of the XML attribute. The value-expression can be of any type except GRAPHIC or VARGRAPHIC. The length of the value is limited to 512 characters.
If XML-att-name is not specified, the attribute name is derived from the XML-att-val-exp value. The XML-att-val-exp value must be a valid SQL column name, optionally qualified with table-name or alias. The fully escaped mapping is applied on the SQL column name to create the attribute name.
Specifies the processing of null values for XML-content-val-exp as follows:
The returned value is never null, but element is completely absent when all XML-content-val-exp are NULL.
The returned value is never null. Element has no content for each NULL value of XML-content-val-exp that is NULL. This is the default.
The returned value is not null. When the [children] property of the element does not contain at least one XML element information item or at least one XML character information item, the element is:
<XML-element-name xsi:nil="true"/>
The returned value is not null, but when all XML-content-val-exp are NULL, element is
<XML-element-name xsi:nil="true"/>
with implicit xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance.
The returned value is NULL when all XML-content-val-exp are NULL.
Notes:
Examples
Example 1
The following SELECT statement produces a row for each employee with one column representing an 'emp' XML element containing the employee's last name. The data type of the result column is VARCHAR(64).
SELECT XMLSERIALIZE(CONTENT
XMLELEMENT(NAME "emp", EMP_LNAME)
AS VARCHAR(64)) AS "EMP_NAME_COL"
FROM DEMOEMPL.EMPLOYEE ;
The result is similar to the following:
EMP_NAME_COL ------------ <emp>Baldwin</emp> <emp>Gallway</emp> <emp>Mills</emp>
Example 2
Same as Example 1, but this example includes a first column containing the employee ID, which uses the "e" alias for the table name and a WHERE clause on the SELECT statement.
SELECT e.EMP_ID,
XMLSERIALIZE(CONTENT
XMLELEMENT(NAME "emp", e.EMP_LNAME)
AS VARCHAR(64)) AS "EMP_NAME_COL"
FROM DEMOEMPL.EMPLOYEE e WHERE EMP_ID < 1500 ;
The result is similar to the following:
EMP_ID EMP_NAME_COL ------ ------------ 1003 <emp>Baldwin</emp> 1034 <emp>Gallway</emp> 1234 <emp>Mills</emp>
Example 3
Same as Example 2, but this example also includes the employee ID as an attribute within the <emp> tag.
SELECT e.EMP_ID,
XMLSERIALIZE(CONTENT
XMLELEMENT(NAME "emp",
XMLATTRIBUTES(e.EMP_ID AS "id"),
e.EMP_LNAME)
AS VARCHAR(64)) AS "EMP_NAME_COL"
FROM DEMOEMPL.EMPLOYEE e WHERE EMP_ID < 1500 ;
The result is similar to the following:
EMP_ID EMP_NAME_COL ------ ------------ 1003 <emp id="1003">Baldwin</emp> 1034 <emp id="1034">Gallway</emp> 1234 <emp id="1234">Mills</emp>
Example 4
Same as Example 3, but this example includes a second attribute within the <emp> tag with the employee's first name.
SELECT e.EMP_ID,
XMLSERIALIZE(CONTENT
XMLELEMENT(NAME "emp",
XMLATTRIBUTES(e.EMP_ID AS "id",
e.EMP_FNAME AS "fname"),
e.EMP_LNAME)
AS VARCHAR(64)) AS "EMP_NAME_COL"
FROM DEMOEMPL.EMPLOYEE e WHERE EMP_ID < 1500 ;
The result is similar to the following. Note that the content of the EMP_NAME_COL column has been formatted for convenience.
EMP_ID EMP_NAME_COL ------ ------------ 1003 <emp id="1003" fname="James">Baldwin</emp> 1034 <emp id="1034" fname="James">Gallway</emp> 1234 <emp id="1234" fname="Thomas">Mills</emp>
Example 5
Same as Example 4, but this example removes the attributes and uses the employee's first name and last name as sub-elements of <emp>.
SELECT e.EMP_ID,
XMLSERIALIZE(CONTENT
XMLELEMENT(NAME "emp",
XMLELEMENT(NAME "fname", e.EMP_FNAME),
XMLELEMENT(NAME "lname", e.EMP_LNAME))
AS VARCHAR(64)) AS "EMP_NAME_COL"
FROM DEMOEMPL.EMPLOYEE e WHERE EMP_ID < 1500 ;
The result is similar to the following. Note that the content of the EMP_NAME_COL column has been formatted for convenience.
EMP_ID EMP_NAME_COL
------ ------------
1003 <emp>
<fname>James</fname>
<lname>Baldwin</lname>
</emp>
1034 <emp>
<fname>James</fname>
<lname>Gallway</lname>
</emp>
1234 <emp>
<fname>Thomas</fname>
<lname>Mills</lname>
</emp>
Example 6
Same as Example 5, but this example concatenates the employee's first name and last name into one sub-element of <emp>.
SELECT e.EMP_ID,
XMLSERIALIZE(CONTENT
XMLELEMENT(NAME "emp",
XMLELEMENT(NAME "name",
e.EMP_FNAME ||' '|| e.EMP_LNAME))
AS VARCHAR(64)) AS "EMP_NAME_COL"
FROM DEMOEMPL.EMPLOYEE e WHERE EMP_ID < 1500 ;
The result is similar to the following. Note that the content of the EMP_NAME_COL column has been formatted for convenience.
EMP_ID EMP_NAME_COL
------ ------------
1003 <emp>
<name>James Baldwin</name>
</emp>
1034 <emp>
<name>James Gallway</name>
</emp>
1234 <emp>
<name>Thomas Mills</name>
</emp>
Example 7
Same as Example 6, but this example uses XMLNAMESPACES.
SELECT e.EMP_ID,
XMLSERIALIZE(CONTENT
XMLELEMENT(NAME "emp",
XMLNAMESPACES(
DEFAULT 'http://ca.com/hr/globalxml',
'http://ca.com/hr/frenchxml' AS "fr" ),
XMLELEMENT(NAME "fr:nom",
e.EMP_FNAME ||' '|| e.EMP_LNAME))
AS VARCHAR(64)) AS "EMP_NAME_COL"
FROM DEMOEMPL.EMPLOYEE e WHERE EMP_ID < 1500 ;
The result is similar to the following. Note that the content of the EMP_NAME_COL column has been formatted for convenience.
EMP_ID EMP_NAME_COL
------ ------------
1003 <emp xmlns="http://ca.com/hr/globalxml"
xmlns:fr="http://ca.com/hr/frenchxml">
<fr:nom>James Baldwin</fr:nom>
</emp>
1034 <emp xmlns="http://ca.com/hr/globalxml"
xmlns:fr="http://ca.com/hr/frenchxml">
<fr:nom>James Gallway</fr:nom>
</emp>
1234 <emp xmlns="http://ca.com/hr/globalxml"
xmlns:fr="http://ca.com/hr/frenchxml">
<fr:nom>Thomas Mills</fr:nom>
</emp>
Example 8
This example illustrates the use of a subquery as an argument of XMLELEMENT.
SELECT
XMLSERIALIZE
( CONTENT
XMLELEMENT
( NAME "Employee"
,XMLATTRIBUTES(e.EMP_ID as "Id")
, e.EMP_FNAME
, e.EMP_LNAME
, SELECT
XMLELEMENT
( NAME "Manager"
, XMLATTRIBUTES(m.EMP_ID as "MgrId")
, m.EMP_FNAME || m.EMP_LNAME
)
FROM DEMOEMPL.employee m
WHERE e.MANAGER_ID = m.EMP_ID
) AS VARCHAR(120)) AS "EmployeeManager"
FROM DEMOEMPL.EMPLOYEE e
The result is similar to the following:
EmployeeManager --------------- <Employee Id="5008">Timothy Fordman <Manager MgrId="2246">Marylou Hamel</Manager></Employee> <Employee Id="4703">Martin Halloran <Manager MgrId="2246">Marylou Hamel</Manager></Employee>
Returns an XML value that is a list of XML element information items as the children of its XML root information. An XML element is produced from each XML-forest-val-exp, using the column name or, if provided, the XML-forest-elem-ident as the XML element name and the XML-forest-val-exp as the element content. The value of XML-forest-val-exp can be any value that has a mapping to an XML value.
The XMLNAMESPACES pseudo function can be used to declare XML namespace in an XML element.
Syntax
XMLFOREST─── ( ──┬──────────────────────────────────┬──────────► └─ XML-namespace-declaration ─ , ──┘ ┌───────────────────────── , ────────────────────────────┐ ►──▼── XML-forest-val-exp ──┬──────────────────────────────┬┴──► └─ AS ─ XML-forest-elem-ident ─┘ ►────┬─────────────────────────────────────────┬──────)────────►◄ └── OPTION ───┬── NULL ON NULL ◄─────┬────┘ ├── EMPTY ON NULL ─────┤ ├── ABSENT ON NULL ────┤ ├── NIL ON NULL ───────┤ └── NIL ON NO CONTENT ─┘
Note: For more information about the expansion of XML-namespace-declaration, see XMLELEMENT-function.
Parameters
Specifies an identifier that is used as an XML element name. The identifier must be an XML QName. If a namespace prefix is used, it must have been declared in the scope of the element. The maximum length of the identifier is 128 characters.
Specifies a value-expression that is used as the element content of an XML element. If XML-forest-elem-ident is not specified, the forest element name is derived from the XML-forest-val-exp value. The XML-forest-val-exp value must be a valid SQL column name, optionally qualified with table-name or alias. The fully escaped mapping is applied on the SQL column name to create the forest element name.
Specifies the processing of null values for XML-forest-val-exp as follows:
The returned value is never null, but element is completely absent from the list when XML-forest-val-exp is NULL.
The returned value is never null. Element has no content for each NULL value of XML-forest-val-exp.
The returned value is not null. When the [children] property of element does not contain at least one XML element information item or at least one XML character information item, the element is:
<XML-forest-element-name xsi:nil="true"/>.
The returned value is not null, but when an XML-forest-val-exp is NULL, element becomes
<XML-forest-element-name xsi:nil="true"/>
with implicit xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance.
The returned value is NULL when all XML-forest-val-exp are NULL. This is the default.
Note: XML-element-name is an identifier in the SQL language. Some valid XML names, that is, all XML QNames with non-null namespace prefix, requires this identifier to be delimited by double quotes.
Example
Similar to Example 5 of the XMLELEMENT function, but the use of two XMLELEMENT invocations to declare two sub-elements of <emp> is replaced by a single XMLFOREST invocation.
SELECT e.EMP_ID,
XMLSERIALIZE(CONTENT
XMLELEMENT(NAME "emp",
XMLFOREST(e.EMP_FNAME AS "fname",
e.EMP_LNAME AS "lname"))
AS VARCHAR(64)) AS "EMP_NAME_COL"
FROM DEMOEMPL.EMPLOYEE e WHERE EMP_ID < 1500 ;
The result is similar to the following. Note that the content of the EMP_NAME_COL column has been formatted for convenience.
EMP_ID EMP_NAME_COL
------ ------------
1003 <emp>
<fname>James</fname>
<lname>Baldwin</lname>
</emp>
1034 <emp>
<fname>James</fname>
<lname>Gallway</lname>
</emp>
1234 <emp>
<fname>Thomas</fname>
<lname>Mills</lname>
</emp>
Returns an XML value as the result of performing a non-validating parse of a character string. Parsing is the inverse operation of serializing.
Syntax
XMLPARSE ── ( ─┬─ CONTENT ─┬── string-value-expression ──────► └─ DOCUMENT ──┘ ►───────────────┬───────────────────────────────────┬─────)────►◄ └──┬── STRIP ─────┬── WHITESPACE ◄──┘ └── PRESERVE ──┘
Parameters
Specifies a character string value-expression, that is a value-expression that returns a value of type character. If string-value-expression is NULL, XMLPARSE returns a NULL value.
Note: The DOCUMENT and STRIP WHITESPACEoptions are not functional in this feature. Therefore, CONTENT and PRESERVE WHITESPACE should always be specified.
Example
The following statement causes an SQL statement exception because the XML is not completely serialized. The serialization is truncated after 20 characters.
SELECT
XMLPARSE
( CONTENT
XMLSERIALIZE
( CONTENT
XMLELEMENT
( NAME "EMP", EMP_LNAME
) AS CHAR(20)
)
)
FROM DEMOEMPL.EMPLOYEE ;
*+ Status = -4 SQLSTATE = 38000 Messages follow:
*+ DB001075 C-4M321: Procedure IDMSQFUX exception 38000 XMLPARSE: Premature end
*+ of data in tag EMP line 1
Returns an XML value that is an XML processing instruction (PI). The XML value consists of:
Syntax
XMLPI ── ( NAME ── identifier ─┬─────────────────────────────┬─ ) ─►◄ └─ , string-value-expression ─┘
Parameters
Specifies the target in the processing instruction. It must be a valid NCName. The maximum length of the identifier is 128 characters.
Specifies a character string value-expression, that is a value-expression that returns a value of type character. It can be NULL or empty, but if present, it cannot contain the "?>" sequence.
A processing instruction takes the following syntactical form in XML 1.0:
<?target data?>
Processing instructions instruct applications to perform some type of extra processing on a given document.
An example of a processing instruction, which is supported by most Web browsers is:
<?xml-stylesheet href="mystyle.xsl" type="text/xsl"?>
When the browser loads an XML document and recognizes the processing instruction, it performs a transformation using the specified XSLT file and displays the result of the transformation instead of the raw XML file. This processing instruction has been accepted as a W3C recommendation. For more information, see http://www.w3.org/TR/xml-stylesheet.
Another example of a processing instruction accepted as a W3C recommendation is the use of the XML declaration:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
The pseudo-attribute version currently must have value "1.0". The pseudo-attribute standalone specifies whether any markup declarations are defined in separate documents. Finally, the pseudo-attribute encoding specifies the encoding of the XML document. XML parsers are required to support at least encoding UTF-8 and UTF-16.
Example
The following statement returns only a single processing instruction:
SELECT XMLSERIALIZE(CONTENT XMLPI (NAME "xml" ,
' version="1.0" encoding="UTF-8" standalone="yes"')
AS CHAR(80)) as "PI Instruction"
FROM SYSTEM.SCHEMA WHERE NAME = 'SYSTEM';
The result is similar to the following:
PI Instruction -------------- <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
Returns an XML value by modifying the properties of the XML root information item of another XML value.
All XML documents must have at least one well-formed root element. The root element, often called the document tag, must follow the prolog (XML declaration plus DTD) and must be a non-empty tag that encompasses the entire document.
Notes:
Syntax
XMLROOT ───( ── XML-value-expression ───────────────────────────► ►───────── , ── VERSION ──┬── string-value-expression ──┬───────► └── NO VALUE ─────────────────┘ ►───────┬──────────────────────────────────────────┬──────)─────►◄ └─ , ── STANDALONE ──┬── YES ──────┬───────┘ ├── NO ───────┤ └── NO VALUE ─┘
Parameters
Specifies a character string value-expression, that is a value-expression that returns a value of type character.
Example
Use of the XMLROOT function to generate the XML declaration in an XML document.
set session XML ENCODING UTF8;
select
XMLSERIALIZE(CONTENT
XMLROOT(
XMLELEMENT(NAME "Employee",
XMLATTRIBUTES(EMP_ID AS "Id",
DEPT_ID AS "DeptId",
MANAGER_ID AS "MgrId"),
TRIM(EMP_FNAME)||' '||trim(EMP_LNAME),
' from ', CITY),
VERSION '1.0', STANDALONE YES)
AS VARCHAR(256)) AS "EmployeeData"
from DEMOEMPL.EMPLOYEE where EMP_ID = 1003;
The result is similar to the following. Note that the content of the EmployeeData column has been formatted for convenience:
*+ EmployeeData *+ ------------ *+ <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <Employee Id="1003" DeptId="6200">James Baldwin from Boston </Employee>
|
Copyright © 2014 CA.
All rights reserved.
|
|