使用xml序列(游标)从xml类型创建xml

Question

我让这个xml查询针对一个视图运行：(Oracle11g r1)

select xmlelement("clientid", xmlattributes (a.clientid),
                               (select xmlagg(
                                              xmlelement("plan",
                                                 xmlattributes(b.planid,b.planame),
                                                    xmlagg( xmlelement ("ppt", xmlattributes ( b.pname, b.pdname)
     )))) from **viewname** b
           where b.clientid = a.clientid
            group by b.planid,b.planname)) as xmlfrom **viewname** a group by a.clientid;

是否可以在xmlsequence(游标)而不是视图上运行此操作？即

select xmlelement("clientid", xmlattributes (a.clientid),
                               (select xmlagg(
                                              xmlelement("plan",
                                                 xmlattributes(b.planid,b.planame),
                                                    xmlagg( xmlelement ("ppt", xmlattributes ( b.pname, b.pdname)
     )))) from **table(xmlsequence(cursor))** b
           where b.clientid = a.clientid
            group by b.planid,b.planname)) as xml from **table(xmlsequence(cursor))** a group by a.clientid;

或者还有其他方法可以做到这一点？我的甲骨文xml的东西真的很缺乏，所以如果我的问题是新鲜事的话，我很抱歉.谢谢

Answer 1

我不确定这是否是一个解决方案，但如果我想创建一个xml，我就会这样做：

我声明游标：

CURSOR cur(l_date_od date, l_date_do date) IS
    select ... from .. where ...;

我正在声明游标行类型

 variable cur%ROWTYPE;

并选择xml_type变量中的每一行。

open cur(local_date1,local_date2);
  loop
  fetch linia into variable ;
  exit when cur%NOTFOUND;
  select xmlelement("ROW", 
            xmlelement("VENDOR_SITE_CODE",variable .VENDOR_SITE_CODE ),
            xmlelement("ATTRIBUTE1",variable .ATTRIBUTE1 ),
            xmlelement("INVOICE_NUM",variable .INVOICE_NUM ),
            xmlelement("INVOICE_DATE",variable .INVOICE_DATE )
            )
        into xml_type_variable
        FROM dual;
        xml_file2 :=(xml_file.getClobVal()); ----Here i am conversing to clob
  end loop;
  close linia;