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







All Articles