Need XML from parent and child table from Oracle SQL query

Question

In a project on which i am working on, we need to create an XML and pass it on to an interface and i am facing issues while creating an XML containing data from parent table and its subsequent child table.

parent table :- Department should be like

dept_id     dept_name
1           History
2           Biology

CHild table :- employee should be like

emp_id      emp_name     dept_id
1           Helen         1
2           Martha        1 
3           John          1
4           Carol         2

So the resulting XML should be like for department id=1

`<department>
<dept_id>1</dept_id>
<dept_name>History</dept_name>
</department>
<employee>
<emp_id>1</emp_id>
<emp_name>Helen</emp_name>
</employee>
<employee>
<emp_id>2</emp_id>
<emp_name>Martha</emp_name>
</employee>
<employee>
<emp_id>3</emp_id>
<emp_name>John</emp_name>
</employee>'

If anyone knows a better way of doing this then it would be of great help. Thanks in advance

A better way than what? What are you doing now, and what issues do you have? You also have an XML fragment, not a valid document - there is no root node. Are you sure that's the format you're looking for? — Alex Poole
– Alex Poole, Commented Jul 5, 2017 at 12:09

MT0 · Accepted Answer · 2017-07-05 12:26:46Z

1

Something like this (with the employee nodes in the department node so it is not a fragment):

SELECT XMLELEMENT(
         "department",
         XMLFOREST(
           e.dept_id AS "dept_id",
           d.dept_name AS "dept_name"
         ),
         XMLAGG(
           XMLELEMENT(
             "employee",
             XMLFOREST(
               emp_id AS "emp_id",
               emp_name AS "emp_name"
             )
           )
         )
       )
FROM   employees e
       INNER JOIN departments d
       on ( e.dept_id = d.dept_id )
WHERE  e.dept_id = 1
GROUP BY e.dept_id, d.dept_name;

answered Jul 5, 2017 at 12:26

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1654571 Over a year ago

Thanks a lot... I was looking for this kind of query only.

MT0 Over a year ago

@user1654571 If this or any other answer has answered your question you can mark it as accepted (tick the green check mark below the voting buttons to the left of the answer) - this will let other users know your issue is resolved.

user1654571 Over a year ago

Thanks again... Done and thanks for helping a newb :)

Collectives™ on Stack Overflow

Need XML from parent and child table from Oracle SQL query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related