PLSQL XMLTABLE examples

sample xml

<?xml version="1.0" encoding="ISO-8859-1"?>
<cls>
    <kopregel>
        <verwerking>2022-11-01</verwerking>
    </kopregel>
    <cbk>
        <bnm>Codeboek Sagitta, onderdeel KIS</bnm>
        <tbl>
            <tnr>K10</tnr>
            <tnm>TYPE ZEKERHEID</tnm>
            <elm>
                <ecd>AZH</ecd>
                <oms>ACTIVITEITZEKERHEID</oms>
                <wet />
                <byz />
            </elm>
        </tbl>
        <tbl>
            <tnr>K18</tnr>
            <tnm>DISPENSATIE</tnm>
            <elm>
                <ecd>1</ecd>
                <oms>DWU art 89 lid 7 Staten, overheid, publiekrhtlk lichaam</oms>
                <wet />
                <byz />
            </elm>
            <elm>
                <ecd>15</ecd>
                <oms>DWU art 89 lid 9 &lt; 1000,00</oms>
                <wet />
                <byz />
            </elm>
            <elm>
                <ecd>17</ecd>
                <oms>DWU art 89 lid 8a Rijn en Donau</oms>
                <wet />
                <byz />
            </elm>
            <elm>
                <ecd>18</ecd>
                <oms>DWU art 89 lid 8b goederen in vaste transportinrichting</oms>
                <wet />
                <byz />
            </elm>
            <elm>
                <ecd>19</ecd>
                <oms>DWU art 89 lid 8c Tijdelijk Invoer goed</oms>
                <wet />
                <byz />
            </elm>
            <elm>
                <ecd>20</ecd>
                <oms>DWU art 89 lid 8d Verv met vereenv. over Zee, lucht), hvns</oms>
                <wet />
                <byz />
            </elm>
            <elm>
                <ecd>3</ecd>
                <oms>DWU art 95 lid 2 en lid 3</oms>
                <wet />
                <byz />
            </elm>
        </tbl>
    </cbk>
</cls>

      Cursor c_codelist(cp_xml In xmltype) Is
         Select xt.tnr
               ,xt.tnm
               ,xt.elm
           From xmltable('for $i in (//*:tbl)
                   return
                   <a>
                     <tnr>{$i/*:tnr}</tnr>
                     <tnm>{$i/*:tnm}</tnm>
                     <elm>{$i/*:elm}</elm>
                   </a>' passing(cp_xml) columns tnr Varchar2(500) path 'tnr'
                        ,tnm Varchar2(500) path 'tnm'
                        ,elm xmltype path 'elm') xt;

      Cursor c_codelist_values(cp_xml In xmltype) Is
         Select xt.value
               ,xt.short_description
               ,xt.long_description
               ,xt.legal_description
           From xmltable('for $i in (//*:elm)
                       return
                         <a>
                           <value>{$i/*:ecd}</value>
                           <short_description>{$i/*:oms}</short_description>
                           <legal_description>{$i/*:wet}</legal_description>
                           <long_description>{$i/*:byz}</long_description>
                         </a>' passing(cp_xml) columns Value Varchar2(200) path 'value'
                        ,short_description Varchar2(4000) path 'short_description'
                        ,long_description Varchar2(4000) path 'long_description'
                        ,legal_description Varchar2(4000) path 'legal_description') xt
          Where xt.value Is Not Null;