Solved

Oracle And XML

Posted on 2007-11-19
23
322 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

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…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

732 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