Solved

Oracle And XML

Posted on 2007-11-19
23
319 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 73

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 73

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
 
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 73

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 73

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 73

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 73

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 73

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 73

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 73

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 73

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 73

Expert Comment

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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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…
This video shows how to recover a database from a user managed backup

864 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

25 Experts available now in Live!

Get 1:1 Help Now