Agfdhyk

problem

XML

<logs>

    <log>

        <ID>123456</ID>

        <NAME>TEST</NAME>

    </log>

</logs>

<logs>

    <log>

        <ID>1234567</ID>

        <NAME>TEST1</NAME>

    </log>

</logs>

in the above xml logs are closing just after log tag.

But i need XML where logs tag to closed in xml in given below.

<logs>

    <log>

        <ID>123456</ID>

        <NAME>TEST</NAME>

    </log>

    <log>

        <ID>1234567</ID>

        <NAME>TEST1</NAME>

    </log>

</logs>

code i tried

v_output is output variable of clob type.

BEGIN

    x := XMLType (in_xml);



    FOR r IN (SELECT EXTRACTVALUE (VALUE (p), '/log/id/text()') AS log_id

                FROM TABLE (XMLSEQUENCE (EXTRACT (x, '/logs/log'))) p)

    LOOP

        OPEN cursorname (r.id);



        FETCH cursorname INTO tmp;



        -- fetching columns

        IF cursorname%FOUND

        THEN

            SELECT XMLELEMENT ("logs",

                               XMLELEMENT ("log", XMLFOREST (id, name))).GETCLOBVAL ()

              INTO out_xml

              FROM  view where id in ( rec1.id ) ;



            v_output := out_xml;

            DBMS_OUTPUT.PUT_LINE (v_output);

        END IF;



        CLOSE cursorname;

    END LOOP;

• can anyone suggest how can i solve multiple xml elements using xmlelement.




• Input is XML and after parsing xml output should be xml.



• 
  

  if i have requirement Dyanamic XML i could use code below.

  
  
  

  select xmltype(cursor(select * from view )) from dual;
  
  

  
  



• cursor is in loop.




• Any suggestion is most welcome.

If I understand what you're trying to do, you're starting from an string version of an XML document like:

<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>

and trying to add the name value corresponding to each of those id value, which you get from a view.

You could extract all the IDs using an XMLTable, join those to the view, and reconstruct the final XML via XMLAgg, something like:

select xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v.id, v.name)))).getclobval()

into out_xml

from xmltable(

  '/logs/log'

  passing xmltype(in_xml)

  columns id number path 'ID'

) x

join your_view v on v.id = x.id;

As a demo in plain SQL starting from your string, and with a CTE to represent your view:

-- CTE to represent your view

with your_view (id, name) as (

  select 123456, 'TEST' from dual

  union all

  select 1234567, 'TEST1' from dual

)

-- actual query

select xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v.id, v.name)))).getclobval()

from xmltable(

  '/logs/log'

  passing xmltype('<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>')

  columns id number path 'ID'

) x

join your_view v on v.id = x.id;



XMLELEMENT("LOGS",XMLAGG(XMLELEMENT("LOG",XMLFOREST(V.ID,V.NAME)))).GETCLOBVAL()                     

-----------------------------------------------------------------------------------------------------

<logs><log><ID>123456</ID><NAME>TEST</NAME></log><log><ID>1234567</ID><NAME>TEST1</NAME></log></logs>

or serialized to prettify it:

-- CTE to represent your view

with your_view (id, name) as (

  select 123456, 'TEST' from dual

  union all

  select 1234567, 'TEST1' from dual

)

-- actual query

select xmlserialize(document

  xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v.id, v.name))))

  indent) as out_xml

from xmltable(

  '/logs/log'

  passing xmltype('<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>')

  columns id number path 'ID'

) x

join your_view v on v.id = x.id;



OUT_XML                                                                         

--------------------------------------------------------------------------------

<logs>

  <log>

    <ID>123456</ID>

    <NAME>TEST</NAME>

  </log>

  <log>

    <ID>1234567</ID>

    <NAME>TEST1</NAME>

  </log>

</logs>

If you really wanted to use the mechanism shown in the question you linked to then you could build up the final output CLOB by appending each element generated inside the loop, with the start and end logs tag added outside the loop:

declare

  in_xml clob := '<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>';

  out_xml clob;

  v_output clob;

begin

  v_output := '<logs>';

  for r in (

    select id

    from xmltable(

      '/logs/log'

      passing xmltype(in_xml)

      columns id number path 'ID'

    )

  )

  loop

     select xmlelement("log", xmlforest (id, name)).getclobval()

     into out_xml

     from your_view where id = r.id;



     v_output := v_output || out_xml;

  end loop;

  v_output := v_output || '</logs>';

  dbms_output.put_line (v_output);

end;

/



<logs><log><ID>123456</ID><NAME>TEST</NAME></log><log><ID>1234567</ID><NAME>TEST1</NAME></log></logs>





PL/SQL procedure successfully completed.

But it's simpler to avoid the cursor loop and just join once:

declare

  in_xml clob := '<logs><log><ID>123456</ID></log><log><ID>1234567</ID></log></logs>';

  v_output clob;

begin

  select xmlelement("logs", xmlagg(xmlelement("log", xmlforest(v.id, v.name)))).getclobval()

  into v_output

  from xmltable(

    '/logs/log'

    passing xmltype(in_xml)

    columns id number path 'ID'

  ) x

  join your_view v on v.id = x.id;



  dbms_output.put_line (v_output);

end;

/



<logs><log><ID>123456</ID><NAME>TEST</NAME></log><log><ID>1234567</ID><NAME>TEST1</NAME></log></logs>





PL/SQL procedure successfully completed.