DECLARE
x XMLType := XMLType(
'<?xml version="1.0" encoding="utf-8"?>
<ORDERS>
<ORDER CMD="calculate2">
<INFORMANTE_EST_CODIGO>157162410</INFORMANTE_EST_CODIGO>
<SIS_CODIGO>SYNCHRO</SIS_CODIGO>
<IND_USA_IF_CALC_IMP>S</IND_USA_IF_CALC_IMP>
<IND_ENTRADA_SAIDA>S</IND_ENTRADA_SAIDA>
<EDOF_CODIGO>NFE</EDOF_CODIGO>
<DT_FATO_GERADOR_IMPOSTO>01/01/2016</DT_FATO_GERADOR_IMPOSTO>
<DT_REF_CALC_IMP>01/01/2016</DT_REF_CALC_IMP>
<IND_NACIONAL_ESTRANGEIRA>N</IND_NACIONAL_ESTRANGEIRA>
<UF_CODIGO_DESTINO>MA</UF_CODIGO_DESTINO>
<UF_CODIGO_ORIGEM>SP</UF_CODIGO_ORIGEM>
<MUN_PRES_SERVICO>3550308</MUN_PRES_SERVICO>
<NOP_CODIGO>S108.01</NOP_CODIGO>
<PFJ_CLASSIFICATION>
<PFJ_CLASS>
<CLASSE_PFJ_EMITENTE/>
<ELEMENTO_PFJ_EMITENTE/>
</PFJ_CLASS>
</PFJ_CLASSIFICATION>
<IND_ORGAO_GOVERNAMENTAL>N
</IND_ORGAO_GOVERNAMENTAL>
<IND_CONTR_ICMS>N</IND_CONTR_ICMS>
<IND_CONTR_IPI>N</IND_CONTR_IPI>
<IND_SUBSTITUTO_ICMS>N</IND_SUBSTITUTO_ICMS>
<IND_SIMPLES_NACIONAL>N</IND_SIMPLES_NACIONAL>
<IND_INSCRICAO_SUFRAMA>N</IND_INSCRICAO_SUFRAMA>
<IND_FISICA_JURIDICA>F</IND_FISICA_JURIDICA>
<ERRO/>
<MENSAGEM_FISCAL/>
<VL_TOTAL_BASE_ICMS_PART_DEST/>
<VL_TOTAL_ICMS_PART_DEST/>
<VL_TOTAL_BASE_ICMS_PART_REM/>
<VL_TOTAL_ICMS_PART_REM/>
<ORDERLINE>
<IDF_NUM>1</IDF_NUM>
<PRECO_TOTAL>100.00</PRECO_TOTAL>
<PRECO_UNITARIO>100.00</PRECO_UNITARIO>
<QTD>1</QTD>
<IND_VL_PIS_COFINS_NO_PRECO>S</IND_VL_PIS_COFINS_NO_PRECO>
<IND_VL_ICMS_NO_PRECO>S</IND_VL_ICMS_NO_PRECO>
<FIN_CODIGO>USO</FIN_CODIGO>
<OM_CODIGO>2</OM_CODIGO>
<MERC_CLASSIFICATION>
<MERC_CLASS>
<ELEMENTO_MERCADORIA>A_GEN_STANDARD</ELEMENTO_MERCADORIA>
<CLASSE_MERCADORIA>PTC</CLASSE_MERCADORIA>
</MERC_CLASS>
</MERC_CLASSIFICATION>
<NBM_CODIGO>8473.40.90</NBM_CODIGO>
<SUBCLASSE_IDF>M</SUBCLASSE_IDF>
<VL_TRIBUTAVEL_DIFA/>
<MENSAGEM_FISCAL/>
<CFOP_CODIGO/>
<VL_IMPOSTO_PIS/>
<VL_BASE_PIS/>
<VL_ALIQ_PIS/>
<STA_CODIGO/>
<VL_IMPOSTO_COFINS/>
<VL_BASE_COFINS/>
<VL_ALIQ_COFINS/>
<STN_CODIGO/>
<VL_ICMS/>
<VL_BASE_ICMS/>
<ALIQ_ICMS/>
<STC_CODIGO/>
<ALIQ_DIFA_ICMS_PART/>
<ALIQ_ICMS_FCP/>
<VL_ICMS_FCP/>
<VL_BASE_ICMS_PART_DEST/>
<ALIQ_ICMS_PART_DEST/>
<PERC_ICMS_PART_DEST/>
<VL_ICMS_PART_DEST/>
<VL_BASE_ICMS_PART_REM/>
<ALIQ_ICMS_PART_REM/>
<PERC_ICMS_PART_REM/>
<VL_ICMS_PART_REM/>
<VL_STF/>
<VL_BASE_STF/>
<ALIQ_STF/>
<VL_ISS/>
<VL_BASE_ISS/>
<ALIQ_ISS/>
<STI_CODIGO/>
<VL_IPI/>
<VL_BASE_IPI/>
<ALIQ_IPI/>
<STP_CODIGO/>
</ORDERLINE>
</ORDER>
</ORDERS>');
BEGIN
FOR r IN (
SELECT ExtractValue(Value(p),'/ORDERS/ORDER/INFORMANTE_EST_CODIGO/text()') as INFORMANTE
,ExtractValue(Value(p),'/ORDERS/ORDER/SIS_CODIGO/text()') as SIS_CODIGO
FROM TABLE(XMLSequence(Extract(x,'/ORDERS'))) p
) LOOP
DBMS_OUTPUT.put_line( R.INFORMANTE);
DBMS_OUTPUT.put_line( R.SIS_CODIGO);
END LOOP;
END;
But I have many others fields/columns and I do know how can to do when in the tag ORDERLINE there are more one , example 2,3 or more
Is there a way to to generate a query or dbms_output with all tags and to generate columns ?