spring pro
Reputation: 107
generate xml from java and sql query
I am using the following query:
SELECT inst.*
FROM
(SELECT ROWNUM AS r,
b.name INSTNAME,
ICC.INSTITUTE_ID,
ICC.COURSE_ID,
c.name INSTCOURSE
FROM INSTITUTE_COURSE_CATEGORY ICC,
INSTITUTE a,
MEMBER b,
INSTITUTE_COURSE c
WHERE ICC.CATEGORY_ID = 47
AND ICC.INSTITUTE_ID = a.id
AND ICC.INSTITUTE_ID = c.institute_id
AND ICC.COURSE_ID = c.id
AND a.member_id = b.id
)inst
WHERE inst.r BETWEEN 1 AND 10
to produce following result:
R INSTNAME INSTITUTE_ID COURSE_ID INSTCOURSE
1 Asia Pacific Institute Of Mangement 167 1134 Post Graduate Diploma in Management
2 Asia Pacific Institute Of Mangement 167 1135 Post Graduate Diploma in Management (Marketing)
3 Asia Pacific Institute Of Mangement 167 1136 Post Graduate Diploma in Management (International Business)
4 Asia Pacific Institute Of Mangement 167 1138 Post Graduate Diploma in Management (Banking & Financial Services)
5 University of Delhi 182 4283 Bachelor of Business Studies
6 University of Delhi 182 4285 Bachelor of Commerce (Pass)
7 University of Delhi 182 4291 Bachelor of Commerce (Honours)
8 University of Delhi 182 4366 Master of Commerce
9 Acharya Narendra Dev College 183 812 B.Com. (Hons)
10 Aditi Mahavidyalaya 184 829 B.Com (Pass)
From the above query result, I had to make the following xml in java:
<Root>
<INSTITUTE>
<ID>167</ID>
<NAME>Asia Pacific Instiute Of Mangement</Name>
<COURSES>
<COURSE>
<ID>1134</ID>
<NAME>Post Graduate Diploma in Management</NAME>
</COURSE>
..more
</COURSES>
</INSTITUTE>
..more
</Root>
For Example:
There are 4 Courses that belongs to InstituteID(167).
The problem I am facing is how to make 4 child nodes (Courses Nodes(1134,1135,1136,1138)) under a single `<INSTITUTE>` node
and subsequently make other courses node under their respective
<INSITUTE>
nodes.
Kindly provide suggestion...
Upvotes: 3
Views: 8207
Answers (4)
Rob van Wijk
Reputation: 17705
You can use Oracle's XMLQuery functionality to produce the XML in one go. To illustrate this, I first simulate your setup by creating four tables:
SQL> create table member (id,name)
2 as
3 select 1, 'Asia Pacific Institute Of Management' from dual union all
4 select 2, 'University of Delhi' from dual union all
5 select 3, 'Acharya Narendra Dev College' from dual union all
6 select 4, 'Aditi Mahavidyalaya' from dual
7 /
Table created.
SQL> create table institute (id, member_id)
2 as
3 select 167, 1 from dual union all
4 select 182, 2 from dual union all
5 select 183, 3 from dual union all
6 select 184, 4 from dual
7 /
Table created.
SQL> create table institute_course (id,institute_id,name)
2 as
3 select 1134, 167, 'Post Graduate Diploma in Management' from dual union all
4 select 1135, 167, 'Post Graduate Diploma in Management (Marketing)' from dual union all
5 select 1136, 167, 'Post Graduate Diploma in Management (International Business)' from dual union all
6 select 1137, 167, 'Post Graduate Diploma in Management (Banking & Financial Services)' from dual union all
7 select 4283, 182, 'Bachelor of Business Studies' from dual union all
8 select 4285, 182, 'Bachelor of Commerce (Pass)' from dual union all
9 select 4291, 182, 'Bachelor of Commerce (Honours)' from dual union all
10 select 4366, 182, 'Master of Commerce' from dual union all
11 select 812, 183, 'B.Com. (Hons)' from dual union all
12 select 829, 184, 'B.Com (Pass)' from dual
13 /
Table created.
SQL> create table institute_course_category (category_id,institute_id,course_id)
2 as
3 select 47, 167, 1134 from dual union all
4 select 47, 167, 1135 from dual union all
5 select 47, 167, 1136 from dual union all
6 select 47, 167, 1137 from dual union all
7 select 47, 182, 4283 from dual union all
8 select 47, 182, 4285 from dual union all
9 select 47, 182, 4291 from dual union all
10 select 47, 182, 4366 from dual union all
11 select 47, 183, 812 from dual union all
12 select 47, 184, 829 from dual
13 /
Table created.
I created those four tables such that your query gives the same result set as in your question:
SQL> SELECT inst.*
2 FROM ( SELECT ROWNUM AS r
3 , b.name INSTNAME
4 , ICC.INSTITUTE_ID
5 , ICC.COURSE_ID
6 , c.name INSTCOURSE
7 FROM INSTITUTE_COURSE_CATEGORY ICC
8 , INSTITUTE a
9 , MEMBER b
10 , INSTITUTE_COURSE c
11 WHERE ICC.CATEGORY_ID = 47
12 AND ICC.INSTITUTE_ID = a.id
13 AND ICC.INSTITUTE_ID = c.institute_id
14 AND ICC.COURSE_ID = c.id
15 AND a.member_id = b.id
16 ) inst
17 WHERE inst.r BETWEEN 1 AND 10
18 /
R INSTNAME INSTITUTE_ID COURSE_ID INSTCOURSE
--- ------------------------------------ ------------ ---------- ------------------------------------------------------------------
1 Asia Pacific Institute Of Management 167 1137 Post Graduate Diploma in Management (Banking & Financial Services)
2 Asia Pacific Institute Of Management 167 1136 Post Graduate Diploma in Management (International Business)
3 Asia Pacific Institute Of Management 167 1135 Post Graduate Diploma in Management (Marketing)
4 Asia Pacific Institute Of Management 167 1134 Post Graduate Diploma in Management
5 University of Delhi 182 4366 Master of Commerce
6 University of Delhi 182 4291 Bachelor of Commerce (Honours)
7 University of Delhi 182 4285 Bachelor of Commerce (Pass)
8 University of Delhi 182 4283 Bachelor of Business Studies
9 Acharya Narendra Dev College 183 812 B.Com. (Hons)
10 Aditi Mahavidyalaya 184 829 B.Com (Pass)
10 rows selected.
Now the XML query is:
SQL> select xmlelement
2 ( "Root"
3 , xmlelement
4 ( "INSTITUTE"
5 , xmlagg(xmlforest(id,name,courses))
6 )
7 ).extract('/') your_xml
8 from ( select i.id
9 , m.name
10 , xmlagg
11 ( xmlelement
12 ( "COURSE"
13 , xmlforest(ic.id,ic.name)
14 )
15 ) courses
16 from institute i
17 inner join member m on (i.member_id = m.id)
18 inner join institute_course ic on (ic.institute_id = i.id)
19 inner join institute_course_category icc on (icc.institute_id = ic.institute_id and icc.course_id = ic.id)
20 where icc.category_id = 47
21 group by i.id
22 , m.name
23 )
24 /
YOUR_XML
--------------------------------------------------------------------------------------------------------------------------------------
<Root>
<INSTITUTE>
<ID>167</ID>
<NAME>Asia Pacific Institute Of Management</NAME>
<COURSES>
<COURSE>
<ID>1137</ID>
<NAME>Post Graduate Diploma in Management (Banking & Financial Services)</NAME>
</COURSE>
<COURSE>
<ID>1136</ID>
<NAME>Post Graduate Diploma in Management (International Business)</NAME>
</COURSE>
<COURSE>
<ID>1135</ID>
<NAME>Post Graduate Diploma in Management (Marketing)</NAME>
</COURSE>
<COURSE>
<ID>1134</ID>
<NAME>Post Graduate Diploma in Management</NAME>
</COURSE>
</COURSES>
<ID>182</ID>
<NAME>University of Delhi</NAME>
<COURSES>
<COURSE>
<ID>4366</ID>
<NAME>Master of Commerce</NAME>
</COURSE>
<COURSE>
<ID>4291</ID>
<NAME>Bachelor of Commerce (Honours)</NAME>
</COURSE>
<COURSE>
<ID>4285</ID>
<NAME>Bachelor of Commerce (Pass)</NAME>
</COURSE>
<COURSE>
<ID>4283</ID>
<NAME>Bachelor of Business Studies</NAME>
</COURSE>
</COURSES>
<ID>183</ID>
<NAME>Acharya Narendra Dev College</NAME>
<COURSES>
<COURSE>
<ID>812</ID>
<NAME>B.Com. (Hons)</NAME>
</COURSE>
</COURSES>
<ID>184</ID>
<NAME>Aditi Mahavidyalaya</NAME>
<COURSES>
<COURSE>
<ID>829</ID>
<NAME>B.Com (Pass)</NAME>
</COURSE>
</COURSES>
</INSTITUTE>
</Root>
1 row selected.
Here is the documentation for the functions used:
Note that I used .extract('/') just for pretty-printing. You can leave that off.
And, just because your current query is only two levels deep, you can shorten the query somewhat by using the query below. However, when your have more than two levels, you'll have to use the inline variant which you can easily expand.
SQL> select xmlelement
2 ( "Root"
3 , xmlelement
4 ( "INSTITUTE"
5 , xmlagg
6 ( xmlforest
7 ( i.id
8 , m.name
9 , xmlagg
10 ( xmlelement
11 ( "COURSE"
12 , xmlforest(ic.id,ic.name)
13 )
14 ) as "COURSES"
15 )
16 )
17 )
18 ).extract('/') your_xml
19 from institute i
20 inner join member m on (i.member_id = m.id)
21 inner join institute_course ic on (ic.institute_id = i.id)
22 inner join institute_course_category icc on (icc.institute_id = ic.institute_id and icc.course_id = ic.id)
23 where icc.category_id = 47
24 group by i.id
25 , m.name
26 /
Hope this helps.
Regards,
Rob.
Upvotes: 4
mavroprovato
Reputation: 8362
Oracle can create XML from a query natively
http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb13gen.htm
I would guess that this will be the fastest way to do it
Upvotes: 0
Gaurav
Reputation: 21
You can construct a POJO and then use the JAXB API to convert it to XML.
Upvotes: 2
Francisco Spaeth
Reputation: 23903
In order to obtain more or less this result:
<?xml version="1.0" encoding="UTF-8"?>
<institute>
<id>167</id>
<name>Asia Pacific Institute Of Mangement</name>
<courses>
<course>
<id>1136</id>
<name>Post Graduate Diploma in Management (International Business)</name>
</course>
<course>
<id>1138</id>
<name>Post Graduate Diploma in Management (Banking & Financial Services)</name>
</course>
</courses>
<id>182</id>
<name>University of Delhi</name>
<courses>
<course>
<id>4283</id>
<name>Bachelor of Business Studies</name>
</course>
</courses>
</institute>
you could do something like:
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.XMLWriter;
public class Dom4JTest {
// DATA SET ORDERED BY INSTITUTE, COURSE (COULD BE THE ID)
// 3 Asia Pacific Institute Of Mangement 167 1136 Post Graduate Diploma in Management
// (International Business) New Delhi Delhi India
// 4 Asia Pacific Institute Of Mangement 167 1138 Post Graduate Diploma in Management (Banking &
// Financial Services) New Delhi Delhi India
// 5 University of Delhi 182 4283 Bachelor of Business Studies New Delhi Delhi India
private static List<String[]> data = new ArrayList<String[]>();
static {
data.add(new String[] { "3", "Asia Pacific Institute Of Mangement", "167", "1136",
"Post Graduate Diploma in Management (International Business)", "New Delhi", "Delhi", "India" });
data.add(new String[] { "4", "Asia Pacific Institute Of Mangement", "167", "1138",
"Post Graduate Diploma in Management (Banking & Financial Services)", "New Delhi", "Delhi", "India" });
data.add(new String[] { "5", "University of Delhi", "182", "4283", "Bachelor of Business Studies", "New Delhi", "Delhi", "India" });
}
public static void main(String[] args) throws IOException {
Document document = DocumentHelper.createDocument();
Element root = document.addElement("institute");
String id = null;
Element coursesElement = null;
// here the iteration over dataset, of course the result will not be just string but the idea will be the same...
for (String[] strs : data) {
if (id == null || !id.equals(strs[2])) {
id = strs[2];
root.addElement("id").addText(id);
root.addElement("name").addText(strs[1]);
coursesElement = root.addElement("courses");
}
Element course = coursesElement.addElement("course");
course.addElement("id").setText(strs[3]);
course.addElement("name").setText(strs[4]);
}
OutputFormat format = OutputFormat.createPrettyPrint();
XMLWriter writer = new XMLWriter(System.out, format);
writer.write(document);
}
}
Upvotes: 2
Related Questions
- Generate an xml file using DOM
- Need to execute sql queries over xml data
- MySQL Query to XML output
- How to produce XML using SQL query on Oracle
- Creating a XML file from a SQL database using Java
- using jdbc to export data into xml document
- Generate xml dynamically in java using data from a table in database
- convert java object from sql to xml
- How to read sql query from xml using java
- How to Generate XML from Database