This discussion is archived
2 Replies Latest reply: Sep 10, 2012 11:52 AM by 878240 RSS

ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequ

878240 Newbie
Currently Being Moderated
When I query from the XMLTYPE column SAMSEDAILY using this query, it throws the ORA-19279 error after it returns a number of records. It seems like it happens at the first record when the OrgWON or WON columns are populated. Those columns are empty for most records.

SELECT WON, OrgWON, SerialNbr
FROM MGT2.samsedaily_ld t,
XMLTable(
XMLNamespaces('http://www.colde.com/namespace/samse' as "sam"),
'/sam:SamseDaily/UIC/Equipment'
passing t.SAMSEDAILY
columns
SerialNbr varchar2(30) path './SerialNbr',
OrgWON varchar2(30) path './MaintActivity/OrigWO/OrgWON',
WON varchar2(30) path './MaintActivity/OrigWO/WON'
)


When I do the query this way there is no error but it seems like it joins each non-null OrgWON or WON column with the SerialNbr in a cartesion join.

SELECT WON, OrgWON, SerialNbr
FROM MGT2.samsedaily_ld t,
XMLTable(
XMLNamespaces('http://www.colde.com/namespace/samse' as "sam"),
'/sam:SamseDaily/UIC/Equipment'
passing t.SAMSEDAILY
columns
SerialNbr varchar2(30) path './SerialNbr'
) x,
XMLTable(
XMLNamespaces('http://www.colde.com/namespace/samse' as "sam"),
'/sam:SamseDaily/UIC/Equipment/MaintActivity/OrigWO'
passing t.SAMSEDAILY
columns
OrgWON varchar2(30) path './OrgWON',
WON varchar2(30) path './WON'
) y
  • 1. Re: ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequ
    Jason_(A_Non) Expert
    Currently Being Moderated
    As the error message implies, the error happens because your XPaths indicate that 0 or 1 occurrences will be found and the XML contains 2 or more occurrences. In this case, I'm assuming two or more occurrences of MaintActivity/OrigWO.

    Your second attempt was close in regards to what you put together, but the missing item was you needed to join the two XMLTables together by passing results from one to the other. What you are looking for is something like this
    WITH samsedaily_ld AS
    (SELECT XMLTYPE('<sam:SamseDaily xmlns:sam="http://www.colde.com/namespace/samse">
       <UIC>
          <Equipment>
             <SerialNbr>sn1</SerialNbr>
             <MaintActivity>
                <OrigWO>
                   <OrgWON>orgwon1</OrgWON>
                   <WON>won1</WON>
                </OrigWO>
             </MaintActivity>
             <MaintActivity>
                <OrigWO>
                   <OrgWON>orgwon2</OrgWON>
                   <WON>won2</WON>
                </OrigWO>
             </MaintActivity>
          </Equipment>
          <Equipment>
             <SerialNbr>sn2</SerialNbr>
          </Equipment>
         </UIC>
    </sam:SamseDaily>') samsedaily from dual)
    -- The above simulates your table since I don't have it
    SELECT WON, OrgWON, SerialNbr
      FROM MGT2.samsedaily_ld t,
           XMLTable(XMLNamespaces('http://www.colde.com/namespace/samse' as "sam"),
                       '/sam:SamseDaily/UIC/Equipment'
                    passing t.SAMSEDAILY
                    columns
                    SerialNbr   varchar2(30) path 'SerialNbr',
                    WOXML XMLTYPE PATH 'MaintActivity/OrigWO') x,
           XMLTable('/OrigWO'
                    passing x.WOXML
                    columns
                    OrgWON varchar2(30) path 'OrgWON',
                    WON varchar2(30) path 'WON') (+) y;
    which produces
    WON                            ORGWON                         SERIALNBR
    ------------------------------ ------------------------------ ------------------------------
    won1                           orgwon1                        sn1
    won2                           orgwon2                        sn1
                                                                  sn2
    Since you mentioned that most occurrences of OrgWON/WON are empty, I took that to assume there is no MaintActivity/OrigWO node in the XML. This meant I needed to outer join the second XMLTable to the first, so you will note the ( +) after the second XMLTable and before the table alias name of "y". Just like you would do in SQL against tables that reside in the DB. As you did not provide any sample XML, you will need to adjust the SQL accordingly if I assumed wrong.
  • 2. Re: ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequ
    878240 Newbie
    Currently Being Moderated
    That worked. Thanks a lot!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points