Home » SQL & PL/SQL » SQL & PL/SQL » Creating a not well-formed XML
Creating a not well-formed XML [message #681058] Wed, 10 June 2020 04:33 Go to next message
Mark70
Messages: 18
Registered: June 2020
Junior Member
Hi,
I've got a problem in generating an XML

The XML is something like this :

<TAG1>
 <TAG2>col</TAG2>
 <TAG3>col</TAG3>
 <TAG4>
  ...
 </TAG4>
 <TAG4>
  ...
 </TAG4>
 <TAG4>
  ...
 </TAG4>
</TAG1>
where col are different columns of different tables and the <TAG4> ... </TAG4> section has to be created dinamically N times.
I've developed a package where I have a loop where I call a stored procedure to create <TAG4> ... </TAG4> and return this XML and to do every time something like this:

select xmlconcat(
                v_xml_res,
                p_xml_doc
                ).extract('/*') 
into v_xml_res
from dual;
where v_xml_res is what I have already created and p_xml_doc is the new <TAG4> ... </TAG4> section generated, so as to have the

<TAG4>
 ...
</TAG4>
<TAG4>
 ...
</TAG4>
<TAG4>
 ...
</TAG4>
and to do the last part

select xmlelement("TAG1", 
       xmlelement("TAG2",col),
       xmlelement("TAG3",col),
       xmlconcat(
                 v_xml_res
                 )).extract('/*')
into v_xml_res
from tables, ...;
The problem is that when I try to concat the first two <TAG4> ... </TAG4> sections in an XMLTYPE, I got

ora-19010: cannot insert xml fragments

because in the loop the XML created would be:

<TAG4>
 ...
</TAG4>
<TAG4>
 ...
</TAG4>
<TAG4>
 ...
</TAG4>
that is not a well formed XML because I should have a external TAG but that I'm going to create only at the last step of the package.

How can I do?
Oracle version 10g
Thanks




Re: Creating a not well-formed XML [message #681060 is a reply to message #681058] Wed, 10 June 2020 05:25 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your previous topic:

Michel Cadot wrote on Thu, 04 June 2020 16:42

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

And as asked in your previous topic:

Michel Cadot wrote on Wed, 03 June 2020 17:58
...
Note: Always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
A feedback in this topic would also be welcome.
And the other one too.


Previous Topic: CASE Statement on a Cursor
Next Topic: Simple UPDATE - should be easy but just can't get it!
Goto Forum:
  


Current Time: Thu Mar 28 12:47:37 CDT 2024