This content has been marked as final. Show 4 replies
We need to see the data of that table (post the insert query and DDL of the table FRQ_FLR_DETAILS
) for which you are trying to convert into xml... provide us the data of fid=101.
Also just simulated your code with dual table and it worked. So definetely issue with your data...
DECLARE xml_string1 XMLTYPE; v_err_number VARCHAR2 (4000); v_err_msg VARCHAR2 (4000); BEGIN SELECT XMLELEMENT ("Emp", XMLFOREST ('R' "Ac", 'FNAME' "FNM", 'FTYPE' "FTY", 'FCITY' "FCT", 'FSTATE' "FST")) "Emp Element" INTO xml_string1 FROM DUAL; EXCEPTION WHEN OTHERS THEN v_err_number := SQLCODE; v_err_msg := SQLERRM; DBMS_OUTPUT.PUT_LINE ( 'ERROR AT LINE NUMBER: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ()); DBMS_OUTPUT.PUT_LINE ( 'ERROR CODE: ' || v_err_number || ' AND ERROR MSG: ' || v_err_msg); END;
Thank you for the reply. Data exactly is my problem here. Above this, this is just a sample block that I posted. In real time, there are several such statements that fetch data from various tables, and almost each one is running into the aforesaid error. My question is, finding out the data that contains control characters being impossible in my case, as data keeps on changing everyday and a valid data might become invalid the other time it is referenced, can I somehow format the xml function used, so that it handles the control characters. Something like regexp_replace maybe.
Hope i could explain the problem. Please let me know, if there is any other info that I can provide. Thank You once again!
Edited by: user13491035(sneha) on Jan 14, 2013 10:27 PM
I am not very sure, but I read it somewhere that this might be because 11g doesn't handle control characters in XML.No, 11g or the database in general has nothing to do with it.
The XML language itself doesn't support those characters, as explained in the spec : http://www.w3.org/TR/REC-xml/#charsets
See this post for some options :
Those links were really helpful. Thank you.