DevOps, CI/CD and Automation

Hi!

I'm using Oracle 10gR2.

How can I extract the value from the IUM tag? Is this sintax correct (an element repeated many times without a group above it)?

When it's an attribute of cxMed tag, I can do what I need (code below).

  WITH tmp AS
  (SELECT XMLTYPE('<root>
                    <inf>
                      <det nItem="1">
                        <prod>
                          <med>
                            <nLote>lote 0</nLote>
                            <qLote>0</qLote>
                            <dFab>2011-03-01</dFab>
                            <dVal>2012-03-01</dVal>
                            <vPMC>300</vPMC>
                          </med>
                          <med>
                            <nLote>lote 1</nLote>
                            <qLote>1</qLote>
                            <dFab>2011-01-01</dFab>
                            <dVal>2012-01-01</dVal>
                            <vPMC>100</vPMC>
                            <IUM>1</IUM>
                            <IUM>2</IUM>
                            <IUM>3</IUM>
                            <IUM>4</IUM>
                            <IUM>5</IUM>
                          </med>
                          <med>
                            <nLote>lote 2</nLote>
                            <qLote>2</qLote>
                            <dFab>2011-02-02</dFab>
                            <dVal>2012-02-02</dVal>
                            <vPMC>200</vPMC>
                            <cxMed IUM="1">
                              <IUC>21</IUC>
                            </cxMed>
                            <cxMed IUM="2">
                              <IUC>22</IUC>
                            </cxMed>
                            <cxMed IUM="3">
                              <IUC>23</IUC>
                            </cxMed>
                          </med>
                          <med>
                            <nLote>lote 4</nLote>
                            <qLote>4</qLote>
                            <dFab>2011-04-01</dFab>
                            <dVal>2012-04-01</dVal>
                            <vPMC>300</vPMC>
                          </med>
                        </prod>
                      </det>
                      <det nItem="2">
                        <prod>
                          <med>
                            <nLote>lote 1</nLote>
                            <qLote>1</qLote>
                            <dFab>2011-01-01</dFab>
                            <dVal>2012-01-01</dVal>
                            <vPMC>100</vPMC>
                            <IUM>1</IUM>
                            <IUM>2</IUM>
                            <IUM>3</IUM>
                            <IUM>4</IUM>
                            <IUM>5</IUM>
                          </med>
                        </prod>
                      </det>
                    </inf>
                  </root>') as XMLArquivo from dual
  )
          SELECT --/NFe/infNFe/det
                 vw.nItem,
                 --/NFe/infNFe/det/prod/med
                 vw2.IdMedicamento,
                 vw3.IdIUM,
                 vw3.IUM,
                 vw3.IUC
            FROM tmp,
            XMLTABLE('/root/inf/det'
                     PASSING tmp.XMLArquivo
                     COLUMNS
                       nItem  NUMBER(3) PATH '@nItem', 
                       medXML XMLTYPE   PATH 'prod/med') vw,
            XMLTABLE('/med'
                     PASSING vw.medXML
                     COLUMNS
                       IdMedicamento FOR ORDINALITY,
                       cxMedXML XMLTYPE PATH 'cxMed') vw2,
            XMLTABLE('/cxMed'
                     PASSING vw2.cxMedXML
                     COLUMNS
                       IdIUM FOR ORDINALITY,
                       IUM NUMBER(13) PATH '@IUM',
                       IUC NUMBER(18) PATH 'IUC') vw3

Thanks,
Luciana

Edited by: Luciana on Jan 21, 2011 8:33 AM

Edited by: Luciana on Jan 21, 2011 8:34 AM

Edited by: Luciana on Jan 21, 2011 9:47 AM