EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

Oracle XMLAGG

Home » Data Science » Data Science Tutorials » Oracle Tutorial » Oracle XMLAGG

Oracle XMLAGG

Introduction to Oracle XMLAGG

We can use the XMLAGG function in the oracle database to aggregate the collection of fragments of XML. This function returns the value which will be an aggregated document in XML and if any of the arguments that are passed to this function evaluate to NULL then the argument is skipped or dropped out from the final result. This function behaves in the same way as that of SYS_XMLAGG. But there is only one difference between SYS_XMLAGG and XMLAGG which is that the XMLAGG function does not accept the XMLFORMAT object for the purpose of formatting the result though it returns the collection of the nodes as the result. One more difference is that the resultant of XMLAGG is not enclosed in the element tags as in the case of SYS_XMLAGG. The number literals mentioned in the ORDER BY clause are not interpreted by the oracle database as column positions as in other cases but they are referred to as number literals themselves.

Syntax

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

The syntax of the XMLAGG function is as shown below –

XMLAGG(<XML_ELEMENT> ORDER BY <VALUE>)

The syntax can also be understood from the following figure –

Oracle XMLAGG syntax

As the XMLAGG function returns the result in a single row or cell to wrap around all the element tags inside a single parent tag we can optionally make the use of XML_ELEMENT which will help to get all the result enclosed in a new parent tag or inside a single element which is created by it. When the strings are to be aggregated then we can use the “.extract(‘//text()’)” which will keep the string together in XML and the rtrim() function can be used along with it to get rid of the trailing commas or spaces. When instead of strings we are about to aggregate the CLOB values using XMLAGG function then we can make the use of xmlparse which will accept the XML text and will transform it into a datatype of XMLTYPE. The use of the ORDER BY clause is completely optional in nature and can be used to order the values of XML elements that are being concatenated by using the XMLAGG function.

Examples of Oracle XMLAGG

Let us take the example of the customer data of a particular brand having multiple stores and multiple departments in each of the stores. The table is created and the data is inserted in it which can be done by using the following query –

DROP TABLE customers;
COMMIT;
CREATE TABLE customers
( customer_id     NUMBER(6)
, f_name     VARCHAR2(20)
, l_name      VARCHAR2(25)
, email_id          VARCHAR2(40)
, mobile_number   VARCHAR2(20)
, purchase_date      DATE
, store_id       VARCHAR2(20)
, bill_amount           NUMBER(8,2)
, salesman_id   NUMBER(6)
, department_id  NUMBER(4)
) ;
CREATE UNIQUE INDEX cust_id_pk ON customers (customer_id) ;
COMMIT;

There are in all 14 different customers data being inserted into the customer’s table that is created using the INSERT INTO statements whose contents are as shown below –

Oracle XMLAGG output 1

SELECT department_id,
rtrim(xmlagg(xmlelement(customerData,f_name
|| ' '
|| l_name , ', ')
ORDER BY f_name).extract('//text()').getstringval(), ',') AS "names"
FROM customers
GROUP BY department_id;

The XML element used in the above statement will lead to the creation of a new xml element called customer data. We can give any name to this xml element being created which will contain the concatenated value of the f_name, l_name, and the comma between each of the concatenated values. The use of XMLAGG, in this case, gives rise to the creation of the XML snippet which is the aggregation of the customer data XML elements. The use of rtrim() removes all the spaces and the trailing commas while.extract helps to keep the string together as a single unit. The execution of the above query will give the following output along with all the grouped result set according to the departments of the store as shown below –

Popular Course in this category
Sale
Oracle Training (14 Courses, 8+ Projects)14 Online Courses | 8 Hands-on Projects | 120+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (9,261 ratings)
Course Price

View Course

Related Courses
Oracle DBA Database Management System Training (2 Courses)All in One Financial Analyst Bundle - 250+ Courses, 40+ Projects

Oracle XMLAGG output 2

In XML format, the output of the execution of the above query will be as follows along with all the grouped result set according to the departments of the store –

Oracle XMLAGG output 3

We can even make use of the XMLAGG function along with the CLOB datatype values rather than the string values by using the XMLPARSE which takes the specified XML values as text and further converts them to XMLTYPE data type.

SELECT department_id,
xmlagg(xmlparse(content f_name
|| ' '
|| l_name wellformed)
ORDER BY f_name).getclobval()
FROM customers
GROUP BY department_id;

The execution of the above query will give the following output along with all the grouped result set according to the departments of the store but without the commas between the aggregated values as shown below –

Oracle XMLAGG output 4

In XML format, the output of the execution of the above query will be as follows along with all the grouped result set according to the departments of the store –

Oracle XMLAGG output 5

We can even use the JSON_ARRAYAGG instead of the XMLAGG function for the oracle database versions which are more than 18c or later. We can use the JSON_ARRAYAGG instead of the XMLAGG function in the above query in the following way which generates similar results with a little difference of the square brackets for each group.

SELECT department_id,
replace(JSON_ARRAYAGG(f_name || ' ' || l_name ORDER BY department_id RETURNING CLOB),'"','') AS returning_value
FROM customers
GROUP BY department_id;

The execution of the above query will give the following output along with all the grouped result set according to the departments of the store as shown below –

Oracle XMLAGG output 6

In XML format, the output of the execution of the above query will be as follows along with all the grouped result set according to the departments of the store –

Oracle XMLAGG output 7

Conclusion

We can use the XMLAGG function in the oracle database management system for aggregating multiple strings or XML fragments to be represented as a concatenated XML element as a single unit. Mostly, strings are aggregated to generate a comma-separated concatenated string which is the collection of all the minor strings. The XMLAGG function works similar to that of SYS_XMLAGG with some minor differences in formatting. In the versions of oracle higher than 18 c, we can also use the JSON_ARRAYAGG function as an alternative to XMLAGG for aggregating multiple values and representing them in a single row or cell. The ORDER BY and GROUP BY statements are mostly used along with the XMLAGG function to get the grouped concatenated result which is properly ordered.

Recommended Articles

This is a guide to Oracle XMLAGG. Here we discuss How we can use the XMLAGG function in the oracle database management system. You may also have a look at the following articles to learn more –

  1. Oracle Tablespace
  2. Oracle Synonyms
  3. Oracle COALESCE
  4. Oracle Analytic Functions

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Oracle Tutorial
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Special Offer - Oracle Course Training Learn More