with sample as (
                select  xmltype(Q'[<?xml version="1.0"?>
<FixInsightReport version="2016.04upd2">
<file name="uUtil.pas">
<message line="82" col="1" id="C101">C101 Method 'Reform' is too long (77 lines)</message>
<message line="78" col="21" id="C102">C102 Too many parameters in 'Reform' (12 parameters)</message>
<message line="97" col="7" id="C108">C108 Nested WITH statement</message>
<message line="123" col="7" id="C108">C108 Nested WITH statement</message>
<message line="138" col="7" id="C108">C108 Nested WITH statement</message>
</file>
<file name="uSave.pas">
<message line="45" col="7" id="C108">C108 Nested WITH statement</message>
<message line="54" col="13" id="C108">C108 Nested WITH statement</message>
<message line="77" col="7" id="C108">C108 Nested WITH statement</message>
<message line="85" col="13" id="C108">C108 Nested WITH statement</message>
<message line="133" col="7" id="C108">C108 Nested WITH statement</message>
</file>
</FixInsightReport>]') xmldoc from dual
)
select  file_name,
        message,
        line,
        col,
        id
  from  sample,
        xmltable(
                 '/FixInsightReport/file'
                 passing xmldoc
                 columns
                   file_name   varchar2(10) path '@name',
                   message_xml xmltype      path 'message'
                ),
       xmltable(
                 '/message'
                 passing message_xml
                 columns
                   message varchar2(52) path '.',
                   line    number       path '@line',
                   col     number       path '@col',
                   id      varchar2(5)  path '@id'
                )
/

FILE_NAME  MESSAGE                                                    LINE        COL ID
---------- ---------------------------------------------------- ---------- ---------- -----
uUtil.pas  C101 Method 'Reform' is too long (77 lines)                  82          1 C101
uUtil.pas  C102 Too many parameters in 'Reform' (12 parameters)         78         21 C102
uUtil.pas  C108 Nested WITH statement                                   97          7 C108
uUtil.pas  C108 Nested WITH statement                                  123          7 C108
uUtil.pas  C108 Nested WITH statement                                  138          7 C108
uSave.pas  C108 Nested WITH statement                                   45          7 C108
uSave.pas  C108 Nested WITH statement                                   54         13 C108
uSave.pas  C108 Nested WITH statement                                   77          7 C108
uSave.pas  C108 Nested WITH statement                                   85         13 C108
uSave.pas  C108 Nested WITH statement                                  133          7 C108

10 rows selected.

SQL>