Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

XMLSequence to XMLTable

754973Feb 22 2010 — edited Feb 22 2010
Hi!

I need a help to use XMLTable instead of XMLSequence.

This is the part of my XML.
         <emiDocAnt>
            <owner>11111111111111</owner>
            <idDocAnt>
              <idDocAntPap>
                <tpDoc>01</tpDoc>
              </idDocAntPap>
              <idDocAntPap>
                <tpDoc>02</tpDoc>
              </idDocAntPap>
            </idDocAnt>
            <idDocAnt>
              <idDocAntPap>
                <tpDoc>03</tpDoc>
              </idDocAntPap>
              <idDocAntPap>
                <tpDoc>04</tpDoc>
              </idDocAntPap>
            </idDocAnt>
          </emiDocAnt>
          <emiDocAnt>
            <owner>22222222222222</owner>
            <idDocAnt>
              <idDocAntPap>
                <tpDoc>05</tpDoc>
              </idDocAntPap>
              <idDocAntPap>
                <tpDoc>06</tpDoc>
              </idDocAntPap>
            </idDocAnt>
          </emiDocAnt>
I have this statement that works fine, it retruns the data inside the idDocAntPap group and its owner.
SELECT EXTRACTVALUE(VALUE(vw),'/emiDocAnt/owner'),
       EXTRACTVALUE(VALUE(vw3), '/idDocAntPap/tpDoc')
  FROM tb_projCargaTMP,
  TABLE(XMLSequence(EXTRACT(XMLAutorizacao,'/emiDocAnt'))) vw,
  TABLE(XMLSequence(EXTRACT(vw.column_value,'//idDocAnt'))) vw2,
  TABLE(XMLSequence(EXTRACT(vw2.column_value,'//idDocAntPap'))) vw3;
But I'd like to use XMLTable instead of XMLSequence. The problem is: how can I get the "owner" value?
SELECT vw."owner",
       vw."tpDoc"
  FROM tb_projCargaTMP,
  XMLTABLE('/emiDocAnt/idDocAnt/idDocAntPap'
           PASSING tb_CargaTMP.XMLAutorizacao
           COLUMNS "owner" NUMBER(14)     PATH ???, <-- How can I get the owner value?
                   "tpDoc" NUMBER(2)      PATH '/idDocAntPap/tpDoc',
                   "serie" VARCHAR2(3)    PATH '/idDocAntPap/serie',
                   "subserie" VARCHAR2(2) PATH '/idDocAntPap/subser',
                   "nDoc" NUMBER(20)      PATH '/idDocAntPap/nDoc',
                   "dEmi" DATE            PATH '/idDocAntPap/dEmi'
           ) vw;
Thanks!!!
This post has been answered by Jason_(A_Non) on Feb 22 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 22 2010
Added on Feb 22 2010
2 comments
2,719 views