Merge Nodes in XML (Oracle)
I wrote this request:
WITH Cities
AS(
SELECT DISTINCT EXTRACTVALUE(Address,'(//City)[1]') AS City
FROM Reader
)
SELECT XMLAGG(XMLELEMENT("Cities",
XMLATTRIBUTES(c.City AS "Title"),
XMLELEMENT("Readers",
XMLELEMENT("Reader",XMLATTRIBUTES(r.FirstName,r.SecondName)))))
FROM Reader r,Cities c
WHERE EXTRACTVALUE(r.Address,'(//City)[1]') = c.City;
It generates a document:
<Cities Title="New York">
<Readers>
<Reader FIRSTNAME="JON" SECONDNAME="SHOW"></Reader>
</Readers>
</Cities>
<Cities Title="New York">
<Readers>
<Reader FIRSTNAME="Poll" SECONDNAME="Aeron"></Reader>
</Readers>
</Cities>
<Cities Title="Kharkiv">
<Readers>
<Reader FIRSTNAME="Slavik" SECONDNAME="Romanov"></Reader>
</Readers>
</Cities>
<Cities Title="Boca Juniors">
<Readers>
<Reader FIRSTNAME="Julio " SECONDNAME="Pedro"></Reader>
</Readers>
</Cities>
<Cities Title="London">
<Readers>
<Reader FIRSTNAME="Johnny " SECONDNAME="Smith"></Reader>
</Readers>
</Cities>
I would like to combine nodos with the same attributes ("New York"), exactly, the result was like this:
<Cities>
<!-- -->
<City Title="New York">
<Readers>
<Reader FirstName="JON" SecondName="SHOW" />
<Reader FirstName="Poll" SecondName="Aeron" />
</Readers>
</City>
</Cities>
Data for this sql query: http://sqlfiddle.com/#!4/2d231
+3
source to share
1 answer
This should do the trick:
SELECT xmlagg(xmlelement("Cities", xmlattributes(c.city as "Title"),
xmlelement("Readers", xmlagg(xmlelement("Reader", xmlattributes(r.firstname,r.secondname)))))) cities_xml
from reader r,
xmltable('/Address'
passing r.address
columns city varchar2(20) path 'City[1]') c
group by c.city;
<Cities Title="Boca Juniors">
<Readers>
<Reader FIRSTNAME="Julio " SECONDNAME="Pedro"></Reader>
</Readers>
</Cities>
<Cities Title="Kharkiv">
<Readers>
<Reader FIRSTNAME="Slavik" SECONDNAME="Romanov"></Reader>
</Readers>
</Cities>
<Cities Title="London">
<Readers>
<Reader FIRSTNAME="Johnny " SECONDNAME="Smith"></Reader>
</Readers>
</Cities>
<Cities Title="New York">
<Readers>
<Reader FIRSTNAME="JON" SECONDNAME="SHOW"></Reader>
<Reader FIRSTNAME="Poll" SECONDNAME="Aeron"></Reader>
</Readers>
</Cities>
Btw I changed yours EXTRACTVALUE
to XMLTABLE
since EXTRACT and EXTRACTVALUE are deprecated at 10g and up.
+2
source to share