Forum Stats

  • 3,825,196 Users
  • 2,260,479 Discussions
  • 7,896,435 Comments

Discussions

Struggling with 3 level XML

Jose Aróstegui
Jose Aróstegui Member Posts: 1,350 Silver Badge

Hi experts,

I'm using database 12.2.0.1 and I have this sample SQL with XML data to parse in 3 levels:

WITH data AS (SELECT xmltype ('<?xml version="1.0" encoding="UTF-8"?>
<xml>
 <albaranelectronicoah>
  <numero_albaran>50J24691</numero_albaran>
  <fecha_albaran>2021-08-02</fecha_albaran>
  <linea>
   <numero_linea>1</numero_linea>
   <codigo_articulo>7134619</codigo_articulo>
   <lotes>
    <lote>96925100</lote>
    <lote/>
    <lote/>
    <lote/>
    <lote/>   
   </lotes>
  </linea>
  <linea>
   <numero_linea>2</numero_linea>
   <codigo_articulo>7134619</codigo_articulo>
   <lotes>
    <lote>06999400</lote>
    <lote>06910300</lote>
    <lote/>
    <lote/>
    <lote/>
   </lotes>
  </linea>
  <linea>
   <numero_linea>3</numero_linea>
   <codigo_articulo>7134633</codigo_articulo>
   <lotes>
    <lote>06910300</lote>
    <lote>06999400</lote>
    <lote/>
    <lote/>
  <lote/>
   </lotes>
  </linea>
 </albaranelectronicoah>
</xml>') AS xml FROM DUAL)
--
SELECT level_one.*, level_two.*, level_three.*
 FROM data r,
    XMLTABLE ('/xml/albaranelectronicoah'
         PASSING r.xml
         COLUMNS numero_albaran VARCHAR2 (400) PATH 'numero_albaran',
             fecha_albaran VARCHAR2 (400) PATH 'fecha_albaran',
             --
             lineas XMLTYPE PATH 'linea') level_one,
    XMLTABLE (
      'linea'
      PASSING level_one.lineas
      COLUMNS numero_linea VARCHAR2 (10)
            PATH 'numero_linea',
          codigo_articulo VARCHAR2 (20)
            PATH 'codigo_articulo',
          --   
          lotes XMLTYPE
            PATH '/linea/lotes') level_two,
    XMLTABLE ('/'
         PASSING level_two.lotes
         COLUMNS lote VARCHAR2 (50) PATH '/lotes') level_three 


My problem is with the 3rd level data (lotes), as I'm supposed to obtain this:


but instead of having the yellow rows I get this concatenated value:


Not too experienced with this so I'd really appreciate the help.

Regards,

Jose.

Best Answer

  • Jason_(A_Non)
    Jason_(A_Non) Member Posts: 2,094 Silver Trophy
    Answer ✓

    You were close. I basically adjusted how you had your level_three table setup is all. I also had it filter out the empty nodes, but you could have done that via "WHERE level_three.lote is not null" as well.

    WITH data AS (SELECT xmltype ('<?xml version="1.0" encoding="UTF-8"?>
    <xml>
     <albaranelectronicoah>
     <numero_albaran>50J24691</numero_albaran>
     <fecha_albaran>2021-08-02</fecha_albaran>
     <linea>
      <numero_linea>1</numero_linea>
      <codigo_articulo>7134619</codigo_articulo>
      <lotes>
       <lote>96925100</lote>
       <lote/>
       <lote/>
       <lote/>
       <lote/>  
      </lotes>
     </linea>
     <linea>
      <numero_linea>2</numero_linea>
      <codigo_articulo>7134619</codigo_articulo>
      <lotes>
       <lote>06999400</lote>
       <lote>06910300</lote>
       <lote/>
       <lote/>
       <lote/>
      </lotes>
     </linea>
     <linea>
      <numero_linea>3</numero_linea>
      <codigo_articulo>7134633</codigo_articulo>
      <lotes>
       <lote>06910300</lote>
       <lote>06999400</lote>
       <lote/>
       <lote/>
     <lote/>
      </lotes>
     </linea>
     </albaranelectronicoah>
    </xml>') AS xml FROM DUAL)
    --
    SELECT level_one.numero_albaran, level_one.fecha_albaran, 
          level_two.numero_linea, level_two.codigo_articulo,
          level_three.lote
     FROM data r,
       XMLTABLE ('/xml/albaranelectronicoah'
                 PASSING r.xml
                 COLUMNS numero_albaran VARCHAR2 (400) PATH 'numero_albaran',
                         fecha_albaran VARCHAR2 (400) PATH 'fecha_albaran',
                         --
                         lineas XMLTYPE PATH 'linea') level_one,
       XMLTABLE ('/linea'
                 PASSING level_one.lineas
                 COLUMNS numero_linea   VARCHAR2 (10) PATH 'numero_linea',
                         codigo_articulo VARCHAR2 (20) PATH 'codigo_articulo',
                         --  
                         lotes XMLTYPE PATH 'lotes') level_two,
       XMLTABLE ('/lotes/lote[text() != ""]'
                 PASSING level_two.lotes
                 COLUMNS 
                 lote VARCHAR2 (50) PATH '.') level_three 
    

    I think 12.2 was the first to support accessing parent nodes so you could simplify that SQL down to

    SELECT level_all.*
     FROM data r,
       XMLTABLE ('/xml/albaranelectronicoah/linea/lotes/lote[text() != ""]'
                 PASSING r.xml
                 COLUMNS numero_albaran VARCHAR2 (400) PATH './../../../numero_albaran',
                         fecha_albaran  VARCHAR2 (400) PATH './../../../fecha_albaran',
                         numero_linea   VARCHAR2 (10) PATH './../../numero_linea',
                         codigo_articulo VARCHAR2 (20) PATH './../../codigo_articulo',
                         lote           VARCHAR2 (50) PATH '.') level_all
    


Answers

  • Jason_(A_Non)
    Jason_(A_Non) Member Posts: 2,094 Silver Trophy
    Answer ✓

    You were close. I basically adjusted how you had your level_three table setup is all. I also had it filter out the empty nodes, but you could have done that via "WHERE level_three.lote is not null" as well.

    WITH data AS (SELECT xmltype ('<?xml version="1.0" encoding="UTF-8"?>
    <xml>
     <albaranelectronicoah>
     <numero_albaran>50J24691</numero_albaran>
     <fecha_albaran>2021-08-02</fecha_albaran>
     <linea>
      <numero_linea>1</numero_linea>
      <codigo_articulo>7134619</codigo_articulo>
      <lotes>
       <lote>96925100</lote>
       <lote/>
       <lote/>
       <lote/>
       <lote/>  
      </lotes>
     </linea>
     <linea>
      <numero_linea>2</numero_linea>
      <codigo_articulo>7134619</codigo_articulo>
      <lotes>
       <lote>06999400</lote>
       <lote>06910300</lote>
       <lote/>
       <lote/>
       <lote/>
      </lotes>
     </linea>
     <linea>
      <numero_linea>3</numero_linea>
      <codigo_articulo>7134633</codigo_articulo>
      <lotes>
       <lote>06910300</lote>
       <lote>06999400</lote>
       <lote/>
       <lote/>
     <lote/>
      </lotes>
     </linea>
     </albaranelectronicoah>
    </xml>') AS xml FROM DUAL)
    --
    SELECT level_one.numero_albaran, level_one.fecha_albaran, 
          level_two.numero_linea, level_two.codigo_articulo,
          level_three.lote
     FROM data r,
       XMLTABLE ('/xml/albaranelectronicoah'
                 PASSING r.xml
                 COLUMNS numero_albaran VARCHAR2 (400) PATH 'numero_albaran',
                         fecha_albaran VARCHAR2 (400) PATH 'fecha_albaran',
                         --
                         lineas XMLTYPE PATH 'linea') level_one,
       XMLTABLE ('/linea'
                 PASSING level_one.lineas
                 COLUMNS numero_linea   VARCHAR2 (10) PATH 'numero_linea',
                         codigo_articulo VARCHAR2 (20) PATH 'codigo_articulo',
                         --  
                         lotes XMLTYPE PATH 'lotes') level_two,
       XMLTABLE ('/lotes/lote[text() != ""]'
                 PASSING level_two.lotes
                 COLUMNS 
                 lote VARCHAR2 (50) PATH '.') level_three 
    

    I think 12.2 was the first to support accessing parent nodes so you could simplify that SQL down to

    SELECT level_all.*
     FROM data r,
       XMLTABLE ('/xml/albaranelectronicoah/linea/lotes/lote[text() != ""]'
                 PASSING r.xml
                 COLUMNS numero_albaran VARCHAR2 (400) PATH './../../../numero_albaran',
                         fecha_albaran  VARCHAR2 (400) PATH './../../../fecha_albaran',
                         numero_linea   VARCHAR2 (10) PATH './../../numero_linea',
                         codigo_articulo VARCHAR2 (20) PATH './../../codigo_articulo',
                         lote           VARCHAR2 (50) PATH '.') level_all
    


  • Jose Aróstegui
    Jose Aróstegui Member Posts: 1,350 Silver Badge

    Gorgeous! Thanks a lot a Jason! I was heavily stuck.