with mvr_dtl (vendor_xml) as (
select to_clob('<Output name="ReadableResponse">
<Envelope>
<Body>
<GetReportResponse>
<GetReportResult>
<report>
<report_data>
<generation_date_time>11/14/2022 2:32:35 PM</generation_date_time>
<paypal_id>308897373</paypal_id>
<report_id>127417202</report_id>
<contract_detail_included_cnt>1</contract_detail_included_cnt>
<sic_codes>
<sic_code code="0339">GUZZMANN AND SONS</sic_code>
</sic_codes>
<managers>
<manager title="Secretary">DIANA LEMKE</manager>
</managers>
<corporate_registration>
<registration_state/>
<registration_date_incorporated>1900-01-01</registration_date_incorporated>
<registration_business_type>CORPORATE</registration_business_type>
<registration_status/>
</corporate_registration>
<total_employees/>
<years_in_business>24</years_in_business>
</report_data>
</report>
</GetReportResult>
</GetReportResponse>
</Body>
</Envelope>
</Output>')
from dual
)
select x.*
from mvr_dtl m,
xmltable ( '/Output/Envelope/Body/GetReportResponse/GetReportResult/report/report_data'
passing xmltype( m.vendor_xml )
returning sequence by ref
columns
paypal_id varchar2(32) path 'paypal_id',
report_id varchar2(32) path 'report_id',
manager varchar2(32) path 'managers',
title varchar2(32) path 'managers/manager/@title',
code varchar2(32) path 'sic_codes/sic_code/@code',
sic_codes varchar2(32) path 'sic_codes'
) as x;