Forum Stats

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

Discussions

Categories

Struggling with 3 level XML

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

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:

image.png


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

image.png


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

Regards,

Jose.

· Share on TwitterShare on Facebook

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


    · Share on TwitterShare on Facebook

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


    · Share on TwitterShare on Facebook
  • Jose Aróstegui
    Jose Aróstegui Member Posts: 1,350 Silver Badge

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

    · Share on TwitterShare on Facebook