Home » Developer & Programmer » JDeveloper, Java & XML » XML Generation using PLSQL
XML Generation using PLSQL [message #156793] Fri, 27 January 2006 18:12 Go to next message
prasadsbillahalli
Messages: 2
Registered: January 2006
Junior Member
Hi All,
Hope all are doing well.
My question is related to XML generation from PLSQL code.
I have a table EMP with 3 columns Empno, Ename and Deptno
and have another table dept with 3 columns deptno, deptname and location.
There exists Primary-Foreign key link between two tables.

I have record such that an employee can work in multiple departments
So I would like to generate an xml in following format using SQL-PLSQL

<Emp>
<empno> 1 </empno>
<ename> XYZ</ename>
<dept_details>
<dept_no> 10 </dept_no>
<dept_name> floor </dept_name>
<location> first_floor </location>
</dept_details>
<dept_details>
<dept_no> 20 </dept_no>
<dept_name> floor </dept_name>
<location> first_floor </location>
</dept_details>
</Emp>

Please help me out in this task.

Thanks & Regards,
Prasad S Billahalli
Re: XML Generation using PLSQL [message #158298 is a reply to message #156793] Thu, 09 February 2006 15:11 Go to previous message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hello.

Well, firstly, you've said that "EMP" has a deptno column, yet you then say that an Employee can be in multiple departments? Post your tables so we can see them.

I'm going to assume that for the purposes of this, your tables are the equivalent of scott/tiger emp/dept tables...

In terms of how to generate XML, there are literally tons of mechanisms. One favourite of mine is to use the SQL/XML functions in 9i, so your query (again, with the caveats than an employee will only be in one department) becomes (for the example, i'm just using empno = 7782) :
SQL> SELECT XMLELEMENT("Emps",
  2           XMLAGG(XMLELEMENT("Emp",
  3                    XMLFOREST(e.empno  "empno",
  4                              e.ename  "ename"),
  5                    XMLELEMENT("dept_details",
  6                      XMLFOREST(d.deptno  "dept_no",
  7                                d.dname   "dept_name",
  8                                d.loc     "location"))
  9                     )
 10                  )
 11              )
 12    FROM
 13      emp e
 14      INNER JOIN dept d ON (d.deptno = e.deptno )
 15  WHERE e.empno = 7782
 16  /

XMLELEMENT("EMPS",XMLAGG(XMLELEMENT("EMP",XMLFOREST(E.EMPNO"EMPNO",E.ENAME"ENAME
--------------------------------------------------------------------------------
<Emps>
  <Emp>
    <empno>7782</empno>
    <ename>CLARK</ename>
    <dept_details>
      <dept_no>10</dept_no>
      <dept_name>ACCOUNTING</dept_name>
      <location>NEW YORK</location>
    </dept_details>
  </Emp>
</Emps>

Note, since this XML query could return more than one employee, i've had to add a "parent" XML element "Emps", since it wouldn't be a valid XML document without one and only one parent element ("root" node).

Other mechanisms would be the use of DBMS_XMLGEN or similar package.

For further information about SQL/XML, see here

Rgds.
Previous Topic: Exception on getTimestamp with Oracle JDBC driver (10.1.0.2.0)
Next Topic: How add a page number by group?
Goto Forum:
  


Current Time: Thu Mar 28 21:28:17 CDT 2024