How to parse XML data in pipe delimited format and insert into table

Question

<Status>SUCCESS</Status>
<Count>7</Count>
<Data>
    <Content>S|123|03011990|5236158|19901254189684|</Content>
    <Content>S|456|02011991|2584959|19916584159385|</Content>
</Data>

I need to parse the data and insert into table.

INSERT INTO ins_data(TYPE,VNO,F_DATE,F_NO,F_CNO).

I want to see the output like below when i query the table.

S 123 03011990 5236158 19901254189684

S 456 02011991 2584959 19916584159385

Answer 1

There is a lot going on in this query so I will explain it in steps. This is the full query:

with D as
(select xmltype('<Root>
<Status>SUCCESS</Status>
<Count>7</Count>
<Data>
    <Content>S|123|03011990|5236158|19901254189684|</Content>
    <Content>S|456|02011991|2584959|19916584159385|</Content>
</Data>
</Root>') dataStr from dual)

select *
from(
    select id, trim(column_value) text,
           rank() over(partition by id order by rownum) pos
    from(
        select rownum id, extract(column_value, 'Content/text()') text
        from d,
        table(xmlsequence(extract(dataStr,'Root/Data/*'))) x
    ),
    xmltable(('"' || REPLACE(text, '|', '","') || '"'))
)pivot(
    max(text) for pos in (1 as TYPE, 2 as VNO, 3 as F_DATE, 4 as F_NO, 5 as F_CNO)
)

My with clause is using the xmltype command to turn the xml string into xml data.

The next piece is this one:

select rownum id, extract(column_value, 'Content/text()') text
  from d,
  table(xmlsequence(extract(dataStr,'Root/Data/*'))) x

This uses the xmlsequence command to extract the rows from the object in the XML. I am assigning each row an ID using rownum. I will need that field later.

The next part is a nifty trick I picked up from this site

select id, trim(column_value) text,
           rank() over(partition by id order by rownum) pos
    from(
        select rownum id, extract(column_value, 'Content/text()') text
        from d,
        table(xmlsequence(extract(dataStr,'Root/Data/*'))) x
    ),
    xmltable(('"' || REPLACE(text, '|', '","') || '"'))

It uses the XML table command and REPLACE function to split each value using the delimiter of "|". I use the trim command around the value in order to convert the XML data to strings. I also use a rank windowing function in order to pivot on in the next step.

The final part is this:

select *
from(
    select id, trim(column_value) text,
           rank() over(partition by id order by rownum) pos
    from(
        select rownum id, extract(column_value, 'Content/text()') text
        from d,
        table(xmlsequence(extract(dataStr,'Root/Data/*'))) x
    ),
    xmltable(('"' || REPLACE(text, '|', '","') || '"'))
)pivot(
    max(text) for pos in (1 as TYPE, 2 as VNO, 3 as F_DATE, 4 as F_NO, 5 as F_CNO)
)

I pivot on the "pos" rank column I made in the last step and give each piece the column name you indicated.

My final results look like this:

ID  TYPE    VNO     F_DATE      F_NO        F_CNO
1   S       123     03011990    5236158     19901254189684
2   S       456     02011991    2584959     19916584159385