catalog field XML (no hardcoding allowed)

Niclas Wagenknecht

Summary

catalog field XML (no hardcoding allowed)

Content

Hello community,

 

actually I'm creating a PDF report out of a XML file. The XML file is stored as CLOB in the database.

 

Unfortunately there are catalog fields in the XML data (For example: ES instead of Spain, M instead of Male). The final report should print out the full name. There exist some lookup tables in a database where I can find the connection of ES and Spain and so on.

 

I'm not allowed to hardcode this in the report by using conditional formats because lookup tables change from time to time and the owner of the report doesnt want to change the report then. Instead I need some sort of connection in the data model.

 

When I use SQL queries it is easy. I would make a SQL query for my data table and another SQL query for my lookup table and finally connect those two queries in my data model. However my data source is XML here and the whole XML file is 1 field in the data model. I can't differentiate between data fields in the XML and thus I cant connect fields with their lookups.

 

 

What I want is something like that here:

 

Is there any possibility to do it in that way?

 

I hope, I've made my problem clear.

 

Greets,

Niclas

Niclas Wagenknecht

Just as a suggestion: Somebody told me that PL SQL can do something like that. Somebody else said that the publisher does not support that. I am grateful for any other idea.

Venkat Thota - BIP

If BIP integrated as part of obiee , you can make use of obiee RPD (if you are good with rpd development) , create rpd using xml sources , each file represent one table then join , then use RPD as source for bip , reference : http://ssssupport.blogspot.com/2013/10/obiee-11g-adding-xml-data-source-in.html Thanks

Niclas Wagenknecht

Thats what I meant with "No hardcoding allowed". I need to get those names from a database table.

The catalog value is in the XML file and the corresponding name is in a database. I need any idea how to connect that.

Dir_Pal

<?xdofx:if column ='ES' then ’Spain’

else

if column ='M' then ’Male’

end if?>

U can try this but this is a condition formating

Niclas Wagenknecht

Can I do some sort of SQL query at the template builder level?

Something like SELECT field_name FROM lookuptable WHERE field_code = code