SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to union XML data and than merge them into XML document

user649990Nov 15 2016 — edited Nov 15 2016

select result from (
( SELECT
   XMLElement("BUSINESSETITY",
   XMLELEMENT("TYPE",'FACILITY'),
   XMLELEMENT("NNUMBER",pk), 
   XMLELEMENT("SITENUMBER",''),
   XMLELEMENT("DEPTNUMBER",'') ) result
 from table 2
 )
UNION ALL
 (
 select  XMLElement("BUSINESSETITY",
   XMLELEMENT("TYPE",'SITE'),
   XMLELEMENT("TIONNUMBER",''), 
   XMLELEMENT("SITENUMBER",PK),
   XMLELEMENT("DEPTNUMBER",'') ) result
  from table2
 )

I am using above code to covert rows into XML data and than doing a union.

But it giving me . different rows

What I want is one row which something like this

<BUSINESS>
<BUSINESSETITY>  ...... </BUSINESSETITY>
<BUSINESSETITY>  ...... </BUSINESSETITY>
 <BUSINESSETITY>  ...... </BUSINESSETITY>
 <BUSINESSETITY>  ...... </BUSINESSETITY>
..
..
...
</BUSINESS>

Can you pls help me. thanks a lot

Locked Post

New comments cannot be posted to this locked post.

Locked on Dec 13 2016

Added on Nov 15 2016

#sql, #xml

4 comments

939 views