Solved

Oracle And XML

Posted on 2007-11-19
23
318 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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…

707 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

17 Experts available now in Live!

Get 1:1 Help Now