CODEWITHSUNDEEP
sqlxmloracle-databasexml-parsingtype-conversion
alejomarchan
alejomarchan

Reputation: 368

How to read a field Oracle containing XML

A table exists in the environment of production with the following structure:

 CREATE TABLE gold_dwh_reload (
  msisdn              NUMBER(13,0)   NOT NULL,
  recharge_date       TIMESTAMP(6)   NOT NULL,
  impacted_balances   VARCHAR2(4000) NULL,
  lc_state            VARCHAR2(5)    NOT NULL)
  TABLESPACE sopfun_tab
  NOCOMPRESS
/

A normal consultation would the following result by example:

MSISDN  RECHARGE_DATE   IMPACTED_BALANCES   LC_STATE
584124723950    29.04.15 13:23:38.000   <balance><name>B_LPP_Bs_Main</name><label></label><before>697.21429</before><after>797.21429</after><amount>100</amount><start></start><end></end><unit>Bs</unit></balance><balance><name>B_LPP_KB_National</name><label>PA_Adjustment</label><before>0</before><after>10240</after><amount>10240</amount><start>29042015000000</start><end>29052015000000</end><unit>Kbytes</unit></balance><balance><name>B_LSP_Bs_Promotions</name><label>PA_Adjustment</label><before>0</before><after>25</after><amount>25</amount><start>29042015000000</start><end>29052015000000</end><unit>Bs</unit></balance> ACT

But i need to break the IMPACTED_BALANCES field in columns. Anyone know how I do it?

Upvotes: 1

Views: 377

Answers (1)

Jon Heller
Jon Heller

Reputation: 36807

This is typically done using XMLTable

select
    msisdn, recharge_date,
    x_name, x_label, x_before, x_after, x_amount,
    to_date(x_start, 'DDMMYYYYHH24MISS') x_start,
    to_date(x_end, 'DDMMYYYYHH24MISS') x_end,
    x_unit,
    lc_state
from gold_dwh_reload
cross join
xmltable('/balances/balance'
    passing xmltype('<balances>'||impacted_balances||'</balances>')
    columns
        x_name path '/balance/name',
        x_label path '/balance/label',
        x_before number path '/balance/before',
        x_after number path '/balance/after',
        x_amount number path '/balance/amount',
        x_start path '/balance/start',
        x_end path '/balance/end',
        x_unit path '/balance/unit'
);

Here's a SQL Fiddle.

Mixing SQL and XML is powerful but creates many potential type safety issues. A single invalid date, number, or XML file will crash the whole query. The string in your example is not valid XML, that's why I concatenated another tag to the beginning and end.

Upvotes: 2

Related Questions