Alter text from XML field before inserting into Oracle

Question

I have a column which has data in XML format. I am creating a view based on that XML data.

The code for taking data from XML and sending to view looks similar to below code. My original code has 120-150 columns.

CREATE VIEW Test.TEST_VW
(
   TARGET_ID,
   TARGET_COST
)
AS
     SELECT TARGET_ID, TARGET_COST
       FROM TABLE_XML,
            XMLTABLE (
               '/root/Data'
               PASSING XML.DATA
               COLUMNS TARGET_ID VARCHAR2 (50) PATH 'TARGET_ID',
                       TARGET_COST VARCHAR2 (50) PATH 'TARGET_COST')
      WHERE XML.TARGET_ID = 4
   ORDER BY TARGET_ID;

The data for TARGET_COST is saved as $123 in XML. I want to trim the first letter so that I can show it in number format 123 in view and use for my calculations.

Can anyone guide me on where that conversion can be done ?

Answer 1

In the view definition, in the SELECT clause right after AS (line 7 of code)

replace

       SELECT TARGET_ID, TARGET_COST

with

       SELECT TARGET_ID, TO_NUMBER(TARGET_COST, '$999999999999.99999')

This will simultaneously interpret the $ as currency symbol (no need to remove it) and convert the value to NUMBER. Just make sure the format model includes enough 9's on both sides of the decimal point to cover all the possible values from your XML table.