sql - 合并 XML 中的节点 (Oracle)

Question

我写了这个查询:

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;

它生成文档:

<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>

我想将具有相同属性(“纽约”)的nodos组合起来，正好，结果是这样的:

<Cities>
<!-- -->
  <City Title="New York">
    <Readers>
      <Reader FirstName="JON" SecondName="SHOW" />
      <Reader FirstName="Poll" SecondName="Aeron" />
    </Readers>
  </City>
</Cities>

此 sql 查询的数据:http://sqlfiddle.com/#!4/2d231

Answer 1

这应该可以解决问题:

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>

顺便说一句，我已将您的 EXTRACTVALUE 换成了 XMLTABLE，因为 EXTRACT 和 EXTRACTVALUE 在 10g 及更高版本中都已弃用。