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
How much is Apple losong out to what appears to be a fairly common but complicated problem. Not sure if it all started the day I upgraded to i-tunes 9 or when I renewed my Norton Anti Virus. It was working for months even when I had Norton which i ha [More]
ITUNES 12-4points 1) Please restore the easy function whereby the side bar easily accommodates playlists 2) How do i know when devices can be ejected following synch--no visible drop down ? 3) How can i change the fonts and font sizes of the library- [More]
My PC crashed last month and I have just installed Windows7 64bit - then installed updated drives for my Epson 7880 - I now find I cannot print anything longer than 1.2m. These are indesign files that I have printed in the past with no problems. I de [More]
Every time I try to play a song downloaded from itunes it asks me to sign in to itunes. After signing in it says.. There was an error storing your authorization information on this computer. The required directory was not found or has a permissions e [More]
I am hosting a website on my server and have protected a folder using the Realms feature. I am successfully able to add users and log in, but have not found a way to log out. It seems that the only way for the user to log out is to quit his/her brows [More]
How can I assign applications to open on specific desktops? i used spaces but seems it's gone I still have the applications i used to have assigned to different spaces now assigned to desktops but how can i cinfigure it now? how come when i change th [More]
Hi all, We got a shiny new box today from Adobe with our new copy of CS5. We have a brand new computer - oodles of processing 3GB of RAM running XP SP3. When we try to install we validate, choose the programs to install, hit next and it sits for a wh [More]
My Photo Album cannot be accessed through the album as it is hidden up behind the title.....I can slide it down to see it, but it won't let me view it. I have to go through my camera and view them that way. Can anyone help????I had the exact same pro [More]
I recently updated my iPhone 4 to iOS 6 and I was pretty happy about that. But then my wi-fi started acting up. I couldnt connect to my Aunt's internet that she had just gotten installed that very same day. So I reset my network settings and everythi [More]
hi, today i put my ipod shuffle back on the charging dock as usual but 3 seconds after i put it on my computers task bar popped up a bubble saying power surge on hub port - a usb device has exceeded the power limits of its hub port. for assistance in [More]