• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

oracle xml query

Hi,

In oracle 10g, the following query (Query 1),

select parent_node,grandchild_node, details from
table;

is returning:

p1 gc1 d1
p1 gc1 d2
p1 gc1 d3


And, this query(Query 2),

with a as (select parent_node,grandchild_node, details from
table)
select XMLELEMENT("PARENT_NODE", parent_node,XMLELEMENT("CHILD_NODE",XMLAGG(XMLELEMENT("GRANDCHILD_NODE",grandchild_node))))
from a;

is returning:

<PARENT_NODE>p1
<CHILD_NODE>
  <GRANDCHILD_NODE>GC1</GRANDCHILD_NODE>
  <GRANDCHILD_NODE>GC1</GRANDCHILD_NODE>
  <GRANDCHILD_NODE>GC1</GRANDCHILD_NODE>
</CHILD_NODE>
</PARENT_NODE>

But I want the query2 to return the following, i.e. i do not want to see duplicate GRANDCHILD_NODE tags.
<PARENT_NODE>p1
<CHILD_NODE>
  <GRANDCHILD_NODE>GC1</GRANDCHILD_NODE>
</CHILD_NODE>
</PARENT_NODE>

Any idea how this can be achieved?

Many thanks.
0
subratoc
Asked:
subratoc
1 Solution
 
sdstuberCommented:
SELECT   XMLELEMENT("PARENT_NODE",
                    parent_node,
                    XMLELEMENT("CHILD_NODE", XMLAGG(XMLELEMENT("GRANDCHILD_NODE", grandchild_node)))
                   )
    FROM (SELECT DISTINCT parent_node, grandchild_node FROM a)
GROUP BY parent_node;
0
 
subratocAuthor Commented:
Thanks sdstuber.
I was wondering whether there was any oracle xml feature like xmlagg(distinct) that can
remove the distinct nodes from the xml but this method works for me at the moment.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now