Strange characters in XML using XMLElement, XMLAgg
Sašo C.Oct 1 2008 — edited Oct 4 2008I have SQL which build XML below. The problem is, if the resulting XML (as Clob) iz larger then 1498 characters, then after 1498 character some strange characters (non ASCII and blanks) appears and this characters "eats" part of follwing XML (to the end of the current <DOCUMENT> element). But the rest of XML after <LOCATION> stil appears.
Example: "<ZA" ends at 1498, folowing with strange characters, and then new <DOCUMENT>:
... /ZAJEM_KRAJ><ZAHPČśd°üśd´üś<DOCUMENT><I...
I prove it in differnet examples, so this is not the problem of non ASCII characters in input data.
If I exclude nested SQL for MY_TABLE_DETAIL table, then XML is OK even it is larger then 1498 characters. So the problem is somehow connected to this nested SQL.
Is this Oracle's bug, something else?
I have Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
select
XMLElement("DISTRIBUTION", XMLAttributes('WATER_MANAGEMENT' as AREA, '1.0.0' as VERZIJA),
XMLElement("SYSTEM",
XMLElement("USER", 'JAVNI')
, XMLElement("SERVER", 'http://institution.gov.si/diatrib...')
, XMLElement("DATUM_IZDAJE", SYSDATE)
),
XMLElement("QUERY", 'Commune = ...'
),
XMLElement("DOCUMENTS",
XMLAgg(
XMLElement("DOCUMENT" ,
XMLElement("ID" , dvln.ID)
,XMLElement("CODE" , dvln.SIFRA_ZADEVE)
,XMLElement("SUBJECT", dvln.NAZIV)
,XMLElement("USE" , decode(dvln.RAVO_SIFRA, 'VD11', dvln.RAVO_DRUGO, dvln.NAZIV_RABE_VODE))
,
(
select XMLElement("LOCATIONS",
XMLAgg(
XMLElement("LOCATION",
XMLElement("TIP_VODNEGA_VIRA_ZAJEM", TIVO_SIFRA_ZAJEM)
,XMLElement("NAZIV_VODNEGA_VIRA_ZAJEM", TIVO_ZAJEM_NAZIV)
,XMLElement("ZAJEM_Y", ZAJEM_Y)
,XMLElement("ZAJEM_X", ZAJEM_X)
,XMLElement("ZAJEM_Z", ZAJEM_Z)
,XMLElement("ZAJEM_KRAJ",ZAJEM_KRAJ)
,XMLElement("ZAJEM_OBCINA", ZAJEM_OBCINA)
,XMLElement("ZAJEM_PARCELA", ZAJEM_PARCELA)
,XMLElement("ZAJEM_KO_STEV", ZAJEM_KO_ID)
,XMLElement("ZAJEM_KO_IME", ZAJEM_KO_IME)
)
)
)
from MY_TABLE_DETAIL vode
where vode.DVLN_ID = dvln.ID
)
)
)
)
) DOVOLJENJA
from MY_TABLE_MASTER dvln
where dvln.SIFRA_ZADEVE = '151-222/2007'
Regards,
Saso