CODEWITHSUNDEEP
xmloracle-databaseplsql
FrustratedWithFormsDesigner
FrustratedWithFormsDesigner

Reputation: 27486

Renaming tags in XML output

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.

Upvotes: 1

Views: 783

Answers (2)

Gary Myers
Gary Myers

Reputation: 35401

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

Upvotes: 3

John
John

Reputation: 16007

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.

Upvotes: 0

Related Questions