2 Replies Latest reply: Apr 9, 2012 1:17 AM by 928885 RSS

    Reading XMLTYPE Column With Parent and Multiple Child Nodes

    928885
      Hi,

      I have a xmltype column which stores the data in below format. There are possible that some of the parent node will have multiple child nodes. I am not able to extract the child nodes. Getting the error as multiset value... singleton required...
      I tried the below query but it returns only the records of parent with child nodes.

      <CrntRgstns>
      <CrntRgstn empStDt="2000-08-21" Auth="ABCD" regCat="BM" u4FlngDt="2011-05-12" stDt="2011-11-08" st="PURGED" actvReg="N" updateTS="2011-11-08T00:37:42">
      <CrntDfcnys/>
      </CrntRgstn>
      <CrntRgstn empStDt="2000-08-21" Auth="ABCD" regCat="BM" u4FlngDt="2012-01-13" stDt="2012-01-13" st="DEFICIENT" actvReg="Y" updateTS="2012-01-13T18:02:19">
      <CrntDfcnys>
      <CrntDfcny dfcnyCd="EXAM" exmCd="M10" createDt="2012-01-13"/>
      <CrntDfcny dfcnyCd="EXAM" exmCd="X40" createDt="2012-01-13"/>
      </CrntDfcnys>
      </CrntRgstn>
      <CrntRgstn empStDt="2000-08-21" Auth="XYZ" regCat="SU" u4FlngDt="2012-01-13" stDt="2012-01-13" st="DEFICIENT" actvReg="Y" updateTS="2012-01-13T18:02:19">
      <CrntDfcnys>
      <CrntDfcny dfcnyCd="EXAM" exmCd="M10" createDt="2012-01-13"/>
      <CrntDfcny dfcnyCd="EXAM" exmCd="X40" createDt="2012-01-13"/>
      </CrntDfcnys>
      </CrntRgstn>
      </CrntRgstns>

      I tried using 2 xmltable querys and using child node of xmltable 1 as input to xmltable 2. But this will return only those records which has child nodes. But i wanted all records from CrntRgstn and if any child node CrntDfcnys then display that also.

      Thanks,
      Vinod K
        • 1. Re: Reading XMLTYPE Column With Parent and Multiple Child Nodes
          odie_63
          I tried using 2 xmltable querys and using child node of xmltable 1 as input to xmltable 2. But this will return only those records which has child nodes.
          You're almost there, you just have to make it an outer join :
          SQL> select x1.empStDt
            2       , x1.Auth
            3       , x1.regCat
            4       , x2.*
            5  from documents t
            6     , xmltable(
            7         '/CrntRgstns/CrntRgstn'
            8         passing t.xmldoc
            9         columns empStDt date         path '@empStDt'
           10               , Auth    varchar2(30) path '@Auth'
           11               , regCat  varchar2(2)  path '@regCat'
           12               , CrntDfcnys xmltype   path 'CrntDfcnys'
           13       ) (+) x1
           14     , xmltable(
           15         '/CrntDfcnys/CrntDfcny'
           16         passing x1.CrntDfcnys
           17         columns dfcnyCd  varchar2(30) path '@dfcnyCd'
           18               , exmCd    varchar2(3)  path '@exmCd'
           19               , createDt date         path '@createDt'
           20       ) (+) x2
           21  ;
           
          EMPSTDT     AUTH                           REGCAT DFCNYCD                        EXMCD CREATEDT
          ----------- ------------------------------ ------ ------------------------------ ----- -----------
          21/08/2000  ABCD                           BM                                          
          21/08/2000  ABCD                           BM     EXAM                           M10   13/01/2012
          21/08/2000  ABCD                           BM     EXAM                           X40   13/01/2012
          21/08/2000  XYZ                            SU     EXAM                           M10   13/01/2012
          21/08/2000  XYZ                            SU     EXAM                           X40   13/01/2012
           
          Note : I've tested the above on 11.2.0.2 and it looks like there's a bug if we don't add an outer join operator on the first XMLTable too.
          Normally, the first outer join is not necessary, so you can also test without it on your version.
          • 2. Re: Reading XMLTYPE Column With Parent and Multiple Child Nodes
            928885
            Thank you so much odie. This worked for me. I was trying the outer join using only on 1 xmltable.

            Thanks,
            Vinod K