4 Replies Latest reply: Jul 14, 2011 12:13 PM by MichaelR64 RSS

    Using XMLAgg with group by in OWB 11gr2

    MichaelR64
      Hi,

      I want to use xml sql operators to construct xml from relational data.
      I am using 11gr2 (11.2.0.1)
      I have followed this: http://blogs.oracle.com/warehousebuilder/entry/leveraging_xdb but the mapping dealing with this is not included in the downloadable zip file.

      My problem is as follows:
      I do not know how to use the group by part of the XMLAgg function in OWB's expression operator.

      Let me explain:
      You have Tables DEPARTMENT and PERSON (oracle sample data).
      The xml should be :
      <Department name="SALES">
      <Employee>PU_CLERK Baida</Employee>
      <Employee>PU_CLERK Colmenares</Employee>
      </Department>

      In owb you can join the two tables and even use XMLAgg to aggregrate the employee fields.
      Something like:
      XMLAgg(
      XMLElement( "EMPLOYEE", employee.lastname)
      )

      And putting that into a xmlnode becomes:

      XMLElement("Department",
      XMLAgg(
      XMLElement( "EMPLOYEE", employee.lastname)
      )
      )

      However if i want to use the departname of the peson, which is is just a field in the joinded recordset, and put that into a attribute of the department node Oracle complains.
      XMLElement("Department",
      XMLAttributes(department.name as "name"),
      XMLAgg(
      XMLElement( "EMPLOYEE", employee.lastname)
      )
      )
      This is because Oracle aggregrates all of the records used for sourcing the XML constructs, not just the person stuff.

      Normally you would include a group by part to the Select statement to allow the use of the department name in the attribute such as:

      Select
      XMLElement("Department",
      XMLAttributes(department.name as "name"),
      XMLAgg(
      XMLElement( "EMPLOYEE", employee.lastname)
      )
      ) from department join employee on (department.xx = employee.xx)
      group by department.name

      Here lies the catch ! Using the group by in an expression operator results in faulty sql since OWB generates wrong sql when using generate intermediate and it appears at the wrong place !
      Instead of appearing after the FROM xxx it appears between the closing bracket of the xmlelement and the FROM.
      (Ofcourse this is due to owb just pasting the entire expression operators content in between the parts)

      Does anybody know how to fix this ?
      I need some kind of workaround without resorting to pl/sql etc.
      As i said the posting on the blog is not clear enough for me so if anyone can help, please do :-)
      This problem shouldn't be first one to surface since it seems fairly trivial, its just that i am stumped and the owb guide is incomplete at best on this.
      I am sorry for the loss of formatting but the post-editor on this forum is really ...

      Edited by: MichaelR64 on 30-jun-2011 14:21
        • 1. Re: Using XMLAgg with group by in OWB 11gr2
          David Allan-Oracle
          Hi Michael

          The entire XMLAgg would need to be in an expression and contain the entire query, you'd need to deploy as set based only.

          Cheers
          David

          Ps. If that doesn't make sense to you I will post tmw, traveling just now.
          • 2. Re: Using XMLAgg with group by in OWB 11gr2
            MichaelR64
            Hi David,

            I know that and have used it already.
            However when using the group by construction together with XMLAgg in the same expression OWB generates the wrong sql when doing a generate intermediate.
            The group by bit should end up after the join but is just included in the select .


            So instead of this:

            Select
            XMLElement("Department",
            XMLAttributes(department.name as "name"),
            XMLAgg(
            XMLElement( "EMPLOYEE", employee.lastname)
            )
            ) from department join employee on (department.xx = employee.xx)
            group by department.name

            (which works in sql developer) it generates this:

            Select
            XMLElement("Department",
            XMLAttributes(department.name as "name"),
            XMLAgg(
            XMLElement( "EMPLOYEE", employee.lastname)
            )
            ) group by department.name
            from department join employee on (department.xx = employee.xx)

            This is ofcourse because the only place where i can enter this group by bit is the expression dialog.

            PS: yes i have done the set based only and even made a tablefunction to handle the xmltype generated from the above to export to a file.
            All of that works, it is just that when i want to use the group by option of xmlagg owb goes south....
            • 3. Re: Using XMLAgg with group by in OWB 11gr2
              David Allan-Oracle
              Hi Michael

              You should use the aggregator for doing group by like the example below, you can define other expressions to project the XML as I have done below ...
              http://blogs.oracle.com/warehousebuilder/resource/OWB/owb_xdb_example20110701.jpg

              Is this what you are after?
              Cheers
              David
              • 4. Re: Using XMLAgg with group by in OWB 11gr2
                MichaelR64
                Hi David,

                Yes this is correct.
                I ran into conceptual problems, aka not smart enough to do it in owb :-), when trying to nest it another level so i resorted to using an inline view.
                Although it obscures your used sources from owb's lineage(which is fixable by hand) it is easier to do with very large xmltrees.