OraFAQ Forum: JDeveloper, Java & XML » XML Generation using PLSQL

Home » Developer & Programmer » JDeveloper, Java & XML » XML Generation using PLSQL

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

XML Generation using PLSQL [message #156793]

Fri, 27 January 2006 18:12

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

Report message to a moderator

Re: XML Generation using PLSQL [message #158298 is a reply to message #156793]

Thu, 09 February 2006 15:11

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.

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 04:22:28 CST 2024