Parse XML to Oracle SQL

Full dynamic solution is not easy to implement.The main problem is generating dynamic amount of column in result set. To achieve this you have to utilize sophisticated technique :) example: Dyn pipeline
But if you can assume limit of level in your xml it will be much easier.

with xml_data as (select xmltype('<?xml version="1.0" encoding="UTF-8"?>
<MSH>
   <MSH.1>|</MSH.1>
   <MSH.2>lal</MSH.2>
   <MSH.3>
      <HD.1>LAB</HD.1>
   </MSH.3>
   <MSH.4>
      <HD.1>767543</HD.1>
   </MSH.4>
   <MSH.5>
      <HD.1>ADT</HD.1>
   </MSH.5>
   <MSH.6>
      <HD.1>767543</HD.1>
   </MSH.6>
   <MSH.7>199003141304-0500</MSH.7>
   <MSH.9>
      <CM_MSG.1>ACK</CM_MSG.1>
      <CM_MSG.3>ACK_ACK</CM_MSG.3>
   </MSH.9>
   <MSH.10>XX3657</MSH.10>
   <MSH.11>
      <PT.1>P</PT.1>
   </MSH.11>
   <MSH.12>
      <VID.1>2.4</VID.1>
   </MSH.12>
</MSH>') xd from dual)
select x.* from xml_data
,xmltable('//*[not(*)]' passing xd 
 columns  
    n_level_1    varchar2(4000) path '(ancestor-or-self::*/name(.))[1]'
   ,n_level_2   varchar2(4000) path '(ancestor-or-self::*/name(.))[2]'
   ,n_level_3   varchar2(4000) path '(ancestor-or-self::*/name(.))[3]'
   ,n_level_4   varchar2(4000) path '(ancestor-or-self::*/name(.))[4]'
   ,n_level_5   varchar2(4000) path '(ancestor-or-self::*/name(.))[5]'
   ,n_level_6   varchar2(4000) path '(ancestor-or-self::*/name(.))[6]'
   ,n_level_7   varchar2(4000) path '(ancestor-or-self::*/name(.))[7]'
   ,n_level_8   varchar2(4000) path '(ancestor-or-self::*/name(.))[8]'
   ,n_level_9   varchar2(4000) path '(ancestor-or-self::*/name(.))[9]'
   ,n_level_10  varchar2(4000) path '(ancestor-or-self::*/name(.))[10]'    
   --...n times
   ,n_value    varchar2(4000)  path './text()'  
 ) x;