Hello everybody,

I'm trying to generate some XML with the various existing functions such as XMLELEMENT, XMLAGG, ...

But I can't seem to manage to generate exactly what I need to.

What I need to achieve is this :

<GLEntries>
    <GLEntry fac_pk="XXX">
        <FinEntryLine lfa_num="X">
            ...
        </FinEntryLine>
        ...
        <PaymentTerms>
            <PaymentTerm>XX</PaymentTerm>
            ...
        </PaymentTerms>
    </GLEntry>
 ...
</GLEntries>

So there can be multiple GLEntry inside GLEntries, multiple FinEntryLine inside GLEntry and multiple PaymentTerm inside PaymentTerms.

Here's what I got so far :

<GLEntries>
  <GLEntry entry="000049" status="E">
    <FinEntryLine number="1"/>
    <PaymentTerms>
      <PaymentTerm entry="000049"/>
    </PaymentTerms>
  </GLEntry>
  <GLEntry entry="000049" status="E">
    <FinEntryLine number="2"/>
    <PaymentTerms>
      <PaymentTerm entry="000049"/>
    </PaymentTerms>
  </GLEntry>
</GLEntries>

Wit ht this following code :

SELECT XMLSERIALIZE (
          CONTENT XMLELEMENT (
                        "GLEntries",
                        XMLAGG (
                           XMLELEMENT (
                              "GLEntry",
                              xmlattributes (TO_CHAR (fac_num) AS "entry",
                                             'E' AS "status"),
                              XMLCONCAT(XMLELEMENT ("FinEntryLine", xmlattributes(to_char(lfa_num) as "number")
                              )),
                              XMLELEMENT("PaymentTerms",
                                XMLELEMENT("PaymentTerm", xmlattributes(fac_num as "entry"))
                                ))))
          VERSION '1.0')
          AS xmlserialize_doc
  FROM fac,
       lfa
 WHERE lfa_fac_fk = fac_pk
 and fac_pk = 365462;

My issue is that the PaymentTerms element is repeated for each FinEntryLine that is present in the file.

I've tried multiple versions with differents uses of XMLAGG and XMLCONCAT but can't find the thing that works like I need it.

Any help and suggestion is appreciated.

Thanks,
aga

with rws as (
  select '00049' fac_num, 1 lfa_num
  from   dual 
  union all
  select '00049' fac_num, 2 lfa_num
  from   dual 
)
SELECT XMLSERIALIZE (
          CONTENT XMLELEMENT (
            "GLEntries",
               XMLELEMENT (
                 "GLEntry",
                 xmlattributes (
                   TO_CHAR (fac_num) AS "entry",
                   'E' AS "status"
                 ),
                 XMLAGG (
                   XMLELEMENT (
                     "FinEntryLine", 
                     xmlattributes(to_char(lfa_num) as "number")
                   )
                 ),
                 XMLELEMENT ( "PaymentTerms",
                   XMLELEMENT (
                     "PaymentTerm", 
                     xmlattributes(fac_num as "entry")
                   )
                 )
              )
          )
          VERSION '1.0'
        ) AS xmlserialize_doc
  FROM rws
  group  by fac_num;
  
XMLSERIALIZE_DOC                                                                                                                                                                                                                            
<?xml version="1.0"?>
<GLEntries>
  <GLEntry entry="00049" status="E">
    <FinEntryLine number="1"/>
    <FinEntryLine number="2"/>
    <PaymentTerms>
      <PaymentTerm entry="00049"/>
    </PaymentTerms>
  </GLEntry>
</GLEntries>