Forum Stats

  • 3,816,034 Users
  • 2,259,132 Discussions
  • 7,893,366 Comments

Discussions

Você quis dizer: transformar XML em colunas transform XML into columns

muttleychess
muttleychess Member Posts: 1,501 Bronze Badge
edited Jun 22, 2017 7:42AM in SQL & PL/SQL

Hi


How to transform an XML with several columns in a query

example


<?xml version="1.0" encoding="utf-8"?><ORDERS><ORDER CMD="calculate2"><INFORMANTE_EST_CODIGO>157162410</INFORMANTE_EST_CODIGO><SIS_CODIGO>XPTO</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>13/07/2016</DT_FATO_GERADOR_IMPOSTO><DT_REF_CALC_IMP>18/05/2016</DT_REF_CALC_IMP><IND_NACIONAL_ESTRANGEIRA>N</IND_NACIONAL_ESTRANGEIRA><UF_CODIGO_DESTINO>MG</UF_CODIGO_DESTINO><UF_CODIGO_ORIGEM>SP</UF_CODIGO_ORIGEM><MUN_PRES_SERVICO>3550308</MUN_PRES_SERVICO><NOP_CODIGO>E949.04</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>10000.00</PRECO_TOTAL><PRECO_UNITARIO>10000.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>0</OM_CODIGO><MERC_CLASSIFICATION><MERC_CLASS><ELEMENTO_MERCADORIA>A_GEN_STANDARD</ELEMENTO_MERCADORIA><CLASSE_MERCADORIA>PTC</CLASSE_MERCADORIA></MERC_CLASS></MERC_CLASSIFICATION><NBM_CODIGO>4901.91.00</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/><CEST_CODIGO/><VL_ISS/><VL_BASE_ISS/><ALIQ_ISS/><STI_CODIGO/><VL_IPI/><VL_BASE_IPI/><ALIQ_IPI/><STP_CODIGO/></ORDERLINE></ORDER></ORDERS>


Using

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0    Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

Tia

Best Answer

  • Paulzip
    Paulzip Member Posts: 8,673 Blue Diamond
    edited Jul 14, 2016 1:25PM Answer ✓

    If you want the data as row(s) with all values in, use the approach Sven demonstrated above.

    If you want key / value form (a row per element / attribute), here's an approach using recursive XQuery written by @odie_63

    with xml_test(the_xml) as (

      select xmltype(q'[<?xml version="1.0" encoding="utf-8"?><ORDERS><ORDER CMD="calculate2"><INFORMANTE_EST_CODIGO>157162410</INFORMANTE_EST_CODIGO><SIS_CODIGO>XPTO</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>13/07/2016</DT_FATO_GERADOR_IMPOSTO><DT_REF_CALC_IMP>18/05/2016</DT_REF_CALC_IMP><IND_NACIONAL_ESTRANGEIRA>N</IND_NACIONAL_ESTRANGEIRA><UF_CODIGO_DESTINO>MG</UF_CODIGO_DESTINO><UF_CODIGO_ORIGEM>SP</UF_CODIGO_ORIGEM><MUN_PRES_SERVICO>3550308</MUN_PRES_SERVICO><NOP_CODIGO>E949.04</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>10000.00</PRECO_TOTAL><PRECO_UNITARIO>10000.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>0</OM_CODIGO><MERC_CLASSIFICATION><MERC_CLASS><ELEMENTO_MERCADORIA>A_GEN_STANDARD</ELEMENTO_MERCADORIA><CLASSE_MERCADORIA>PTC</CLASSE_MERCADORIA></MERC_CLASS></MERC_CLASSIFICATION><NBM_CODIGO>4901.91.00</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/><CEST_CODIGO/><VL_ISS/><VL_BASE_ISS/><ALIQ_ISS/><STI_CODIGO/><VL_IPI/><VL_BASE_IPI/><ALIQ_IPI/><STP_CODIGO/></ORDERLINE></ORDER></ORDERS>doc

      from dual

    )

    select x.*

    from xml_test t

      , XMLTable(

        'declare function local:getChildren($e as node(), $pID as xs:string?) as element()*

          {

            for $i at $p in $e/(child::*|attribute::*)

            let $ID := if ($pID) then concat($pID,".",$p) else "1"

            return element r

            {

              element node_id {$ID}

            , element parent_node_id {$pID}

            , element node_name {name($i)}

            , if ($i instance of attribute())

                then ( element node_value {data($i)}, element node_kind {"attribute"} )

                else ( element node_value {$i/text()}, element node_kind {"element"} )

            }

            | local:getChildren($i,$ID)

          }; (: :)

          local:getChildren($d,())'

          passing t.the_xml as "d"

          columns node_id        varchar2(100)   path 'node_id'

                , node_name      varchar2(30)    path 'node_name'

                , node_value      varchar2(2000) path 'node_value'

                , parent_node_id  varchar2(100)  path 'parent_node_id'

                , node_kind      varchar2(30)    path 'node_kind'

        ) x

    ;

    NODE_IDNODE_NAMENODE_VALUEPARENT_NODE_IDNODE_KIND
    '1.1.28.49''STP_CODIGO''1.1.28''element'
    '1.1.28.48''ALIQ_IPI''1.1.28''element'
    '1.1.28.47''VL_BASE_IPI''1.1.28''element'
    '1.1.28.45''STI_CODIGO''1.1.28''element'
    '1.1.28.46''VL_IPI''1.1.28''element'
    '1.1.28.44''ALIQ_ISS''1.1.28''element'
    '1.1.28.42''VL_ISS''1.1.28''element'
    '1.1.28.43''VL_BASE_ISS''1.1.28''element'
    '1.1.28.41''CEST_CODIGO''1.1.28''element'
    '1.1.28.40''ALIQ_STF''1.1.28''element'
    '1.1.28.38''VL_STF''1.1.28''element'
    '1.1.28.39''VL_BASE_STF''1.1.28''element'
    '1.1.28.37''VL_ICMS_PART_REM''1.1.28''element'
    '1.1.28.35''ALIQ_ICMS_PART_REM''1.1.28''element'
    '1.1.28.36''PERC_ICMS_PART_REM''1.1.28''element'
    '1.1.28.34''VL_BASE_ICMS_PART_REM''1.1.28''element'
    '1.1.28.32''PERC_ICMS_PART_DEST''1.1.28''element'
    '1.1.28.33''VL_ICMS_PART_DEST''1.1.28''element'
    '1.1.3''SIS_CODIGO''XPTO''1.1''element'
    '1.1.2''INFORMANTE_EST_CODIGO''157162410''1.1''element'
    '1.1.1''CMD''calculate2''1.1''attribute'
    '1.1.28.31''ALIQ_ICMS_PART_DEST''1.1.28''element'
    '1.1.28.29''VL_ICMS_FCP''1.1.28''element'
    '1.1.28.30''VL_BASE_ICMS_PART_DEST''1.1.28''element'
    '1.1.28.28''ALIQ_ICMS_FCP''1.1.28''element'
    '1.1.28.26''STC_CODIGO''1.1.28''element'
    '1.1.28.27''ALIQ_DIFA_ICMS_PART''1.1.28''element'
    '1.1.28.25''ALIQ_ICMS''1.1.28''element'
    '1.1.28.24''VL_BASE_ICMS''1.1.28''element'
    '1.1.28.22''STN_CODIGO''1.1.28''element'
    '1.1.28.23''VL_ICMS''1.1.28''element'
    '1.1.28.21''VL_ALIQ_COFINS''1.1.28''element'
    '1.1.28.19''VL_IMPOSTO_COFINS''1.1.28''element'
    '1.1.28.20''VL_BASE_COFINS''1.1.28''element'
    '1.1.28.18''STA_CODIGO''1.1.28''element'
    '1.1.28.17''VL_ALIQ_PIS''1.1.28''element'
    '1.1.28.15''VL_IMPOSTO_PIS''1.1.28''element'
    '1.1.28.16''VL_BASE_PIS''1.1.28''element'
    '1.1.28.14''CFOP_CODIGO''1.1.28''element'
    '1.1.28.12''VL_TRIBUTAVEL_DIFA''1.1.28''element'
    '1.1.28.13''MENSAGEM_FISCAL''1.1.28''element'
    '1.1.28.11''SUBCLASSE_IDF''M''1.1.28''element'
    '1.1.28.9.1.2''CLASSE_MERCADORIA''PTC''1.1.28.9.1''element'
    '1.1.28.10''NBM_CODIGO''4901.91.00''1.1.28''element'
    '1.1.28.9.1.1''ELEMENTO_MERCADORIA''A_GEN_STANDARD''1.1.28.9.1''element'
    '1.1.28.9.1''MERC_CLASS''1.1.28.9''element'
    '1.1.28.8''OM_CODIGO''0''1.1.28''element'
    '1.1.28.9''MERC_CLASSIFICATION''1.1.28''element'
    '1.1.28.7''FIN_CODIGO''USO''1.1.28''element'
    '1.1.28.6''IND_VL_ICMS_NO_PRECO''S''1.1.28''element'
    '1.1.28.4''QTD''1''1.1.28''element'
    '1.1.28.5''IND_VL_PIS_COFINS_NO_PRECO''S''1.1.28''element'
    '1.1.28.3''PRECO_UNITARIO''10000.00''1.1.28''element'
    '1.1.28.2''PRECO_TOTAL''10000.00''1.1.28''element'
    '1.1.28''ORDERLINE''1.1''element'
    '1.1.28.1''IDF_NUM''1''1.1.28''element'
    '1.1.27''VL_TOTAL_ICMS_PART_REM''1.1''element'
    '1.1.26''VL_TOTAL_BASE_ICMS_PART_REM''1.1''element'
    '1.1.24''VL_TOTAL_BASE_ICMS_PART_DEST''1.1''element'
    '1.1.25''VL_TOTAL_ICMS_PART_DEST''1.1''element'
    '1.1.23''MENSAGEM_FISCAL''1.1''element'
    '1.1.21''IND_FISICA_JURIDICA''F''1.1''element'
    '1.1.22''ERRO''1.1''element'
    '1.1.20''IND_INSCRICAO_SUFRAMA''N''1.1''element'
    '1.1.18''IND_SUBSTITUTO_ICMS''N''1.1''element'
    '1.1.19''IND_SIMPLES_NACIONAL''N''1.1''element'
    '1.1.17''IND_CONTR_IPI''N''1.1''element'
    '1.1.16''IND_CONTR_ICMS''N''1.1''element'
    '1.1.14.1.2''ELEMENTO_PFJ_EMITENTE''1.1.14.1''element'
    '1.1.15''IND_ORGAO_GOVERNAMENTAL''N''1.1''element'
    '1.1.14.1.1''CLASSE_PFJ_EMITENTE''1.1.14.1''element'
    '1.1.14''PFJ_CLASSIFICATION''1.1''element'
    '1.1.14.1''PFJ_CLASS''1.1.14''element'
    '1.1.13''NOP_CODIGO''E949.04''1.1''element'
    '1.1.12''MUN_PRES_SERVICO''3550308''1.1''element'
    '1.1.10''UF_CODIGO_DESTINO''MG''1.1''element'
    '1.1.11''UF_CODIGO_ORIGEM''SP''1.1''element'
    '1.1.9''IND_NACIONAL_ESTRANGEIRA''N''1.1''element'
    '1.1.8''DT_REF_CALC_IMP''18/05/2016''1.1''element'
    '1.1.7''DT_FATO_GERADOR_IMPOSTO''13/07/2016''1.1''element'
    '1.1.5''IND_ENTRADA_SAIDA''S''1.1''element'
    '1.1.6''EDOF_CODIGO''NFE''1.1''element'
    '1.1.4''IND_USA_IF_CALC_IMP''S''1.1''element'
    '1''ORDERS''element'
    '1.1''ORDER''1''element'

    ;

Answers

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited Jul 14, 2016 12:48PM

    what do you mean by transform?

    What results do you want from the above XML?

  • Paulzip
    Paulzip Member Posts: 8,673 Blue Diamond
    edited Jul 14, 2016 12:51PM

    Transform how?  You want to flatten it?  Which columns do you want?

  • muttleychess
    muttleychess Member Posts: 1,501 Bronze Badge
    edited Jul 14, 2016 1:04PM

    Thank you

      I wanna to show data of all column inside XML,

    example:

    INFORMANTE_EST_CODIGO :=157162410;

    SIS_CODIGO := 'XPTO';

    IND_USA_IF_CALC_IMP :='S';

    .....

    ...

    UF_CODIGO_DESTINO := 'MG';

    ...ETC

  • Sven W.
    Sven W. Member Posts: 10,534 Gold Crown
    edited Jul 14, 2016 1:15PM

    Example:

    with xmldata as (select xmltype(q'[<?xml version="1.0" encoding="utf-8"?><ORDERS><ORDER CMD="calculate2"><INFORMANTE_EST_CODIGO>157162410</INFORMANTE_EST_CODIGO><SIS_CODIGO>XPTO</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>13/07/2016</DT_FATO_GERADOR_IMPOSTO><DT_REF_CALC_IMP>18/05/2016</DT_REF_CALC_IMP><IND_NACIONAL_ESTRANGEIRA>N</IND_NACIONAL_ESTRANGEIRA><UF_CODIGO_DESTINO>MG</UF_CODIGO_DESTINO><UF_CODIGO_ORIGEM>SP</UF_CODIGO_ORIGEM><MUN_PRES_SERVICO>3550308</MUN_PRES_SERVICO><NOP_CODIGO>E949.04</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>10000.00</PRECO_TOTAL><PRECO_UNITARIO>10000.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>0</OM_CODIGO><MERC_CLASSIFICATION><MERC_CLASS><ELEMENTO_MERCADORIA>A_GEN_STANDARD</ELEMENTO_MERCADORIA><CLASSE_MERCADORIA>PTC</CLASSE_MERCADORIA></MERC_CLASS></MERC_CLASSIFICATION><NBM_CODIGO>4901.91.00</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/><CEST_CODIGO/><VL_ISS/><VL_BASE_ISS/><ALIQ_ISS/><STI_CODIGO/><VL_IPI/><VL_BASE_IPI/><ALIQ_IPI/><STP_CODIGO/></ORDERLINE></ORDER></ORDERS>doc
                     from dual)
    select *
    from xmldata x
        ,xmltable('/ORDERS/ORDER'
                  passing  x.doc      
                  columns  order_code varchar2(100) path 'INFORMANTE_EST_CODIGO'
                  )
        ,xmltable('/ORDERS/ORDER/ORDERLINE'
                  passing  x.doc      
                  columns  idf      varchar2(100) path 'IDF_NUM'
                          ,price    varchar2(100) path 'PRECO_TOTAL'
                          ,quantity varchar2(100) path 'QTD'
                  )
    ;
    
    
    

    ORDER_CODE    IDF    PRICE    QUANTITY

    157162410    1    10000.00    1

    There are more and better possibilities using FLWOR syntax

    http://www.oracle.com/technetwork/articles/grid/o55xquery-097999.html

  • Paulzip
    Paulzip Member Posts: 8,673 Blue Diamond
    edited Jul 14, 2016 1:25PM Answer ✓

    If you want the data as row(s) with all values in, use the approach Sven demonstrated above.

    If you want key / value form (a row per element / attribute), here's an approach using recursive XQuery written by @odie_63

    with xml_test(the_xml) as (

      select xmltype(q'[<?xml version="1.0" encoding="utf-8"?><ORDERS><ORDER CMD="calculate2"><INFORMANTE_EST_CODIGO>157162410</INFORMANTE_EST_CODIGO><SIS_CODIGO>XPTO</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>13/07/2016</DT_FATO_GERADOR_IMPOSTO><DT_REF_CALC_IMP>18/05/2016</DT_REF_CALC_IMP><IND_NACIONAL_ESTRANGEIRA>N</IND_NACIONAL_ESTRANGEIRA><UF_CODIGO_DESTINO>MG</UF_CODIGO_DESTINO><UF_CODIGO_ORIGEM>SP</UF_CODIGO_ORIGEM><MUN_PRES_SERVICO>3550308</MUN_PRES_SERVICO><NOP_CODIGO>E949.04</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>10000.00</PRECO_TOTAL><PRECO_UNITARIO>10000.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>0</OM_CODIGO><MERC_CLASSIFICATION><MERC_CLASS><ELEMENTO_MERCADORIA>A_GEN_STANDARD</ELEMENTO_MERCADORIA><CLASSE_MERCADORIA>PTC</CLASSE_MERCADORIA></MERC_CLASS></MERC_CLASSIFICATION><NBM_CODIGO>4901.91.00</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/><CEST_CODIGO/><VL_ISS/><VL_BASE_ISS/><ALIQ_ISS/><STI_CODIGO/><VL_IPI/><VL_BASE_IPI/><ALIQ_IPI/><STP_CODIGO/></ORDERLINE></ORDER></ORDERS>doc

      from dual

    )

    select x.*

    from xml_test t

      , XMLTable(

        'declare function local:getChildren($e as node(), $pID as xs:string?) as element()*

          {

            for $i at $p in $e/(child::*|attribute::*)

            let $ID := if ($pID) then concat($pID,".",$p) else "1"

            return element r

            {

              element node_id {$ID}

            , element parent_node_id {$pID}

            , element node_name {name($i)}

            , if ($i instance of attribute())

                then ( element node_value {data($i)}, element node_kind {"attribute"} )

                else ( element node_value {$i/text()}, element node_kind {"element"} )

            }

            | local:getChildren($i,$ID)

          }; (: :)

          local:getChildren($d,())'

          passing t.the_xml as "d"

          columns node_id        varchar2(100)   path 'node_id'

                , node_name      varchar2(30)    path 'node_name'

                , node_value      varchar2(2000) path 'node_value'

                , parent_node_id  varchar2(100)  path 'parent_node_id'

                , node_kind      varchar2(30)    path 'node_kind'

        ) x

    ;

    NODE_IDNODE_NAMENODE_VALUEPARENT_NODE_IDNODE_KIND
    '1.1.28.49''STP_CODIGO''1.1.28''element'
    '1.1.28.48''ALIQ_IPI''1.1.28''element'
    '1.1.28.47''VL_BASE_IPI''1.1.28''element'
    '1.1.28.45''STI_CODIGO''1.1.28''element'
    '1.1.28.46''VL_IPI''1.1.28''element'
    '1.1.28.44''ALIQ_ISS''1.1.28''element'
    '1.1.28.42''VL_ISS''1.1.28''element'
    '1.1.28.43''VL_BASE_ISS''1.1.28''element'
    '1.1.28.41''CEST_CODIGO''1.1.28''element'
    '1.1.28.40''ALIQ_STF''1.1.28''element'
    '1.1.28.38''VL_STF''1.1.28''element'
    '1.1.28.39''VL_BASE_STF''1.1.28''element'
    '1.1.28.37''VL_ICMS_PART_REM''1.1.28''element'
    '1.1.28.35''ALIQ_ICMS_PART_REM''1.1.28''element'
    '1.1.28.36''PERC_ICMS_PART_REM''1.1.28''element'
    '1.1.28.34''VL_BASE_ICMS_PART_REM''1.1.28''element'
    '1.1.28.32''PERC_ICMS_PART_DEST''1.1.28''element'
    '1.1.28.33''VL_ICMS_PART_DEST''1.1.28''element'
    '1.1.3''SIS_CODIGO''XPTO''1.1''element'
    '1.1.2''INFORMANTE_EST_CODIGO''157162410''1.1''element'
    '1.1.1''CMD''calculate2''1.1''attribute'
    '1.1.28.31''ALIQ_ICMS_PART_DEST''1.1.28''element'
    '1.1.28.29''VL_ICMS_FCP''1.1.28''element'
    '1.1.28.30''VL_BASE_ICMS_PART_DEST''1.1.28''element'
    '1.1.28.28''ALIQ_ICMS_FCP''1.1.28''element'
    '1.1.28.26''STC_CODIGO''1.1.28''element'
    '1.1.28.27''ALIQ_DIFA_ICMS_PART''1.1.28''element'
    '1.1.28.25''ALIQ_ICMS''1.1.28''element'
    '1.1.28.24''VL_BASE_ICMS''1.1.28''element'
    '1.1.28.22''STN_CODIGO''1.1.28''element'
    '1.1.28.23''VL_ICMS''1.1.28''element'
    '1.1.28.21''VL_ALIQ_COFINS''1.1.28''element'
    '1.1.28.19''VL_IMPOSTO_COFINS''1.1.28''element'
    '1.1.28.20''VL_BASE_COFINS''1.1.28''element'
    '1.1.28.18''STA_CODIGO''1.1.28''element'
    '1.1.28.17''VL_ALIQ_PIS''1.1.28''element'
    '1.1.28.15''VL_IMPOSTO_PIS''1.1.28''element'
    '1.1.28.16''VL_BASE_PIS''1.1.28''element'
    '1.1.28.14''CFOP_CODIGO''1.1.28''element'
    '1.1.28.12''VL_TRIBUTAVEL_DIFA''1.1.28''element'
    '1.1.28.13''MENSAGEM_FISCAL''1.1.28''element'
    '1.1.28.11''SUBCLASSE_IDF''M''1.1.28''element'
    '1.1.28.9.1.2''CLASSE_MERCADORIA''PTC''1.1.28.9.1''element'
    '1.1.28.10''NBM_CODIGO''4901.91.00''1.1.28''element'
    '1.1.28.9.1.1''ELEMENTO_MERCADORIA''A_GEN_STANDARD''1.1.28.9.1''element'
    '1.1.28.9.1''MERC_CLASS''1.1.28.9''element'
    '1.1.28.8''OM_CODIGO''0''1.1.28''element'
    '1.1.28.9''MERC_CLASSIFICATION''1.1.28''element'
    '1.1.28.7''FIN_CODIGO''USO''1.1.28''element'
    '1.1.28.6''IND_VL_ICMS_NO_PRECO''S''1.1.28''element'
    '1.1.28.4''QTD''1''1.1.28''element'
    '1.1.28.5''IND_VL_PIS_COFINS_NO_PRECO''S''1.1.28''element'
    '1.1.28.3''PRECO_UNITARIO''10000.00''1.1.28''element'
    '1.1.28.2''PRECO_TOTAL''10000.00''1.1.28''element'
    '1.1.28''ORDERLINE''1.1''element'
    '1.1.28.1''IDF_NUM''1''1.1.28''element'
    '1.1.27''VL_TOTAL_ICMS_PART_REM''1.1''element'
    '1.1.26''VL_TOTAL_BASE_ICMS_PART_REM''1.1''element'
    '1.1.24''VL_TOTAL_BASE_ICMS_PART_DEST''1.1''element'
    '1.1.25''VL_TOTAL_ICMS_PART_DEST''1.1''element'
    '1.1.23''MENSAGEM_FISCAL''1.1''element'
    '1.1.21''IND_FISICA_JURIDICA''F''1.1''element'
    '1.1.22''ERRO''1.1''element'
    '1.1.20''IND_INSCRICAO_SUFRAMA''N''1.1''element'
    '1.1.18''IND_SUBSTITUTO_ICMS''N''1.1''element'
    '1.1.19''IND_SIMPLES_NACIONAL''N''1.1''element'
    '1.1.17''IND_CONTR_IPI''N''1.1''element'
    '1.1.16''IND_CONTR_ICMS''N''1.1''element'
    '1.1.14.1.2''ELEMENTO_PFJ_EMITENTE''1.1.14.1''element'
    '1.1.15''IND_ORGAO_GOVERNAMENTAL''N''1.1''element'
    '1.1.14.1.1''CLASSE_PFJ_EMITENTE''1.1.14.1''element'
    '1.1.14''PFJ_CLASSIFICATION''1.1''element'
    '1.1.14.1''PFJ_CLASS''1.1.14''element'
    '1.1.13''NOP_CODIGO''E949.04''1.1''element'
    '1.1.12''MUN_PRES_SERVICO''3550308''1.1''element'
    '1.1.10''UF_CODIGO_DESTINO''MG''1.1''element'
    '1.1.11''UF_CODIGO_ORIGEM''SP''1.1''element'
    '1.1.9''IND_NACIONAL_ESTRANGEIRA''N''1.1''element'
    '1.1.8''DT_REF_CALC_IMP''18/05/2016''1.1''element'
    '1.1.7''DT_FATO_GERADOR_IMPOSTO''13/07/2016''1.1''element'
    '1.1.5''IND_ENTRADA_SAIDA''S''1.1''element'
    '1.1.6''EDOF_CODIGO''NFE''1.1''element'
    '1.1.4''IND_USA_IF_CALC_IMP''S''1.1''element'
    '1''ORDERS''element'
    '1.1''ORDER''1''element'

    ;

This discussion has been closed.