Sitemap

Amicuk Programming Answers

Nested group function without group xmlagg

-0001-11-30   Views:6

Advertisement

I am getting nested group function without group by xmlagg when using the xmlagg function inside another xmlagg function. Find the table structure and sample data here, CREATE TABLE "TEST_TABLE"    ("KEY" NUMBER(20,0),     "NAME&q

I am getting nested group function without group by xmlagg when using the xmlagg function inside another xmlagg function. Find the table structure and sample data here,
CREATE TABLE "TEST_TABLE"
   ("KEY" NUMBER(20,0),
    "NAME" VARCHAR2(50 ),
    "DESCRIPTION" VARCHAR2(100 )
   Insert into TEST_TABLE (KEY,NAME,DESCRIPTION) values (1,'sam','desc1');
   Insert into TEST_TABLE (KEY,NAME,DESCRIPTION) values (2,'max','desc2');
   Insert into TEST_TABLE (KEY,NAME,DESCRIPTION) values (3,'peter',null);
   Insert into TEST_TABLE (KEY,NAME,DESCRIPTION) values (4,'andrew',null);
select
        XMLSerialize(document
        xmlelement("root",
         xmlagg(
           xmlelement("emp"          
           , xmlforest(Key as "ID")          
           , xmlforest(name as "ename")
           , xmlelement("Descriptions", 
           xmlagg(
              xmlforest(description as "Desc")
       ) as clob indent
       ) as t   
      from test_table;Then i removed the xmlagg function from the above select query and used xmlelement instead
  select
        XMLSerialize(document
        xmlelement("root",
         xmlagg(
           xmlelement("emp"          
           , xmlforest(Key as "ID")          
           , xmlforest(name as "ename")
           , xmlelement("Descriptions",            
              xmlforest(description as "Desc")
       ) as clob indent
       ) as t   
      from test_table;This is working fine, but xml created with empty elements for Descriptions element for key 3 and 4 which has null values. I need don't need Descriptions element in the xml when it has null value. Please help me to resolve this.

The replay answer
Advertisement
You can do it with a correlated subquery :
SQL> select xmlserialize(document
  2           xmlelement("root",
  3             xmlagg(
  4               xmlelement("emp"
  5               , xmlforest(
  6                   t.key as "ID"
  7                 , t.name as "ename"
  8                 , (
  9                     select xmlagg(
10                              xmlelement("Desc", d.description)
11                              order by d.description -- if necessary
12                            )
13                     from test_desc d
14                     where d.key = t.key
15                   ) as "Descriptions"
16                 )
17               )
18             )
19           ) as clob indent
20         )
21  from test_table t;
XMLSERIALIZE(DOCUMENTXMLELEMEN
<root>
  <emp>
    <ID>1</ID>
    <ename>sam</ename>
    <Descriptions>
      <Desc>desc1_1</Desc>
      <Desc>desc1_2</Desc>
      <Desc>desc1_3</Desc>
    </Descriptions>
  </emp>
  <emp>
    <ID>2</ID>
    <ename>max</ename>
    <Descriptions>
      <Desc>desc2_1</Desc>
      <Desc>desc2_2</Desc>
      <Desc>desc2_3</Desc>
    </Descriptions>
  </emp>
  <emp>
    <ID>3</ID>
    <ename>peter</ename>
  </emp>
  <emp>
    <ID>4</ID>
    <ename>andrew</ename>
  </emp>
</root>
Or an OUTER JOIN + GROUP-BY :
select xmlserialize(document
         xmlelement("root",
           xmlagg(
             xmlelement("emp"          
             , xmlforest(
                 t.key as "ID"
               , t.name as "ename"
               , xmlagg(
                   xmlforest(d.description as "Desc")
                   order by d.description -- if necessary
                 ) as "Descriptions"
         ) as clob indent
from test_table t
     left outer join test_desc d on d.key = t.key
group by t.key
       , t.name
;Edited by: odie_63 on 11 juil. 2012 14:54 - added 2nd option

Go to See the other 9 answers

Nested group function without group xmlagg

Category:DefaultRelease time:-0001-11-30Views:130

I am getting nested group function without group by xmlagg when using the xmlagg function inside another xmlagg function. Find the table structure and sample data here, CREATE TABLE "TEST_TABLE"    ("KEY" NUMBER(20,0),     "NAME&q[More]

Nested group function

Category:DefaultRelease time:-0001-11-30Views:130

Hello all, How can I use a nested group function in a select statement? For example: select sum(count(id), column a, column b from table_name group by??? Thanks in advance!select sum(CNT) column_a, column_b        from  (select count(id), column_a, c[More]

Error in nested group function used with column name.

Category:DefaultRelease time:-0001-11-30Views:130

Hi Team, If i used nested group function with column name its not working. Could you please any one suggest me. How to use it. Regards, Venkat. Please find Spool ........ SQL> select user_name,max(max(CNT)) from (select USER_NAME,count(*) CNT from v$[More]

Using ldap Network Management Functions how to get nested groups that are members of a parent group

Category:DefaultRelease time:-0001-11-30Views:130

Using NetLocalGetMembers() I can get the users withing a group Using NetUserGetGroups() I can get the groups a user is part of. However, I cant find a function that will list the GROUPS that are part of another group. I have a group called APP_ADMIN,[More]

Nested Group Function without Group By Problem

Category:DefaultRelease time:-0001-11-30Views:130

Hey everyone, I have 3 tables as below: TABLES ITEM (Item_no, Item_price, desc) DeliveryItem (delivery_no, item_no, quantity) Delivery (delivery_no, delivery_date) SELECT desc, MAX(SUM(quantity)) FROM DeliveryItem, Item, Delivery WHERE Item.item_no =[More]

Ideal way to report on Nested Groups and their Members - I can get the script to work if there's one group, but not if there is two or more

Category:DefaultRelease time:2015-10-11Views:130

Hi All, I like to keep track of Groups and Users added into our Domain Admins.  Yes, it should always be minimal, but if I can have a daily report just to be certain, I'll sleep better. The following works just fine if there is a single nested group.[More]

Howto create nested groups???

Category:DefaultRelease time:-0001-11-30Views:130

Hello Mac fans, I'm trying to place groups <domain>\schema admins, admins and admin under one group name poweradmins on the mac. I also want to manually map some users to additional groups. I don't have a Mac network, just a shared PowerMac and a wi[More]

XML Publisher nested grouping, sum and order by question

Category:DefaultRelease time:2015-10-11Views:130

Please help in nested group by, sum and order by question My Data look like this: <Header> <Line1>      <Part Number></Part Number>           <Component>                <Component1></Component1>                <[More]

PS CS6: It'd be helpful to have the option to collapse or expand the nested groups and layers.

Category:DefaultRelease time:2015-10-11Views:130

PS CS6: It'd be helpful to have the option to collapse or expand the nested groups and layers all at once.  Autodesk Maya has a similar feature in it's outliner that allows for you to do this.And as Jeffrey Tranberry answered there: • Open/close all[More]

Getting a list of users and permission from a folder including nested groups

Category:DefaultRelease time:2015-10-11Views:130

$project_folder = "\\servername\foldername\foldername1" Get-Acl $project_folder |      ForEach-Object {          $_.Access | Add-Member -MemberType NoteProperty -Name Path -Value $_.Path -PassThru       } |      ft -GroupBy Path -Property Identi[More]

Nested Groups in Component Model

Category:DefaultRelease time:-0001-11-30Views:130

Is it possible to have a nested group in the Component Model? For example, I have a group of metatdata. That group is called Class and consists of the following fields: - Department - Number - Title - Level There can be 1:n of these Classes. These Cl[More]

Hot
Before when I would open an image on my desktop it would automatically open that image into Photoshop.  Now it opens the image in preview.  I now have to drag the image to the PS icon to open it in PS.  How can I get back to where the image is automa [More]
Hi,  I'm trying to download a trial of premier pro and after effects.  The pop up for applications manager pops up but there is no option to download either program for trial?  Can someone point me in the right direction?  Thank youYou can download t [More]
Hi, My prsentation services has gone down, after the changed the default rpd and oped another rpd which was password lock. De-Commented the AUTHENTICATION_TYPE = BYPASS_NQS and restarted the BI Server and stopped it again, then logged into the rpd wi [More]
Hi all, Does anyone know where I can get a replacement AC adapter for a 802.11n Base Station?Yes and they don't sell it separately.Read other 3 answers [More]
Hi there, I want to save data to my Sony Ericsson K750i. I know the phone implements FileConnection version 1.0. The list of roots taken from FileSystemRegistry.listRoots();returns single element named "c:/". I want to save a file.txt just about [More]
Hi does anyone know if there is firmware update for the Blackberry 8830 world edition with the carrier being Bell Canada?  I am running version 4.2 and I know there is 4.5 but I am not sure if this applies the my phone with Bell Canada. When I boot u [More]
Hi, I have installed UPK 12.1 with Oracle database in Server-client architecture. But when I tried to access the, http://hostname/ODSConent, getting below error.   HTTP Error 403.14 - Forbidden  Please help me to solvehttp://hostname/ODSConent is sup [More]
Hi everybody When I build a Gantt chart in apex.oracle.com, start & end dates show no real dates, just the text 'dd.mm.rr'. My query follows: select null as link, description as task_name, task_id as task_id, null as parent_id, start_date as actual_s [More]
I have a custom Css applied to the whole sharePoint sites, but what I want is that the sharePoint Icons was highlighted should come in white. I have not used themes in SharePoint and have applied custom CSS using custom master page. I have explored o [More]
All the songs I bought in beatport Not working in my music app on my iphone, what can i do?I'm having the same problem with MP3 downloaded from beatport. The songs are added and listed  normally on the iphone; when I try playing them, the player (and [More]