9 Replies Latest reply: Mar 6, 2014 1:16 PM by odie_63 RSS

    Yet another "ORA-19279: XPTY0004 - XQuery dynamic type mismatch"

    backintheday

      I have traversed this forum for a couple days and am unable to find a solution that matches my problem - close, but not quite.  I have some data that is repeated just once - the third set of data as seen below.  I need to be able to retrieve tabular data where all information is returned and repeated ITEMNUMBER and ITEMAMOUNT rows have the appropriate DETAILID and TRACEID data.  Hope that makes sense.  I have tried many variations of setting up XMLTables in my SELECT queries to no avail, receiving the "ORA-19279: XPTY0004" error.  Here is the XML followed by the desired outcome:

       

      <Result>
        <Payment>
          <PmtDetail>
            <DetailId>111131</DetailId>
            <PmtRequest>        
              <TraceId>800116</TraceId>
              <Item>
                <ItemNumber>1</ItemNumber>
                <ItemAmount>1234.50</ItemAmount>
              </Item>
            </PmtRequest>
            <DetailId>000535</DetailId>
            <PmtRequest>
              <TraceId>800131</TraceId>
              <Item>
                <ItemNumber>1</ItemNumber>
                <ItemAmount>23.06</ItemAmount>
              </Item>
            </PmtRequest>
            <DetailId>166483</DetailId>
            <PmtRequest> 
              <TraceId>800229</TraceId>
              <Item>
                <ItemNumber>1</ItemNumber>
                <ItemAmount>450</ItemAmount>
                <ItemNumber>2</ItemNumber>
                <ItemAmount>1302.08</ItemAmount>
                <ItemNumber>3</ItemNumber>
                <ItemAmount>677</ItemAmount>
              </Item>
            </PmtRequest>
          </PmtDetail>
        </Payment>
      </Result>
      

       

      DETAILID  TRACEID  ITEMNUMBER  ITEMAMOUNT

      --------  -------  ----------  ----------

        111131   800116           1     1234.50

        000535   800131           1       23.06

        166483   800229           1         450

        166483   800229           2     1302.08

        166483   800229           3         677

       

      Thanks in advance,

      -Seth.

        • 1. Re: Yet another "ORA-19279: XPTY0004 - XQuery dynamic type mismatch"
          Jason_(A_Non)

          What version of Oracle? aka select * from v$version

           

          Is it possible to have the XML modified to add a grouping node around each DetailId/PmtRequest group.  If not, is it safe to assume those two always appear next to each other and only once per group?

           

          This looks to be an XQuery solution based on what you have provided so far, but just checking before I go explore that route.

          • 2. Re: Yet another "ORA-19279: XPTY0004 - XQuery dynamic type mismatch"
            backintheday

            Thank you, Jason, I am using Enterprise Edition, 11.2.0.3.

             

            I am not able to modify the XML; but, yes, they appear next to each other and only once per group.

             

            -Seth.

            • 3. Re: Yet another "ORA-19279: XPTY0004 - XQuery dynamic type mismatch"
              Jason_(A_Non)

              Based on similar work from Odie found at

              Xquery parent attribute

              Parsing XML

              Re: column type not supported on external organized table

              I present to you

              WITH your_table AS (SELECT XMLTYPE('<Result>  
                    <Payment>  
                      <PmtDetail>  
                        <DetailId>111131</DetailId>  
                        <PmtRequest>          
                          <TraceId>800116</TraceId>  
                          <Item>  
                            <ItemNumber>1</ItemNumber>  
                            <ItemAmount>1234.50</ItemAmount>  
                          </Item>  
                        </PmtRequest>  
                        <DetailId>000535</DetailId>  
                        <PmtRequest>  
                          <TraceId>800131</TraceId>  
                          <Item>  
                            <ItemNumber>1</ItemNumber>  
                            <ItemAmount>23.06</ItemAmount>  
                          </Item>  
                        </PmtRequest>  
                        <DetailId>166483</DetailId>  
                        <PmtRequest>   
                          <TraceId>800229</TraceId>  
                          <Item>  
                            <ItemNumber>1</ItemNumber>  
                            <ItemAmount>450</ItemAmount>  
                            <ItemNumber>2</ItemNumber>  
                            <ItemAmount>1302.08</ItemAmount>  
                            <ItemNumber>3</ItemNumber>  
                            <ItemAmount>677</ItemAmount>  
                          </Item>  
                        </PmtRequest>  
                      </PmtDetail>  
                    </Payment>  
                  </Result>') your_col FROM dual)
              SELECT detailid, traceid, itemnumber, itemamount
                FROM your_table,
                     XMLTABLE('for $detailId in /Result/Payment/PmtDetail/DetailId
                               return element r {
                               $detailId/following-sibling::PmtRequest[1],
                               $detailId}'
                              PASSING your_table.your_col
                              COLUMNS 
                              detailid      VARCHAR2(10) PATH 'DetailId',
                              pmtRequestXML XMLTYPE      PATH 'PmtRequest') xt,
                     XMLTABLE('for $pmtRequest in /PmtRequest,
                                   $itemNumber in $pmtRequest/Item/ItemNumber
                               return element r{
                               $pmtRequest/TraceId,
                               $itemNumber/following-sibling::ItemAmount[1],
                               $itemNumber}'
                              PASSING xt.pmtRequestXML
                              COLUMNS
                              traceid       NUMBER   PATH 'TraceId',
                              itemnumber    NUMBER   PATH 'ItemNumber',
                              itemamount    NUMBER   PATH 'ItemAmount') xt2;
              

               

              which produces

              DETAILID  TRACEID ITEMNUMBER ITEMAMOUNT

              ---------- ---------- ---------- ----------

              111131     800116      1 1234.5
              000535     800131      23.06
              166483     800229      1    450
              166483     800229      21302.08
              166483     800229      3    677

               

              You may want to play with the returned datatypes, but that gets you what you need.

              • 4. Re: Yet another "ORA-19279: XPTY0004 - XQuery dynamic type mismatch"
                backintheday

                Thank you, Jason.  It works!  You took all those posts and put them together where I would have run in circles for days trying to do that. Let me just say, I dove into XML DB just a few days ago and am way over my head in all this, so I appreciate your help

                 

                I ran into another problem, however.  What I posted was, of course, sample data.  When I tried the query against my real XML that was stored in a table/column as XMLTYPE, I received the following error:

                 

                ORA-19244: XQTY0024 - invalid attribute node in element constructor

                Cause: The content sequence in an element constructor contained an attribute node following a node that was not an attribute node.

                Action: Change the content sequence of the element constructor so that it does not contain the attribute node.


                Honestly, I don't know what that means; and googling or looking on support forums returns nothing.

                 

                To test this further, I then extracted some XML straight from the file that was loaded into my XMLTYPE column and used that in my WITH clause, and it worked fine.  What's the difference?  Here are the SELECT statements - first having the WITH clause (returning no error) and the second selecting from my XMLTYPE table/column (returning the error):

                 

                WITH your_table AS ( SELECT XMLTYPE ('<?xml version="1.0"?>
                <Result xmlns="http://schemas.fromsomewhere.com/Result.v4">
                ...
                [XML copied from file]
                ...
                </Result>') AS your_col FROM dual)
                SELECT detailid, traceid, itemnumber, itemamount  
                  FROM your_table,  
                       XMLTABLE('for $detailId in /Result/Payment/PmtDetail/DetailId  
                                 return element r {  
                                 $detailId/following-sibling::PmtRequest[1],  
                                 $detailId}'  
                                PASSING your_table.your_col  
                                COLUMNS   
                                detailid      VARCHAR2(10) PATH 'DetailId',  
                                pmtRequestXML XMLTYPE      PATH 'PmtRequest') xt,  
                       XMLTABLE('for $pmtRequest in /PmtRequest,  
                                     $itemNumber in $pmtRequest/Item/ItemNumber  
                                 return element r{  
                                 $pmtRequest/TraceId,  
                                 $itemNumber/following-sibling::ItemAmount[1],  
                                 $itemNumber}'  
                                PASSING xt.pmtRequestXML  
                                COLUMNS  
                                traceid       NUMBER   PATH 'TraceId',  
                                itemnumber    NUMBER   PATH 'ItemNumber',  
                                itemamount    NUMBER   PATH 'ItemAmount') xt2; 
                

                 

                SELECT detailid, traceid, itemnumber, itemamount  
                  FROM my_table,  
                       XMLTABLE(
                         XMLNamespaces(default 'http://schemas.fromsomewhere.com/Result.v4')
                       , 'for $detailId in /Result/Payment/PmtDetail/DetailId  
                                 return element r {  
                                 $detailId/following-sibling::PmtRequest[1],  
                                 $detailId}'  
                                PASSING my_table.xml_data  
                                COLUMNS   
                                detailid      VARCHAR2(10) PATH 'DetailId',  
                                pmtRequestXML XMLTYPE      PATH 'PmtRequest') xt, 
                       XMLTABLE(
                         XMLNamespaces(default 'http://schemas.fromsomewhere.com/Result.v4')
                       , 'for $pmtRequest in /PmtRequest,  
                                     $itemNumber in $pmtRequest/Item/ItemNumber  
                                 return element r{  
                                 $pmtRequest/TraceId,  
                                 $itemNumber/following-sibling::ItemAmount[1],  
                                 $itemNumber}'  
                                PASSING xt.pmtRequestXML  
                                COLUMNS  
                                traceid       NUMBER   PATH 'TraceId',  
                                itemnumber    NUMBER   PATH 'ItemNumber',  
                                itemamount    NUMBER   PATH 'ItemAmount') xt2;
                

                 

                BTW, not declaring the default namespace simply returns the error that the elements/attributes are invalid.

                 

                -Seth.

                • 5. Re: Yet another "ORA-19279: XPTY0004 - XQuery dynamic type mismatch"
                  Jason_(A_Non)

                  Can you share a shortened version of the XML that you used in the WITH statement and from your table so I can have something to check against?

                   

                  As for putting the work together, I know a small bit of XQuery and Odie knows far more than me so I pay attention to his examples and sometimes I remember what I read even.  We have both been on these forums for some time so that helps.

                   

                  As a side note, the error sounds like an order issue.  I ran into that when putting the return elements into a different order.  I did not dig into the rules deep enough to understand fully why order mattered.  I'm wondering if your real XML is ordered differently but just a guess.

                  • 6. Re: Yet another "ORA-19279: XPTY0004 - XQuery dynamic type mismatch"
                    odie_63

                    The XQTY0024 error is normally raised in a situation like this :

                    SQL> xquery

                      2  element a { element b {}, attribute id {} }

                      3  /

                    ERROR:

                    ORA-19244: XQTY0024 - invalid attribute node in element constructor

                     

                     

                    SQL> xquery

                      2  element a {  attribute id {}, element b {} }

                      3  /

                     

                    Result Sequence

                    --------------------------------------------------------------------------------

                    <a><b></b></a>

                     

                     

                    But Oracle also raises it (wrongly) when some particular non-attribute constructors are used, which goes against the W3C specs :

                    SQL> xquery

                      2  let $d := <root><x>A</x><y>B</y></root>

                      3  return element a {

                      4    $d/x

                      5  , $d/x/following-sibling::y

                      6  }

                      7  /

                    ERROR:

                    ORA-19244: XQTY0024 - invalid attribute node in element constructor

                     

                     

                    SQL> xquery

                      2  let $d := <root><x>A</x><y>B</y></root>

                      3  return element a {

                      4    $d/x/following-sibling::y

                      5  , $d/x

                      6  }

                      7  /

                     

                    Result Sequence

                    --------------------------------------------------------------------------------

                    <a><y>B</y><x>A</x></a>

                     

                    • 7. Re: Yet another "ORA-19279: XPTY0004 - XQuery dynamic type mismatch"
                      Jason_(A_Non)

                      And your second example explains what I was seeing.  When creating the sample query, I had done the $d/x, $d/x/following-sibling::y and gotten the mentioned error.  The wrong part explains why it made no sense and why simply switching the order as you showed allowed it to work.

                      • 8. Re: Yet another "ORA-19279: XPTY0004 - XQuery dynamic type mismatch"
                        tsuji

                        That's is really disturbing and that is clearly an unacceptable solution as it means you have to make y in front of x restricting the freedom of the structure in xs:sequence. for x before y.

                         

                        A way to make sure you get what you want in the sequential structure using following-sibling etc is this : use a FLOWR expression for the purpose.

                         

                        let $d := <root><x>A</x><y>B</y></root>

                        return

                        element a {

                        let $dummy:="dummy"

                        return

                        ($d/x, $d/x/following-sibling::y)

                        }

                         

                        The $dummy is just to satisfy the mandatory of having either let or for in a FLOWR expression. It makes the construction here a bit ridiculous.

                         

                        In order to make it not so ridiculous (just apparently in this case), we can make it look more serious (again just apprently) like this for instance.

                         

                        let $d := <root><x>A</x><y>B</y></root>

                        return

                        element a {

                        let $e:=$d/x

                        return

                        ($e, $e/following-sibling::y)

                        }

                        • 9. Re: Yet another "ORA-19279: XPTY0004 - XQuery dynamic type mismatch"
                          odie_63

                          That's is really disturbing and that is clearly an unacceptable solution as it means you have to make y in front of x restricting the freedom of the structure in xs:sequence. for x before y.

                          I agree. From a general point of view, it's not solution and surely worth filing a bug.

                          However, the order doesn't matter in the present case since the XQuery expression is just there to prepare the data to be presented in relational format, making that workaround an acceptable solution.