Veamos el caso con un ejemplo, tenemos un XML con datos de paises, vemos que como primer elemento esta <datos> y como elementos hijos están los países y con los países lo datos relacionados(id, cod, nombre). Dentro del dato hay dos pisases.
<datos><pais><id>1</id><cod>ECU</cod><nombre>Ecuador</nombre></pais><pais><id>2</id><cod>COL</cod><nombre>Colombia</nombre></pais></datos>
El formato XML lo vamos a tranformar en una estructura que permita tratarlo como una tabla y con ello podamos realizar consultas.
SELECT *FROM TABLE (XMLSEQUENCE (EXTRACT (XMLTYPE.CREATEXML ('<datos><pais><id>1</id><cod>ECU</cod><nombre>Ecuador</nombre></pais><pais><id>2</id><cod>COL</cod><nombre>Colombia</nombre></pais></datos>' ), '/'))) PAIS
Si ejecutamos el sql solo nos devolverá un solo registro con el campo COLUMN_VALUE con el formato XML.
Antes de avanzar para ver como recorrer los registro que conforman el XML primero les explicaré como fue que se armo la consulta con las funciones utilizadas del lado del from.
- XMLTYPE.CREATEXML Convierte texto con formato XML en un tipo de dato XML. Utilizamos el XML de países de ejemplo.
- EXTRACT Extraemos los elementos del XML en el nivel /, todo el XML.
- XMLSEQUENCE Extrae los nodos dentro del XML.
- TABLE Transforma en tabla los resultados del XMLSEQUENCE.
Ahora si, ya explicado las funciones del lado del from vamos a obtener cada uno de los registro de país. Para ellos vamos a indicar en el EXTRACT el parametro '/datos/pais' para que así recorramos todos los elementos de país.
SELECT *FROM TABLE (XMLSEQUENCE (EXTRACT (XMLTYPE.CREATEXML ('<datos><pais><id>1</id><cod>ECU</cod><nombre>Ecuador</nombre></pais><pais><id>2</id><cod>COL</cod><nombre>Colombia</nombre></pais></datos>' ), '/datos/pais'))) PAIS
El resultado sera todos los elementos de pais, pero todavía nos falta obtener el dato de cada campo de país, para ello vamos a usar la función ExtractValue para obtener cada uno de los campos del registro.
SELECT ExtractValue(Value(PAIS),'/pais/id/text()')id,ExtractValue(Value(PAIS),'/pais/cod/text()')cod,ExtractValue(Value(PAIS),'/pais/nombre/text()')nombreFROM TABLE (XMLSEQUENCE (EXTRACT (XMLTYPE.CREATEXML ('<datos><pais><id>1</id><cod>ECU</cod><nombre>Ecuador</nombre></pais><pais><id>2</id><cod>COL</cod><nombre>Colombia</nombre></pais></datos>' ), '/datos/pais'))) PAIS
Nota:
XMLSEQUENCE es una función que se uso desde la versión 10g y sigue funcionando en versiones superiores pero en la versión 11g se incluyó la XMLTable como una mejora.
