4 Replies Latest reply: Aug 16, 2013 4:20 AM by HenkB RSS

    xquery question

    HenkB


      Hi,

       

      I am just learning x(ml)query and I have the following question

       

      say I have table orders with the columns customer_id and order_id and I want, as an example to outpur in xml only one row per customer_id :

      the customer_id and the number of orders (count). I can do it for each row like

       

      select *

        from xmltable('

                for $ord in ora:view("OE", "orders")/ROW

                order by $ord/CUSTOMER_ID

                  return <Orders cusid="{$ord/CUSTOMER_ID}"  ordid="{$ord/ORDER_ID}">

                  <count>

                    {fn:count(for $j in ora:view("OE", "orders")/ROW where $j/CUSTOMER_ID eq $ord/CUSTOMER_ID return 1)}

                  </count>                

                  </Orders>');

       

       

      but how to do it per customer_id?

       

      Also how to group within xquery. I have seem some xquery examples for grouping but it does not work in oracle... (maybe because I am a newbier ;-) )

       

      Is there some Oracle documentation specific to supported xquery syntax and examples that are more advanced?

      Is there an Oracle tool for debugging xquery?Maybe (an addin) for sqldeveloper?

       

      Thanks ,

       

      Henk

        • 1. Re: xquery question
          odie_63

          say I have table orders with the columns customer_id and order_id and I want, as an example to outpur in xml only one row per customer_id :

          the customer_id and the number of orders (count).

          So basically, you're asking about XQuery grouping?

          If the data source is a (or more) relational table(s) then I wouldn't use XQuery at all but just SQL/XML functions XMLElement/XMLAgg with a regular SQL GROUP BY clause :

          SELECT XMLElement("Customers"

                 , XMLAgg(

                     XMLElement("Customer"

                     , XMLAttributes(

                         customer_id as "cusId"

                       , count(*) as "orderCnt"

                       )

                     )

                   )

                 )

          FROM orders

          GROUP BY customer_id ;

           

          XQuery 3.0 defines a syntax for grouping but Oracle doesn't support it yet.

          In order to group within an XQuery 1.0  expression, you have to do it "manually" by first selecting distinct values.

          Here's an example using SCOTT schema :

          SQL> SELECT XMLSerialize(document

            2           XMLQuery(

            3             'element Departments

            4              {

            5                for $deptId in fn:distinct-values( fn:collection("oradb:/SCOTT/EMP")/ROW/DEPTNO )

            6                return element Department

            7                {

            8                  attribute deptId { $deptId }

            9                , attribute empCnt { fn:count( fn:collection("oradb:/SCOTT/EMP")/ROW[DEPTNO=$deptId] ) }

          10                }

          11              }'

          12             returning content

          13           )

          14           indent

          15         ) as result

          16  FROM dual ;

           

          RESULT

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

          <Departments>

            <Department deptId="10" empCnt="3"/>

            <Department deptId="20" empCnt="3"/>

            <Department deptId="30" empCnt="6"/>

          </Departments>

           

           

          Is there some Oracle documentation specific to supported xquery syntax and examples that are more advanced?

          See : Using XQuery with Oracle XML DB

          And : Best Practices for XQuery processing in Oracle Database 12c  (intended for 12c but most of it applies to 11.2)

           

          Is there an Oracle tool for debugging xquery?Maybe (an addin) for sqldeveloper?

          I'm not aware of any Oracle-supplied tool but IDEs such XMLSpy or Oxygen have this feature.

          • 2. Re: xquery question
            HenkB

            Hi Odie,

             

            Thanks,

             

            It works for oracle11r2 but not for oracle10r203 (missing right parenthesis error)

             

            My code should at least work for Oracle10Gr2 because not all our clients are at 11gr2....

             

            I see strange things:

            Just changing  your example code:

             

            in 10.2.0.3

            select xmlquery

                   ('xquery version "1.0"; (: : )         

                     for $cust_id in fn:distinct-values( fn:collection("oradb:/OE/ORDERS")/ROW/CUSTOMER_ID)

                       where ( $cust_id = 101 or $cust_id = 102 )

                  return element Customer

                   {

                     attribute custId { $cust_id }

                   }

                      '

                     returning content) as output

                from dual

             

            with output is null

             

            In 11gr2 (exactly the same query)

            output ->    <Customer custId="101"></Customer><Customer custId="102"></Customer>

             

            Frustrating ...

             

            Henk

            • 3. Re: xquery question
              odie_63

              My code should at least work for Oracle10Gr2 because not all our clients are at 11gr2....

              Yes, forgot that from your previous posts.

              Use ora:view() function then (instead of fn:collection), but be aware it's deprecated in 11.2.

               

              You can also lose the XQuery prolog declaration.

              • 4. Re: xquery question
                HenkB

                Unfortunately still error in oracle10gr2

                select xmlquery

                       ('xquery version "1.0";      

                         for $cust_id in fn:distinct-values(ora:view("OE", "ORDERS")/ROW/CUSTOMER_ID)

                           where ( $cust_id = 101 or $cust_id = 102 )

                      return element Customer

                       {

                         attribute custId { $cust_id }

                       }

                          '

                         returning content) as output

                    from dual

                 

                ORA-19112: error...: oracle.xquery.XQException: Conversion to String failed

                19112. 00000 -  "error raised during evaluation: %s"

                 

                Query run without error in 11r2....

                 

                Any solution that the query also works for Oracle10Gr2? Message was edited by: HenkB