Renaming tags in XML output

Question

I have some code that generates XML from some tables. The tables that I'm querying were generated from an XSD file the describes the XML I am supposed to be generating. The problem is that the names of the tables and fields follow a different naming convention than those in the schema - for example, an element named "personID" in the schema would be "PRSN_ID" in the database tables. To generate output, I have code that looks like this:

Select xmlelement("person",
         xmlelement("personID",PRSN_ID),
         xmlelement("personName",PRSN_NAM),
...

As you can imagine, this is starting to get tedious. I am looking for a better solution. I know I can use xmlforest to give tags the same names as a selected column, and I now need a way to translate. For example, if I generated:

/*the full long list of columns names can be generated so it's an easy copy-paste:*/
select xmlelement("PRSN", xmlforest(PRSN_ID,PRSN_NAM,...

/*produces this:*/

<PRSN>
    <PRSN_ID>1<PRSN_ID/>
    <PRSN_NAM>BOB<PRSN_NAM/>
...

I would need to translate it into this:

<person>
    <personID>1</personID>
    <personName>BOB</personName>
...

How would I go about doing this kind of translation in Oracle PL/SQL? I do have a generated mapping file that tells me that "PRSN_ID" should be translated to "personID", I'm just not sure the best way to proceed with this.

Answer 1

If you have a generated mapping file that says PRSN_ID should be personID, why don't you just generate your first SELECT command using that mapping information?

If that SELECT command does what you want, generating it takes away the tedium.

Answer 2

Can't you create view over the table with the columns renamed. Oracle will do mixed case column names if you put them in double quotes

SELECT PRSN_ID "personID"....
FROM