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!!!