Link to home
Start Free TrialLog in
Avatar of mmoore
mmooreFlag for United States of America

asked on

XMLTABLE XQuery dynamic type mismatch

I do understand why this throws this error:
ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

What I would like to get as a result is a record with a nested table column. 

    SELECT x.*
      FROM XMLTABLE (
              '/batch/testload'
              PASSING xmltype (q'[<batch>
<testload>
    <Operator>IN</Operator>
    <Operator>OUT</Operator>
    <Attribute>DoorsDouble</Attribute>
</testload>
<testload>
   <Operator>IN</Operator>
   <Attribute>WindowsSingleMultiple</Attribute>
</testload>
</batch>]')
              COLUMNS operator1 VARCHAR2 (200) PATH 'Operator',
                      attribute2 VARCHAR2 (200) PATH 'Attribute') x;

Open in new window


I would like the result set to look something like the below image where Operator is a nested table column.
User generated image
I tried using COLLECT but there seems to be a type mismatch.
Thanks,
Mike
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mmoore
mmoore
Flag of United States of America image

ASKER

Ah, the key to it was using XMLTYPE as the datatype for operators. You also took it a step further and normalized the output. Now I can make the whole thing a sub query and have the outer query COLLECT Operator into a nested table. Thanks!