Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to generate XML with nested values ?
Problem Statement:
You want to generate a complex XML document with nesting values at different levels.
Solution: Oracle has quite a number of functions and procedures for generating XML from regular data. To demonstrate the usage, I will be using the studentdata. We will assume we would like to use the student_id of an student as an XML attribute to the root elementand add detail on fees, specifying the payment period as an attribute, and adding currency details.
Oracle functions XMLROOT, XMLELEMENT, and XMLATTRIBUTE provide full control over the structure of your desired XML.
XMLROOT provides the necessary XML header to turn our results from an XML fragment into a fully formed XML document. XMLROOT when applied results in
<?xml version="1.0"?>
XMLELEMENT works pretty much the same as dbms_output.put_line and a concatenation command. XMLELEMENT outputs the source column together with the provided element name to form XML building blocks for the result. You can string as many calls to XMLELEMENT together as you wish (just as you can concatenate any number of strings), and the expression on which it operates can itself be another call to XMLELEMENT.
XMLATTRIBUTES instruct Oracle to place the given columns or expressions as attributes of the element created by the parent call to XMLELEMENT.
XMLAttributes(student_id as "student_id")
SQL: To genetare a nested XML
SELECT XMLRoot( XMLElement("student", XMLAttributes(student_id as "student_id"), XMLElement("FirstName", first_name), XMLElement("LastName", last_name), XMLElement("fees", XMLAttributes('yearly' as "Period"), XMLElement("Amount", fees), XMLElement("Currency", 'USD')) ), VERSION '1.0') student_XML FROM students ;
Output
<?xml version="1.0"?> <student student_id="100"> <FirstName>SMITH</FirstName> <LastName>JAMES</LastName> <fees Period="yearly"> <Amount>24000</Amount> <Currency>USD</Currency> </fees> </student>
Data Preparation: Data used for the problem is shown below. The data is completely made up for demonstration purpose.
Example
DROP TABLE students; COMMIT; CREATE TABLE students ( student_id NUMBER(6) , first_name VARCHAR2(20) , last_name VARCHAR2(25) , email VARCHAR2(40) , phone_number VARCHAR2(20) , join_date DATE , class_id VARCHAR2(20) , fees NUMBER(8,2) , professor_id NUMBER(6) , department_id NUMBER(4) ) ;
Example
CREATE UNIQUE INDEX stu_id_pk ON students (student_id) ;
INSERT INTO students VALUES (100,'SMITH','JAMES','SMITH.JAMES@notreal.com','111.111.1245',TO_DATE('17-06-2003','DD-MM-YYYY'),'INS_CHAIRMAN',24000,NULL,NULL);
INSERT INTO students VALUES (101,'JOHNSON','JOHN','JOHNSON.JOHN@notreal.com','111.111.1246',TO_DATE('21-09-2005','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (102,'WILLIAMS','ROBERT','WILLIAMS.ROBERT@notreal.com','111.111.1247',TO_DATE('13-01-2001','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (103,'BROWN','MICHAEL','BROWN.MICHAEL@notreal.com','111.111.1248',TO_DATE('03-01-2006','DD-MM-YYYY'),'INS_STAFF',9000,102,60);
INSERT INTO students VALUES (104,'JONES','WILLIAM','JONES.WILLIAM@notreal.com','111.111.1249',TO_DATE('21-05-2007','DD-MM-YYYY'),'INS_STAFF',6000,103,60);
INSERT INTO students VALUES (105,'MILLER','DAVID','MILLER.DAVID@notreal.com','111.111.1250',TO_DATE('25-06-2005','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (106,'DAVIS','RICHARD','DAVIS.RICHARD@notreal.com','111.111.1251',TO_DATE('05-02-2006','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (107,'GARCIA','CHARLES','GARCIA.CHARLES@notreal.com','111.111.1252',TO_DATE('07-02-2007','DD-MM-YYYY'),'INS_STAFF',4200,103,60);
INSERT INTO students VALUES (108,'RODRIGUEZ','JOSEPH','RODRIGUEZ.JOSEPH@notreal.com','111.111.1253',TO_DATE('17-08-2002','DD-MM-YYYY'),'CL_PHY',12008,101,100);
INSERT INTO students VALUES (109,'WILSON','THOMAS','WILSON.THOMAS@notreal.com','111.111.1254',TO_DATE('16-08-2002','DD-MM-YYYY'),'CL_MATH',9000,108,100);
INSERT INTO students VALUES (110,'MARTINEZ','CHRISTOPHER','MARTINEZ.CHRISTOPHER@notreal.com','111.111.1255',TO_DATE('28-09-2005','DD-MM-YYYY'),'CL_MATH',8200,108,100);
INSERT INTO students VALUES (111,'ANDERSON','DANIEL','ANDERSON.DANIEL@notreal.com','111.111.1256',TO_DATE('30-09-2005','DD-MM-YYYY'),'CL_MATH',7700,108,100);
INSERT INTO students VALUES (112,'TAYLOR','PAUL','TAYLOR.PAUL@notreal.com','111.111.1257',TO_DATE('07-03-2006','DD-MM-YYYY'),'CL_MATH',7800,108,100);
INSERT INTO students VALUES (113,'THOMAS','MARK','THOMAS.MARK@notreal.com','111.111.1258',TO_DATE('07-12-2007','DD-MM-YYYY'),'CL_MATH',6900,108,100);
COMMIT;
1K+ Views
To Continue Learning Please Login