# 22. Virtuoso Functions Guide - XMLAGG

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
Virtuoso Functions Guide
Administration
Aggregate Functions
Array Manipulation
BPEL APIs
Backup
Compression
Cursor
Date & Time Manipulation
Debug
Dictionary Manipulation
Encoding & Decoding
File Manipulation
Free Text
Hashing / Cryptographic
LDAP
Locale
Mail
Miscellaneous
Number
Remote SQL Data Source
Replication
SOAP
SQL
String
Transaction
Type Mapping
UDDI
User Defined Types & The CLR
Virtuoso Java PL API
Virtuoso Server Extension Interface (VSEI)
Web Server & Internet
XML
xmlagg
xmlattributes
xmladdattribute
xmlappendchildren
xmlconcat
xmlelement
xmlforest
xmlinsertafter
xmlinsertbefore
xmlreplace
xmltype.xmltype
xmltype.createnonsch...
xmltype.createschema...
xmltype.createxml
xmltype.existsnode
xmltype.extract
xmltype.getclobval
xmltype.getnamespace
xmltype.getnumval
xmltype.getrooteleme...
xmltype.getschemaurl
xmltype.getstringval
xmltype.isfragment
xmltype.isschemabase...
xmltype.isschemavali...
xmltype.isschemavali...
xmltype.schemavalida...
xmltype.setschemaval...
xmltype.toobject
xmltype.transform
xmlupdate
xper navigation
createxml
isentity
serialize_to_utf8_xm...
tidy_html
tidy_list_errors
updatexml
xml_add_system_path
xml_auto
xml_auto_dtd
xml_auto_schema
xml_create_tables_fr...
xml_cut
xml_doc_output_optio...
xml_get_system_paths
xml_load_mapping_sch...
xml_load_schema_decl
xml_namespace_scope
xml_persistent
xml_template
xml_tree
xml_tree_doc
xml_tree_doc_media_t...
xml_uri_get
xml_validate_dtd
xml_validate_schema
xml_view_dtd
xml_view_schema
xmlsql_update
xpath_eval
xper_cut
xper_doc
xper_locate_words
xpf_extension
xpf_extension_remove
xquery_eval
xslt
xslt_format_number
xslt_sheet
xslt_stale
xte_head
xte_node
xte_node_from_nodebl...
xte_nodebld_acc
xte_nodebld_final
xte_nodebld_init
xtree_doc
XPATH & XQUERY
Functions Index

## XMLAGG

Produces a forest of elements from a collection of XML values
vector XMLAGG ( value_expression any);
Description

XMLAGG is aggregate function that produces a forest of XML elements from the given list of xml elements. It concatenates the values returned from one column of multiple rows, unlike XMLCONCAT, which concatenates the values returned from multiple columns in the same row.

The order of element in the result of XMLAGG is defined by the order of retrieval of the source data rows. It is important to remember that the order of rows in an SQL resultset defined only if there's an explicit ORDER BY clause. Hence if the order of elements in the resulting forest is important then XMLAGG should be applied to data that comes from inner SELECT statement that has an ORDER BY clause, not e.g. from a table reference.

Note that XMLAGG is actually declared as DB.DBA.XMLAGG but it is not important for plain use: for compatibility with other systems, any call of XMLAGG in any SQl statement is always replaced with the call of DB.DBA.XMLAGG, no matter which qualifier and user name are in use.

Parameters
value_expression – the result of one of the following functions XMLAGG, XMLCONCAT, XMLELEMENT, or XMLFOREST.
Parameters

The aggregate returns a vector that is a suitable input for functions XMLELEMENT, XMLCONCAT and xml_tree_doc.

Examples
XMLAGG() enclosed in XMLELEMENT()

The following example produces an 'Emp' element with attribute 'Title' and a list of all employees having the title 'Sales Representative' as element content.

    select XMLELEMENT ('Emp', XMLATTRIBUTES ('Sales Representative' as "Title"),
      XMLAGG (XMLELEMENT ('Name', "FirstName", ' ', "LastName")))
      from "Demo"."demo"."Employees"
      where "Title" = 'Sales Representative';

    callret
    VARCHAR
    _______________________________________________________________________________

    <Emp Title="Sales Representative">
      <Name>Nancy Davolio</Name>
      <Name>Janet Leverling</Name>
      <Name>Margaret Peacock</Name>
      <Name>Michael Suyama</Name>
      <Name>Robert King</Name>
      <Name>Anne Dodsworth</Name>
    </Emp>

    1 Rows.

XMLAGG() that produces a sorted document

The result of the previous example contains an unsorted list of names. This is because data rows were retrieved from "Demo"."demo"."Employees" in primary key order, and this order has nothing common with fields "FirstName" and "LastName". To produce the sorted result, the query should contain ORDER BY in a subquery. These two variants will work identically if "FirstName" never contains whitespaces or nonprintable control characters, but the last one is formally more correct.

    select XMLELEMENT ('Emp', XMLATTRIBUTES ('Sales Representative' as "Title"),
      XMLAGG (XMLELEMENT ('Name', "FirstName", ' ', "LastName")))
      from
        (select "FirstName", "LastName"
         from "Demo"."demo"."Employees"
         where "Title"= 'Sales Representative'
         order by 1, 2) as subq;

    callret
    VARCHAR
    _______________________________________________________________________________

    <Emp Title="Sales Representative">
      <Name>Anne Dodsworth</Name>
      <Name>Janet Leverling</Name>
      <Name>Margaret Peacock</Name>
      <Name>Michael Suyama</Name>
      <Name>Nancy Davolio</Name>
      <Name>Robert King</Name>
    </Emp>

    1 Rows.

    select XMLELEMENT ('Emp', XMLATTRIBUTES ('Sales Representative' as "Title"),
      XMLAGG (XMLELEMENT ('Name', full_name)))
      from
        (select concat ("FirstName", ' ', "LastName") as full_name
         from "Demo"."demo"."Employees"
         where "Title"= 'Sales Representative'
         order by 1) as subq;

    callret
    VARCHAR
    _______________________________________________________________________________

    <Emp Title="Sales Representative">
      <Name>Anne Dodsworth</Name>
      <Name>Janet Leverling</Name>
      <Name>Margaret Peacock</Name>
      <Name>Michael Suyama</Name>
      <Name>Nancy Davolio</Name>
      <Name>Robert King</Name>
    </Emp>

    1 Rows.

See Also

XMLELEMENT()

XMLATTRIBUTES()

XMLFOREST()

XMLCONCAT()