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.