Solved

Oracle And XML

Posted on 2007-11-19
23
321 Views
Last Modified: 2013-12-19
Can someone please help me translate this sql query into xml output?????


I have 4 tables:-
provider,member,treatment and services
one provider could have many members and one member could have many services.

Provider table:-
prd_id,prd_name

Member table:-member_id,
member_name

Treatment:
treatment_id,
treatment_member_uid,
treatment_service_id
treatment_prd_id,
treatment_date


Services:-
Service_id,
service_code,
Service_Begin_date,
service_end_date,
Service_Desc,
service_prd_id_auth

select

sysdate,
prd_id,
prd_name,
member_id,
member_name,
treatment_date,
Service_id,
Service_Begin_date,
service_end_date,
Service_Desc

from

treatment,
member,
provider,
services

where

prd_id = service_prd_id_auth
and
Service_id = treatment_service_id
and
treatment_member_uid = member_id

group by
prd_id,member_id,service_id


o/p should look like this in xml:-

-<Provider>
    <ProviderId>12345</ProviderId>
    <ProviderName>dafsd</ProviderName>
  -<Members>
   -<Member>
    <MemberId>56778</MemberId>
    <Member_Name>trtg</Member_Name>
    <treatment_date>12/1/2006</treatment_date>
   
    -<Services>

      -<Service>
         <Service_id>78</Service_id>
         <Service_desc>jhgjhkd</service_desc>
         <Service_Begin_date>01/02/2007</Service_Begin_date>
         <service_end_date>01/05/2007</service_end_date>
       </Service>

       -<Service>
         <Service_id>79</Service_id>
         <Service_desc>jhnjhkd</service_desc>
         <Service_Begin_date>01/02/2007</Service_Begin_date>
         <service_end_date>01/05/2007</service_end_date>
       </Service>
     
-</Services>

</Member>


<Member>

-<Member>
    <MemberId>56779</MemberId>
    <Member_Name>trtkgkg</Member_Name>
    <treatment_date>12/3/2006</treatment_date>
   
          -<Services>

      -<Service>
         <Service_id>78</Service_id>
         <Service_desc>jhgjhkd</service_desc>
         <Service_Begin_date>01/02/2007</Service_Begin_date>
         <service_end_date>01/05/2007</service_end_date>
       </Service>

       -<Service>
         <Service_id>79</Service_id>
         <Service_desc>jhnjhkd</service_desc>
         <Service_Begin_date>01/02/2007</Service_Begin_date>
         <service_end_date>01/05/2007</service_end_date>
       </Service>
     
-</Services>
     
</Member>

</Members>

<systemdate>10/12/2007</systemdate>

</Provider>





0
Comment
Question by:chisun7679
  • 11
  • 8
23 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 20315006
Can you provide create table scripts and sample data to work with?
0
 

Author Comment

by:chisun7679
ID: 20315335
Does this help?

Provider table:-
prd_id,prd_name

create table provider(
prd_id number(10),
prd_name varchar2(25));

insert into provider
values(&prd_id,&prd_name);


Member table:-member_id,
member_name

create table Member(
member_id number(10),
member_name varchar2(25));

insert into provider
values(&member_id,&member_name);

Treatment:
treatment_id,
treatment_member_uid,
treatment_service_id,
treatment_prd_id,
treatment_date

create table Treatment(
treatment_id number(10),
treatment_member_uid number(10),
treatment_service_id number(10),
treatment_prd_id number(10),
treatment_date date);

insert into Treatment
values(&treatment_id,&treatment_member_uid,&treatment_service_id,
&treatment_prd_id,
&treatment_date);




Services:-
Service_id,
service_code,
Service_Begin_date,
service_end_date,
Service_Desc,
service_prd_id_auth


create table Services(
Service_id number(10),
service_code vachar2(4),
Service_Begin_date date,
service_end_date date,
Service_Desc vachar2(24)
service_prd_id_auth number(10));

insert into Services
values(&Service_id,&service_code,&Service_Begin_date,
&service_end_date,
&Service_Desc,
&service_prd_id_auth);
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20315395
yes, but for the inserts I meant some sample data that I could use to work with.  I can extrapolate some of it from the output you have above; but it's a lot of work just to try to create a test scenario before I can even start trying to actually solve the problem.

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.

 
LVL 5

Expert Comment

by:sora
ID: 20317417
see http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb13gen.htm
there are a lot of XML functions you can use in SQL if XML DB option is installed with 9.2x and above. Note your sql*plus session from where you connect should be 9.x and above. 8.x and below SQL*PLUS does not recognise xml
0
 

Author Comment

by:chisun7679
ID: 20462947
hr schema->employees table->
i.am trying to print out department_id->job_id->employees for every department.

the below sql/xml i have wriiten does not give me the desired output:-
SELECT
   XMLELEMENT("DEPARTMENT_ID",xmlforest(ED.DEPARTMENT_ID as "department_id"),
             (
              SELECT xmlagg(XMLELEMENT("job",xmlforest(j.job_id as "job_id"),
                                            (
                                            SELECT XMLAGG(XMLELEMENT("Employee",xmlforest( e.last_name as "last_name")))
                                            FROM employees e
                                            WHERE E.job_id = j.job_id    
                                           
                                            )
                                      ))
                           
             FROM employees j
             WHERE j.department_id = ed.department_id
                       
             
              ))
   AS "Dept_list"    
   FROM EMPLOYEES ED
   GROUP BY ED.DEPARTMENT_ID;
   
 


     
It repeats the job_id for the number of employees in every job...
Please help..
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20467062
SELECT XMLELEMENT("Department_List",
                  XMLELEMENT("Department",
                             XMLAGG(XMLELEMENT("Department",
                                               XMLATTRIBUTES(department_id "ID"),joblist
                                              )
                                   )
                            )
                 ) xml
  FROM (SELECT department_id, XMLAGG(XMLELEMENT("Job",
                                                  XMLATTRIBUTES(job_id "ID"), emplist
                                                 )
                                      ) joblist
          FROM (SELECT department_id, job_id, XMLELEMENT("Employees",
                                                         XMLAGG(XMLELEMENT("Employee",
                                                                           last_name
                                                                          )
                                                               )
                                                        ) emplist
                  FROM hr.employees
              GROUP BY department_id,job_id)        
      GROUP BY department_id
       )
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20467886
or, if you don't want the employees wrapped in a sub-element...

select xmlelement("Department_List", xmlelement("Department",xmlagg(xmlelement("Department",xmlattributes(department_id "ID"),joblist))) )
        from (
        select department_id, xmlagg(xmlelement("Job", xmlattributes(job_id "ID"), emplist)) joblist from
        (SELECT   department_id, job_id,
                                      XMLAGG(XMLELEMENT("Employee", last_name
                                                       )
                                            )
                                     emplist
                      FROM hr.employees
                  GROUP BY department_id,job_id)        
        group by department_id);
0
 

Author Comment

by:chisun7679
ID: 20492197
if i have more than one attribute to print in the department,job and employees elment can i use xmlforest function instead of the xmlattributes??
when i use xmlforest in  the department and job element it gives me an error but works fine in employee element...please help..
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20492241
Can you show me what you're trying to do?
0
 

Author Comment

by:chisun7679
ID: 20494204
when i run the above query it gives me a part of the output and then there are  garbage values like this
"@ and lots of squares...
is there any function for encoding the xml output into readble format or something to get a proper output in readable format.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20494229
xml is just text,  if it's not showing up correctly perhaps you have a character set mismatch between your db and your client software.

I ran both of the queries I sent you and they produced human readable text
0
 

Author Comment

by:chisun7679
ID: 20494512
is there any xml function that can take care of the mis-match???
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20494576
no

are you getting the funny characters with the hr schema data?
0
 

Author Comment

by:chisun7679
ID: 20495000
the output from the hr schema query is one row for all the departments.is it possible to get i row for each department element?
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 20495103
select xmlelement("Department",xmlattributes(department_id "ID"),joblist)
        from (
        select department_id, xmlagg(xmlelement("Job", xmlattributes(job_id "ID"), emplist)) joblist from
        (SELECT   department_id, job_id,
                                      XMLAGG(XMLELEMENT("Employee", last_name
                                                       )
                                            )
                                     emplist
                      FROM hr.employees
                  GROUP BY department_id,job_id)        
        group by department_id);
0
 

Author Comment

by:chisun7679
ID: 20500585
can i use the xmforest instead of the xmlattributes function to include a bunch of fields for a particular element?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20500613
should be able to, you can also add more than one attribute using xmlattributes.
0
 

Author Comment

by:chisun7679
ID: 20514566
I have to create a table using the create table statement and then insert values into the table using the insert statement.
The third query would be the sql/xml query against the table i have created.

I need to write a perl script which will connect to the database and execute these 3 queries and generate an xml output file

from the sql xml query.

My code is as follows:-

my $outputFile = shift(@ARGV);
my $env = shift(@ARGV);

if ($env eq "PROD")
{
  $schema_name = "PROD";
  $SID  = "PRODUCTION";
  $USERNAME = "Puser";
  $PASSWORD = "****";
}


if ($env eq "TEST")
{
  $SID = "TEST";
  $schema_name = "QA";
  $USERNAME = "QAUSER";
  $PASSWORD = "****";
}

my $filename = $outputFile;

my $sqlcode1 = qq{create table query};

my $sqlcode2 = qq{insert table query};

my $sqlcode3 = qq{sql/xml table query};



open (FILE, ">" . $filename) or die "Cannot create output file: $!\n:";

my $dbh = DBI->connect($SID,$USERNAME,$PASSWORD,"Oracle")
                or die "Couldn't connect to database: ";

my $sth1 = $dbh->prepare($sqlcode1)
                or die "Couldn't prepare statement: ";
$sth1->execute()or die $sth1->errstr;

my $sth2 = $dbh->prepare($sqlcode2)
                or die "Couldn't prepare statement: ";

$sth2->execute()or die $sth2->errstr;

my $sth3 = $dbh->prepare($sqlcode3)
                or die $dbh->errstr;


$sth3->execute()or die $sth3->errstr;

my @ary = $sth3->fetchrow_array;
print $sth3->rows;
print $#ary;
print FILE $ary[0];

close (FILE);

$dbh->disconnect();


it comes up with the below warning and the file created is empty.Please help.


1
DBI::db=HASH(0x1b8ff18)->disconnect invalidates 1 active statement handle (eithe
r destroy statement handles or call finish on them before disconnecting)

-1
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20519105
sorry, I'm not a perl guy.  Is there any telse I can help you with on the orginal question?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23509966
I believe #20495103 answers the original question
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

820 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