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 ?