1 2 Previous Next 21 Replies Latest reply: Mar 27, 2013 10:47 PM by user12136582 Go to original post RSS
      • 15. Re: ORA-19279: XQuery dynamic type mismatch:expected singleton sequence-mul
        mdrake-Oracle
        Much easier once I have an instacne to work with....
        SQL> drop table tmp_xml
          2  /
        
        Table dropped.
        
        Elapsed: 00:00:00.10
        SQL> create table tmp_xml of xmltype
          2  /
        
        Table created.
        
        Elapsed: 00:00:00.06
        SQL> insert into tmp_xml values (xmltype(
          2  '<P>
          3     <IdSegment>
          4             <EyeColor>BRO</EyeColor>
          5             <HairColor>BLK</HairColor>
          6             <Height>511</Height>
          7             <Race>W</Race>
          8             <Gender>M</Gender>
          9             <UID>228021</UID>
         10             <ScarsMarksTattoos>SC L KNEE</ScarsMarksTattoos>
         11             <ScarsMarksTattoos>TAT L BUTK</ScarsMarksTattoos>
         12             <ScarsMarksTattoos>TAT R BUTK</ScarsMarksTattoos>
         13             <ScarsMarksTattoos>TAT R ARM</ScarsMarksTattoos>
         14     </IdSegment>
         15  </P>'))
         16  /
        
        1 row created.
        
        Elapsed: 00:00:00.01
        SQL> select extractValue(value(smt),'/ScarsMarksTattoos')
          2  from tmp_xml,
          3  table(xmlsequence(extract(object_value,'//P//IdSegment' ))) id,
          4  table(xmlsequence(extract(value(id),'/IdSegment/ScarsMarksTattoos'))) smt
          5  /
        
        EXTRACTVALUE(VALUE(SMT),'/SCARSMARKSTATTOOS')
        --------------------------------------------------------------------------------
        SC L KNEE
        TAT L BUTK
        TAT R BUTK
        TAT R ARM
        
        Elapsed: 00:00:00.51
        SQL>
        BTW I assume in the real world there can be multiple 'IdSegment' elements within a P element but that a P element cannot occur within an IgSegment. If so then a more efficient query would be
        SQL> select extractValue(value(smt),'/ScarsMarksTattoos')
          2  from tmp_xml,
          3  table(xmlsequence(extract(object_value,'/P/IdSegment' ))) id,
          4  table(xmlsequence(extract(value(id),'/IdSegment/ScarsMarksTattoos'))) smt
          5  /
        
        EXTRACTVALUE(VALUE(SMT),'/SCARSMARKSTATTOOS')
        --------------------------------------------------------------------------------
        SC L KNEE
        TAT L BUTK
        TAT R BUTK
        TAT R ARM
        
        Elapsed: 00:00:00.03
        SQL>
        which removes the '//' from the Path Expression.

        How big are the documents you are processing...
        • 16. Re: ORA-19279: XQuery dynamic type mismatch:expected singleton sequence-mul
          mdrake-Oracle
          BTW your original XMLTable approach will work in ScarsMarksTattos is the sequenced element
          SQL> SELECT X.*
            2    FROM TMP_XML P2,
            3         XMLTable
            4         (
            5            '//P//IdSegment/ScarsMarksTattoos'
            6            PASSING P2.object_value
            7            COLUMNS
            8            "Seqno" FOR ORDINALITY,
            9            "ScarsMarksTattoos" varchar(30) PATH '.'
           10         ) AS X
           11  /
          
               Seqno ScarsMarksTattoos
          ---------- ------------------------------
                   1 SC L KNEE
                   2 TAT L BUTK
                   3 TAT R BUTK
                   4 TAT R ARM
          
          Elapsed: 00:00:00.04
          SQL>
          • 17. Re: ORA-19279: XQuery dynamic type mismatch:expected singleton sequence-mul
            503668
            you are corect on the Idsegment
            and this worked,

            how would I modify this to get the UID for the Scarsmarksand tattoos so I have a relational table ?

            the files I have are supplied and range from very small (most of them) up to about 10K.
            • 18. Re: ORA-19279: XQuery dynamic type mismatch:expected singleton sequence-mul
              503668
              ahh
              the original SQL is preferred but is there any way to get the unique ID (UID) to relate the recurring data columns back to the original records ?
              • 19. Re: ORA-19279: XQuery dynamic type mismatch:expected singleton sequence-mul
                mdrake-Oracle
                Is this what you are looking for ?
                SQL> SELECT id."UID", smt.*
                  2    FROM TMP_XML P2
                  3         , XMLTable
                  4         (
                  5            '/P/IdSegment'
                  6            PASSING P2.object_value
                  7            COLUMNS
                  8            "UID" varchar2(32) path 'UID',
                  9            "ScarsMarksTattoos" XMLType PATH 'ScarsMarksTattoos'
                 10         ) AS id
                 11         , XMLTable
                 12         (
                 13            'ScarsMarksTattoos'
                 14            PASSING id."ScarsMarksTattoos"
                 15            COLUMNS
                 16            "Seqno" FOR ORDINALITY,
                 17            "ScarsMarksTattoos" varchar(30) PATH '.'
                 18         ) AS smt
                 19  /
                
                UID                                   Seqno ScarsMarksTattoos
                -------------------------------- ---------- ------------------------------
                228021                                    1 SC L KNEE
                228021                                    2 TAT L BUTK
                228021                                    3 TAT R BUTK
                228021                                    4 TAT R ARM
                • 20. Re: ORA-19279: XQuery dynamic type mismatch:expected singleton sequence-mul
                  853310
                  Dear mdrake

                  Your XMLType suggestion helps me ...

                  thank you.

                  Arunachalam.C
                  1 2 Previous Next