Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Convert Array to XML

Sc0ttNov 27 2013 — edited Nov 28 2013

I'm a little embarrassed that this has taken me so long, but I'm a bit rusty on my pl/sql and am throwing in the towel

DB: 11.1.0.7.0

Scenario:  We receive a file of records from an external system, and they must be validated in the Oracle database.  A single record has to go through about 20 validations for length, content...etc.  They want to perform every validation on a single record and gather up a list of every validation that failed and pass it back to the client.  I'm creating a packaged stored procedure and need to pass out the set of errors to our middleware.  It does not understand collection types, so I was thinking of passing back XML as a CLOB which it can easily parse instead of the array.

As I encounter an error, I'm throwing it in a simple array of varchar2(4000).  Where I'm stuck is that at the end, I want to convert the errors into an XML string (CLOB) with a simple format like this:

<errors>

  <error>ERROR GOES HERE</error>

  <error>ERROR 2 GOES HERE</error>

  ...

</errors>

I've looked at the XML functions in pl/sql and admittedly I'm overwhelmed at what to use where.  I do have it in a collection that can use table(cast()) to get it in a resultset, but I'm stuck on which of the 4000 XML functions I should use.  I've searched extensively, but it seems like a lot of the hits I get are on how to convert XML to something else, and few on how to create XML from Oracle.

Can someone nudge me in the right direction?

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 26 2013
Added on Nov 27 2013
3 comments
797 views