Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

Oracle And XML

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
chisun7679
Asked:
chisun7679
  • 11
  • 8
1 Solution
 
sdstuberCommented:
Can you provide create table scripts and sample data to work with?
0
 
chisun7679Author Commented:
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
 
sdstuberCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
soraCommented:
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
 
chisun7679Author Commented:
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
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
chisun7679Author Commented:
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
 
sdstuberCommented:
Can you show me what you're trying to do?
0
 
chisun7679Author Commented:
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
 
sdstuberCommented:
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
 
chisun7679Author Commented:
is there any xml function that can take care of the mis-match???
0
 
sdstuberCommented:
no

are you getting the funny characters with the hr schema data?
0
 
chisun7679Author Commented:
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
 
sdstuberCommented:
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
 
chisun7679Author Commented:
can i use the xmforest instead of the xmlattributes function to include a bunch of fields for a particular element?
0
 
sdstuberCommented:
should be able to, you can also add more than one attribute using xmlattributes.
0
 
chisun7679Author Commented:
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
 
sdstuberCommented:
sorry, I'm not a perl guy.  Is there any telse I can help you with on the orginal question?
0
 
sdstuberCommented:
I believe #20495103 answers the original question
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now