Solved

Oracle XML Hierarchical output.

Posted on 2013-11-21
24
735 Views
Last Modified: 2013-11-26
Hi,

I need to create a hieararchical XML from oracle table data, For Example: EMP. In my actual scenario, the entire data is coming from a view. In the EMP case, it is coming from Employee table.

Please find attached the sample xml output file. I masked my original data and may not look like a typical XML file. Basically, I need to group all the employees under one department and subgroup them under one MGR. I was able to generate the XML without the grouping. For example if there are 3 employees under deptno 10 and MGR 7839. Deptno 10 and MGR 7839 are also repeated for each employee.

Please help me with the query to generate the xml.

Thanks in advance.

Sudhkaar Naraparaju.
QualityData-Raj.xml
0
Comment
Question by:nssudha
  • 14
  • 6
  • 4
24 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39666861
Any reason you don't add deptno and empno as attributes?

Also not getting the need for the H node.

You really don't have a parent child relationship in the XML you posted.  Is the attached file what you have or what you are expecting as a final result?
0
 

Author Comment

by:nssudha
ID: 39666905
As I said, it is all coming from a view. The data is coming from 3-4 multiple tables. I combined them all into one view using UNION ALL. I am expecting a similar file as the final result.

Basically, I need to keep a main group as deptno and under that I need to have a subgroup of MGR and under that I need to have another subgroup of empsalary which is what the H node is.

Thanks,
Sudhakar.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39666923
>>subgroup of MGR and under that I need to have another subgroup of empsalary

But that isn't what you show in the sample you posted.  MGR and E nodes are peers.  They are both children of EMPINFO.

So you want E nodes to be children of MGR?
0
 

Author Comment

by:nssudha
ID: 39666946
Yes. I want E nodes to be the children of MGR. I might have made a mistake while masking the XML.

Thanks,
Sudhakar.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39666999
I might have made a mistake while masking the XML.

if so, can you post a corrected XML?
0
 

Author Comment

by:nssudha
ID: 39667035
Hi sdstuber,

It's baskically a hiearchical XML with Dept as the main node, MGR as the sub node and E as it's sub node.

Thanks,
Sudhakar.
0
 

Author Comment

by:nssudha
ID: 39667308
I am attaching what might be a better XML than the first one (I hope). Please let me know if there any questions.

Thanks,
Sudhakar.
QualityData2.xml
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39667597
Since your expected results are loosely based on your actual query, I'm not going to re-write the example from the link below to tweak it for what isn't exactly what you need.

It is a good example also based off the EMP table and shows the basics of a hierarchical XML generation.

https://forums.oracle.com/message/1447865

Snippet from that link to pay attention to:
select xmlelement("employees",
  (select dbms_xmlgen.getxmltype(dbms_xmlgen.newcontextFromHierarchy('
    select level
           , xmlelement("emp"
           , xmlelement("number", empno)
           , xmlelement("name", ename)
           , xmlelement("salary", sal)
           , xmlelement("hiredate", hiredate)
           , xmlelement("level", level)
           )
    from emp
    where deptno = 20
    start with mgr is null connect by prior empno=mgr
    order siblings by hiredate
  '))
  from dual)
  ) xmldoc
from dual

Open in new window

0
 

Author Comment

by:nssudha
ID: 39668012
I saw this example in OTN before I posted here. My situation is a little different. I do not have columns similar to EMPNO and MGR in my tables or view to satisfy the condition connect by prior  EMPNO=MGR.

My data is medical data.
Hospital Code replaces DEPTNO.
Medical Code replaces MGR
Medical Details replaces the info under node "E"
Medical Details include "Medicine Name", "Amount","Date" etc.

Please let me know how to tackle this.

Thanks,
Sudhakar.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39668051
We can only post based on what you provide.  You asked for a hierarchical XML example based on the EMP table based on a MGR EMP relationship.

Please post a more accurate example and expected results.

Dummy up some sample tables and data and based on your actual requirements and expected results.  Then we can build a test case based of that with working code.

The closer you make your example test case, the closer our code will be to a copy/paste solution.
0
 

Author Comment

by:nssudha
ID: 39668062
Sure. I will work on it and post it soon.

Thanks,
Sudhakar.
0
 

Author Comment

by:nssudha
ID: 39669138
Please find attached the data (I put the datatypes right below the column names) and the XML for my data. It is as close to the data I have in my DB with the actual data masked. I put the dates and most of the other details the same except for the top nodes.

Hope these are better samples.

Thanks,
Sudhakar.
MyData.xml
MedicalData.xls
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:nssudha
ID: 39674955
I kind of got an output by following the query in the link below. I am not able to group the results to get the count for the number of records in the output.

Please let me know if you have a way to get the count also.

Thanks,
Sudhakar.

<link removed - GaryC123>
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39674963
I'll try to get some time later today to work on this.
0
 

Author Comment

by:nssudha
ID: 39675023
Thank you slightwv.

Thanks,
(Raj) Sudhakar.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39675330
>>Please let me know if you have a way to get the count also.

I showed you this in your previous question:
http://www.experts-exchange.com/Database/Oracle/Q_28269030.html#a39580824

In looking at your posted sample data, I'm not seeing a hierarchical layout in the data.  Where is the parent/child relationship?

Based on the data and expected results you posted, any SQL I came up with would be real similar to the same SQL I posted in the link above from your previous question.

Also, in the latest expected results you show a count of 5.  What is that 5 a count of in the sample data?
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39675410
I think these do what you're looking for...


I made some assumptions for your header information...

One - DataSource : EMPR-Data  is constant
Two - DateCreated: is supposed to be the current time the xml was generated
Three - The EMPCount was a mistake was supposed to be a count of the number of hospital (MEDINFO) nodes generated

If any of these assumptions are not correct, please explain


SELECT XMLELEMENT(
           "MedDetails",
           XMLELEMENT(
               "HeaderInformation",
               XMLFOREST(
                   'EMPR-Data' AS "DataSource",
                   TO_CHAR(SYSDATE, 'mm/dd/yyyy hh:mi:ss AM') AS "DateCreated",
                   COUNT(*) AS "EMPCount"
               )
           ),
           XMLAGG(innerxml)
       )
  FROM (  SELECT XMLELEMENT(
                     "MEDINFO",
                     XMLELEMENT("HOSPITAL_CODE", hospital_code),
                     XMLAGG(
                         XMLELEMENT(
                             "M",
                             xmlattributes(medical_code AS "MEDICAL_CODE"),
                             XMLELEMENT(
                                 "E",
                                 xmlattributes(medical_desc AS "Medical_Desc"),
                                 XMLFOREST(
                                     amount AS "Amount",
                                     stdt AS "StDt",
                                     enddt AS "EnDt",
                                     details AS "Details"
                                 )
                             )
                         )
                     )
                 )
                     innerxml
            FROM meddetails m
        GROUP BY hospital_code);

Open in new window


alternate version, slightly more compact

  SELECT XMLELEMENT(
             "MedDetails",
             XMLELEMENT(
                 "HeaderInformation",
                 XMLFOREST(
                     'EMPR-Data' AS "DataSource",
                     TO_CHAR(SYSDATE, 'mm/dd/yyyy hh:mi:ss AM') AS "DateCreated",
                     COUNT(*) AS "EMPCount"
                 )
             ),
             XMLAGG(
                 XMLELEMENT(
                     "MEDINFO",
                     XMLELEMENT("HOSPITAL_CODE", hospital_code),
                     XMLAGG(
                         XMLELEMENT(
                             "M",
                             xmlattributes(medical_code AS "MEDICAL_CODE"),
                             XMLELEMENT(
                                 "E",
                                 xmlattributes(medical_desc AS "Medical_Desc"),
                                 XMLFOREST(
                                     amount AS "Amount",
                                     stdt AS "StDt",
                                     enddt AS "EnDt",
                                     details AS "Details"
                                 )
                             )
                         )
                     )
                 )
             )
         )
    FROM meddetails m
GROUP BY hospital_code

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39675454
I didn't include formatting in my queries,  you can use xmlserialize as shown in link above to pretty-print the xml into  clob text

 xmlserialize(document (...) indent)

where "(...)" is the xml calls above
0
 

Author Closing Comment

by:nssudha
ID: 39675763
It worked. Your assumptions were correct. However, when I am trying to spool to an xml file, it was giving me different CLOBs for each Hospital code. Is there a way to spool to xml as one document for all the hospital codes?

Thanks,
(Raj) Sudhakar.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39675790
Using either of my queries above
I get a single clob result that has all of them in it

<MedDetails>
  <HeaderInformation>
    <DataSource>EMPR-Data</DataSource>
    <DateCreated>11/25/2013 01:03:46 PM</DateCreated>
    <EMPCount>3</EMPCount>
  </HeaderInformation>
  <MEDINFO>
    <HOSPITAL_CODE>1001</HOSPITAL_CODE>
    <M MEDICAL_CODE="Inf323">
      <E Medical_Desc="NA">
        <Amount>5</Amount>
        <StDt>8/12/2013</StDt>
        <EnDt>10/31/2013</EnDt>
        <Details>NA</Details>
      </E>
    </M>
    <M MEDICAL_CODE="Inf326">
      <E Medical_Desc="NA">
        <Amount>5</Amount>
        <StDt>8/12/2013</StDt>
        <EnDt>10/31/2013</EnDt>
        <Details>NA</Details>
      </E>
    </M>
    <M MEDICAL_CODE="Inf325">
      <E Medical_Desc="NA">
        <Amount>5</Amount>
        <StDt>8/12/2013</StDt>
        <EnDt>10/31/2013</EnDt>
        <Details>NA</Details>
      </E>
    </M>
    <M MEDICAL_CODE="Inf324">
      <E Medical_Desc="NA">
        <Amount>5</Amount>
        <StDt>8/12/2013</StDt>
        <EnDt>10/31/2013</EnDt>
        <Details>NA</Details>
      </E>
    </M>
  </MEDINFO>
  <MEDINFO>
    <HOSPITAL_CODE>1002</HOSPITAL_CODE>
    <M MEDICAL_CODE="SCN102">
      <E Medical_Desc="NA">
        <Amount>5</Amount>
        <StDt>8/12/2013</StDt>
        <EnDt>10/31/2013</EnDt>
        <Details>NA</Details>
      </E>
    </M>
    <M MEDICAL_CODE="SCN105">
      <E Medical_Desc="NA">
        <Amount>5</Amount>
        <StDt>8/12/2013</StDt>
        <EnDt>10/31/2013</EnDt>
        <Details>NA</Details>
      </E>
    </M>
    <M MEDICAL_CODE="SCN104">
      <E Medical_Desc="NA">
        <Amount>5</Amount>
        <StDt>8/12/2013</StDt>
        <EnDt>10/31/2013</EnDt>
        <Details>NA</Details>
      </E>
    </M>
    <M MEDICAL_CODE="SCN103">
      <E Medical_Desc="NA">
        <Amount>5</Amount>
        <StDt>8/12/2013</StDt>
        <EnDt>10/31/2013</EnDt>
        <Details>NA</Details>
      </E>
    </M>
  </MEDINFO>
  <MEDINFO>
    <HOSPITAL_CODE>1003</HOSPITAL_CODE>
    <M MEDICAL_CODE="VLN101">
      <E Medical_Desc="NA">
        <Amount>5</Amount>
        <StDt>8/12/2013</StDt>
        <EnDt>10/31/2013</EnDt>
        <Details>NA</Details>
      </E>
    </M>
    <M MEDICAL_CODE="VLN104">
      <E Medical_Desc="NA">
        <Amount>5</Amount>
        <StDt>8/12/2013</StDt>
        <EnDt>10/31/2013</EnDt>
        <Details>NA</Details>
      </E>
    </M>
    <M MEDICAL_CODE="VLN103">
      <E Medical_Desc="NA">
        <Amount>5</Amount>
        <StDt>8/12/2013</StDt>
        <EnDt>10/31/2013</EnDt>
        <Details>NA</Details>
      </E>
    </M>
    <M MEDICAL_CODE="VLN102">
      <E Medical_Desc="NA">
        <Amount>5</Amount>
        <StDt>8/12/2013</StDt>
        <EnDt>10/31/2013</EnDt>
        <Details>NA</Details>
      </E>
    </M>
  </MEDINFO>
</MedDetails>

Open in new window

0
 

Author Comment

by:nssudha
ID: 39675852
It is also putting the header information for each hospital code. I need one header information for the entire XML. I used "with mydata as (select hospital_code,medical_code,amount,stdt,enddt....,count(*) over() mycount
from table_name
)
select xmlserialize(document
                xmlelement("MedDetails",
                   xmlelement("HeaderInformation",
                       xmlforest('EMPR-Data' as "DataSource",
                       TO_CHAR(SYSDATE, 'mm/dd/yyyy hh:mi:ss AM') AS "DateCreated",
                         mycount AS "EMPCount"
                      )
                       ),
                      xmlelement("MEDINFO",
                       xmlelement("HOSPITAL_CODE",hospital_code),
                         xmlagg(
                             xmlelement("M",
                                xmlelement("E",
                                  xmlforest(amount AS "Amount",
                                     stdt AS "StDt",
                                     enddt AS "EnDt",
                                     details AS "Details"
                                                   )
                                                  )
                                                  )
                                                  )
                                                )
                                                 )
                           version '1.0' indent)
                             from mydata
                           group by mycount,hospital_code
/

Please let me know how to spool to a single xml document.

Thanks,
Sudhakar.
0
 

Author Comment

by:nssudha
ID: 39676295
Never mind. I got it worked out. If I need more help, I will post again.

Thanks,
(Raj) Sudhakar.
0
 

Author Comment

by:nssudha
ID: 39677564
Hi,

I still have one last issue with the count. I am using the above query provided to spool a file with a query similar to the one provided earlier here. I am copying the solution below. When I am running it, I am getting ORA-00937: not a single-group group function near mycount as "OrdersCount".

Could you please let me know where the issue is?

Thanks,
Sudhakar.


drop table myorders purge;
create table myorders (
RPT_ORD_ID            CHAR(32),
OrderID                  CHAR(32),
FTNT_ORD_ID            NUMBER,
OrderDesc            VARCHAR2(500),
IS_ACTV                  CHAR(1)
);

insert into myorders(orderid,orderdesc) values('1', 'Oracle Developers Guide.');
insert into myorders(orderid,orderdesc) values('2', 'Oracle DBA Guide.');
insert into myorders(orderid,orderdesc) values('3', 'Oracle PLSQL Guide.');
insert into myorders(orderid,orderdesc) values('4', 'Java Developers Guide.');
insert into myorders(orderid,orderdesc) values('5', 'Java for Dummies.');
commit;

with mydata as (
select orderid, orderdesc, count(*) over() mycount from myorders
)
select xmlserialize(document
      xmlelement("FootNoteDetails",
            xmlelement("HeaderInformation",
                  xmlforest(
                        'ORDR-Footnote' as "DataSource",
                        sysdate as "DateCreated",
                        sysdate as "DateLastUpdated",
                        mycount as "OrdersCount"
                  )
            ),
            xmlagg(
                  xmlelement("FootNote",
                        xmlforest(
                              trim(orderid) as "OrderID",
                              orderdesc as "OrderDesc"
                        )
                  )
            )
      )
version '1.0' indent)
from mydata
group by mycount
/
0
 

Author Comment

by:nssudha
ID: 39677956
I will post my actual query in a separate post.

Thanks,
Sudhakar.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now