Quantcast

Hi Experts - XML Output From PL-SQL Query for Oracle 9

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Hi Experts - XML Output From PL-SQL Query for Oracle 9

hidalgo.horse1
Hi Experts,
   I'm new to oracle and toad. I just started using toad 8.6 and
oracle 9. I have requirement to generate XML string from the PL-SQL
query. I tried using certain commands which will create xml string for
each row of the output of the pl-sql query. I need to get one xml
string out of that pl-sql query which can have information about one
or many rows.
Can any one help me out with an example. I need to execute this query
from vb.net.
 
Thanks
Hidalgo

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Hi Experts - XML Output From PL-SQL Query for Oracle 9

Michael J. Moore
too vague
need more details

----- Original Message ----
From: hidalgo.horse1 <[hidden email]>
To: [hidden email]
Sent: Tuesday, January 30, 2007 10:15:29 AM
Subject: [toad] Hi Experts - XML Output From PL-SQL Query for Oracle 9









 


   
            Hi Experts,

   I'm new to oracle and toad. I just started using toad 8.6 and

oracle 9. I have requirement to generate XML string from the PL-SQL

query. I tried using certain commands which will create xml string for

each row of the output of the pl-sql query. I need to get one xml

string out of that pl-sql query which can have information about one

or many rows.

Can any one help me out with an example. I need to execute this query

from vb.net.

 

Thanks

Hidalgo





   
 

   
   




<!--

#ygrp-mlmsg {font-size:13px;font-family:arial,helvetica,clean,sans-serif;}
#ygrp-mlmsg table {font-size:inherit;font:100%;}
#ygrp-mlmsg select, input, textarea {font:99% arial,helvetica,clean,sans-serif;}
#ygrp-mlmsg pre, code {font:115% monospace;}
#ygrp-mlmsg * {line-height:1.22em;}
#ygrp-text{
font-family:Georgia;
}
#ygrp-text p{
margin:0 0 1em 0;
}
#ygrp-tpmsgs{
font-family:Arial;
clear:both;
}
#ygrp-vitnav{
padding-top:10px;
font-family:Verdana;
font-size:77%;
margin:0;
}
#ygrp-vitnav a{
padding:0 1px;
}
#ygrp-actbar{
clear:both;
margin:25px 0;
white-space:nowrap;
color:#666;
text-align:right;
}
#ygrp-actbar .left{
float:left;
white-space:nowrap;
}
.bld{font-weight:bold;}
#ygrp-grft{
font-family:Verdana;
font-size:77%;
padding:15px 0;
}
#ygrp-ft{
font-family:verdana;
font-size:77%;
border-top:1px solid #666;
padding:5px 0;
}
#ygrp-mlmsg #logo{
padding-bottom:10px;
}

#ygrp-vital{
background-color:#e0ecee;
margin-bottom:20px;
padding:2px 0 8px 8px;
}
#ygrp-vital #vithd{
font-size:77%;
font-family:Verdana;
font-weight:bold;
color:#333;
text-transform:uppercase;
}
#ygrp-vital ul{
padding:0;
margin:2px 0;
}
#ygrp-vital ul li{
list-style-type:none;
clear:both;
border:1px solid #e0ecee;
}
#ygrp-vital ul li .ct{
font-weight:bold;
color:#ff7900;
float:right;
width:2em;
text-align:right;
padding-right:.5em;
}
#ygrp-vital ul li .cat{
font-weight:bold;
}
#ygrp-vital a {
text-decoration:none;
}

#ygrp-vital a:hover{
text-decoration:underline;
}

#ygrp-sponsor #hd{
color:#999;
font-size:77%;
}
#ygrp-sponsor #ov{
padding:6px 13px;
background-color:#e0ecee;
margin-bottom:20px;
}
#ygrp-sponsor #ov ul{
padding:0 0 0 8px;
margin:0;
}
#ygrp-sponsor #ov li{
list-style-type:square;
padding:6px 0;
font-size:77%;
}
#ygrp-sponsor #ov li a{
text-decoration:none;
font-size:130%;
}
#ygrp-sponsor #nc {
background-color:#eee;
margin-bottom:20px;
padding:0 8px;
}
#ygrp-sponsor .ad{
padding:8px 0;
}
#ygrp-sponsor .ad #hd1{
font-family:Arial;
font-weight:bold;
color:#628c2a;
font-size:100%;
line-height:122%;
}
#ygrp-sponsor .ad a{
text-decoration:none;
}
#ygrp-sponsor .ad a:hover{
text-decoration:underline;
}
#ygrp-sponsor .ad p{
margin:0;
}
o {font-size:0;}
.MsoNormal {
margin:0 0 0 0;
}
#ygrp-text tt{
font-size:120%;
}
blockquote{margin:0 0 0 4px;}
.replbq {margin:4;}
-->








 
____________________________________________________________________________________
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail beta.
http://new.mail.yahoo.com
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: Hi Experts - XML Output From PL-SQL Query for Oracle 9

boothie
In reply to this post by hidalgo.horse1
Essentially what I've done is to use the UTL_File utilities to write a
flat file.
 
File_handle := UTL_FILE.FOPEN( Directry, File_name, Access_mode );

UTL_FILE.PUT_LINE( File_handle, Xml_buffer_end );

UTL_FILE.FCLOSE( File_handle );

You'll open the file.
write opening xml.
You'll open the query
loop
   read each line
   write corresponding xml
end loop
write final xml.
close the file
 
You're done.
 
Steve...
________________________________

From: [hidden email] [mailto:[hidden email]] On Behalf Of
hidalgo.horse1
Sent: Tuesday, January 30, 2007 12:15 PM
To: [hidden email]
Subject: [toad] Hi Experts - XML Output From PL-SQL Query for Oracle 9



Hi Experts,
I'm new to oracle and toad. I just started using toad 8.6 and
oracle 9. I have requirement to generate XML string from the PL-SQL
query. I tried using certain commands which will create xml string for
each row of the output of the pl-sql query. I need to get one xml
string out of that pl-sql query which can have information about one
or many rows.
Can any one help me out with an example. I need to execute this query
from vb.net.

Thanks
Hidalgo



 
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Hi Experts - XML Output From PL-SQL Query for Oracle 9

Rodrigo B. Campacci
In reply to this post by hidalgo.horse1
Hi,

You can use DBMS_XMLGEN in Oracle10g.
You pass a query in a string, this package will open a cursor with that
string and will generate a XML with tags with the name of columns. If
you want, you can change another items in XML.

Search in Google to see more details. Alternatively, you can see
something in Quest Xpert KnowledgeBase.

[]s,
Rodrigo B. Campacci


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: Re: Hi Experts - XML Output From PL-SQL Query for Oracle 9

boothie
In reply to this post by hidalgo.horse1
Yep... but unfortunately we are not yet at 10g... I don't know at what
level of the Oracle DB the original asker was at.

________________________________

From: [hidden email] [mailto:[hidden email]] On Behalf Of
Rodrigo Campacci
Sent: Wednesday, January 31, 2007 5:41 AM
To: [hidden email]
Subject: [toad] Re: Hi Experts - XML Output From PL-SQL Query for Oracle
9



Hi,

You can use DBMS_XMLGEN in Oracle10g.
You pass a query in a string, this package will open a cursor with that
string and will generate a XML with tags with the name of columns. If
you want, you can change another items in XML.

Search in Google to see more details. Alternatively, you can see
something in Quest Xpert KnowledgeBase.

[]s,
Rodrigo B. Campacci


 
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Hi Experts - XML Output From PL-SQL Query for Oracle 9

Michael J. Moore
In reply to this post by hidalgo.horse1
Maybe I am not understanding the problem but ...

for cur1 in (select '<mytag>'||mytabledata||'</mytag>' as mynode)
loop
UTL_FILE.PUT_ LINE( File_handle, cur1.mynode );
end;



----- Original Message ----
From: Booth.Steve <[hidden email]>
To: [hidden email]
Sent: Tuesday, January 30, 2007 1:21:08 PM
Subject: RE: [toad] Hi Experts - XML Output From PL-SQL Query for Oracle 9









 


   
           



Essentially what I've done is to use the UTL_File utilities to
write a flat file.

 


File_handle :=
UTL_FILE.FOPEN(
Directry,
File_name, Access_mode
);

UTL_FILE.PUT_ LINE( File_handle, Xml_buffer_end );

UTL_FILE.FCLOSE(
File_handle );


You'll open the file.


write opening xml.
You'll open the
query

loop

   read each line

   write corresponding xml

end
loop

write final xml.

close the file

 

You're done.

 

Steve...

From: toad@yahoogroups. com
[mailto:toad@ yahoogroups. com] On Behalf Of hidalgo.horse1
Sent:
Tuesday, January 30, 2007 12:15 PM
To:
toad@yahoogroups. com
Subject: [toad] Hi Experts - XML Output From
PL-SQL Query for Oracle 9






Hi Experts,
I'm new to oracle and toad. I just started using toad 8.6 and

oracle 9. I have requirement to generate XML string from the PL-SQL

query. I tried using certain commands which will create xml string for

each row of the output of the pl-sql query. I need to get one xml
string
out of that pl-sql query which can have information about one
or many
rows.
Can any one help me out with an example. I need to execute this query

from vb.net.

Thanks
Hidalgo





   
 

   
   




<!--

#ygrp-mlmsg {font-size:13px;font-family:arial,helvetica,clean,sans-serif;}
#ygrp-mlmsg table {font-size:inherit;font:100%;}
#ygrp-mlmsg select, input, textarea {font:99% arial,helvetica,clean,sans-serif;}
#ygrp-mlmsg pre, code {font:115% monospace;}
#ygrp-mlmsg * {line-height:1.22em;}
#ygrp-text{
font-family:Georgia;
}
#ygrp-text p{
margin:0 0 1em 0;
}
#ygrp-tpmsgs{
font-family:Arial;
clear:both;
}
#ygrp-vitnav{
padding-top:10px;
font-family:Verdana;
font-size:77%;
margin:0;
}
#ygrp-vitnav a{
padding:0 1px;
}
#ygrp-actbar{
clear:both;
margin:25px 0;
white-space:nowrap;
color:#666;
text-align:right;
}
#ygrp-actbar .left{
float:left;
white-space:nowrap;
}
.bld{font-weight:bold;}
#ygrp-grft{
font-family:Verdana;
font-size:77%;
padding:15px 0;
}
#ygrp-ft{
font-family:verdana;
font-size:77%;
border-top:1px solid #666;
padding:5px 0;
}
#ygrp-mlmsg #logo{
padding-bottom:10px;
}

#ygrp-vital{
background-color:#e0ecee;
margin-bottom:20px;
padding:2px 0 8px 8px;
}
#ygrp-vital #vithd{
font-size:77%;
font-family:Verdana;
font-weight:bold;
color:#333;
text-transform:uppercase;
}
#ygrp-vital ul{
padding:0;
margin:2px 0;
}
#ygrp-vital ul li{
list-style-type:none;
clear:both;
border:1px solid #e0ecee;
}
#ygrp-vital ul li .ct{
font-weight:bold;
color:#ff7900;
float:right;
width:2em;
text-align:right;
padding-right:.5em;
}
#ygrp-vital ul li .cat{
font-weight:bold;
}
#ygrp-vital a {
text-decoration:none;
}

#ygrp-vital a:hover{
text-decoration:underline;
}

#ygrp-sponsor #hd{
color:#999;
font-size:77%;
}
#ygrp-sponsor #ov{
padding:6px 13px;
background-color:#e0ecee;
margin-bottom:20px;
}
#ygrp-sponsor #ov ul{
padding:0 0 0 8px;
margin:0;
}
#ygrp-sponsor #ov li{
list-style-type:square;
padding:6px 0;
font-size:77%;
}
#ygrp-sponsor #ov li a{
text-decoration:none;
font-size:130%;
}
#ygrp-sponsor #nc {
background-color:#eee;
margin-bottom:20px;
padding:0 8px;
}
#ygrp-sponsor .ad{
padding:8px 0;
}
#ygrp-sponsor .ad #hd1{
font-family:Arial;
font-weight:bold;
color:#628c2a;
font-size:100%;
line-height:122%;
}
#ygrp-sponsor .ad a{
text-decoration:none;
}
#ygrp-sponsor .ad a:hover{
text-decoration:underline;
}
#ygrp-sponsor .ad p{
margin:0;
}
o {font-size:0;}
.MsoNormal {
margin:0 0 0 0;
}
#ygrp-text tt{
font-size:120%;
}
blockquote{margin:0 0 0 4px;}
.replbq {margin:4;}
-->








 
____________________________________________________________________________________
Don't pick lemons.
See all the new 2007 cars at Yahoo! Autos.
http://autos.yahoo.com/new_cars.html 
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Hi Experts - XML Output From PL-SQL Query for Oracle 9

hidalgo.horse1
Thanks friends, for all your support. Here are the detail information about my requirement.
   
  Oracle Version 9
  Visual Studio 2005 [VB.NET]
   
  VB Code:
   
   
   Open Oracle Connection  
   Execute PL-SQL Query / Stored Procedure / Function / Packages.  
   The return from that execution should be only one XML string for all rows [I don’t want to loop through the record set to get xml string for each row]  
   Then generate the XML file on my hard disk and pass the virtual path to the user to download the XML file.
   
  I had tried few commands in PL-SQL but I was able to achieve to an extend, to get one XML string per record which I have to loop through the record set to get the complete XML string. Is there a way in oracle that I get only one output XML string which will have all rows in it and the column name should be the tag name.
   
  Like I said I’m very new to oracle, looking forward to some experts help.


"Michael J. Moore" <[hidden email]> wrote:              Maybe I am not understanding the problem but ...

for cur1 in (select '<mytag>'||mytabledata||'</mytag>' as mynode)
loop
  UTL_FILE.PUT_ LINE( File_handle, cur1.mynode );
  end;



  ----- Original Message ----
From: Booth.Steve <[hidden email]>
To: [hidden email]
Sent: Tuesday, January 30, 2007 1:21:08 PM
Subject: RE: [toad] Hi Experts - XML Output From PL-SQL Query for Oracle 9

   
  Essentially what I've done is to use the UTL_File utilities to write a flat file.
   
    File_handle := UTL_FILE.FOPEN( Directry, File_name, Access_mode );
  UTL_FILE.PUT_ LINE( File_handle, Xml_buffer_end );
  UTL_FILE.FCLOSE( File_handle );

  You'll open the file.
    write opening xml.
You'll open the query
  loop
     read each line
     write corresponding xml
  end loop
  write final xml.
  close the file
   
  You're done.
   
  Steve...  
---------------------------------
  From: toad@yahoogroups. com [mailto:toad@ yahoogroups. com] On Behalf Of hidalgo.horse1
Sent: Tuesday, January 30, 2007 12:15 PM
To: toad@yahoogroups. com
Subject: [toad] Hi Experts - XML Output From PL-SQL Query for Oracle 9


 
    Hi Experts,
I'm new to oracle and toad. I just started using toad 8.6 and
oracle 9. I have requirement to generate XML string from the PL-SQL
query. I tried using certain commands which will create xml string for
each row of the output of the pl-sql query. I need to get one xml
string out of that pl-sql query which can have information about one
or many rows.
Can any one help me out with an example. I need to execute this query
from vb.net.

Thanks
Hidalgo









 
---------------------------------
  Food fight? Enjoy some healthy debate
in the Yahoo! Answers Food Drink Q&A.  

         

 
---------------------------------
Now that's room service! Choose from over 150,000 hotels
in 45,000 destinations on Yahoo! Travel to find your fit.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: Hi Experts - XML Output From PL-SQL Query for Oracle 9

boothie
In reply to this post by hidalgo.horse1
I agree and it works for me. OTOH, I'm not the one who had the problem.

________________________________

From: [hidden email] [mailto:[hidden email]] On Behalf Of
Michael J. Moore
Sent: Wednesday, January 31, 2007 11:30 AM
To: [hidden email]
Subject: Re: [toad] Hi Experts - XML Output From PL-SQL Query for Oracle
9



Maybe I am not understanding the problem but ...

for cur1 in (select '<mytag>'||mytabledata||'</mytag>' as mynode)
loop


UTL_FILE.PUT_ LINE( File_handle, cur1.mynode );

end;




----- Original Message ----
From: Booth.Steve <[hidden email]>
To: [hidden email]
Sent: Tuesday, January 30, 2007 1:21:08 PM
Subject: RE: [toad] Hi Experts - XML Output From PL-SQL Query for Oracle
9



Essentially what I've done is to use the UTL_File utilities to write a
flat file.
 
File_handle := UTL_FILE.FOPEN( Directry, File_name, Access_mode );

UTL_FILE.PUT_ LINE( File_handle, Xml_buffer_end );

UTL_FILE.FCLOSE( File_handle );

You'll open the file.
write opening xml.
You'll open the query
loop
   read each line
   write corresponding xml
end loop
write final xml.
close the file
 
You're done.
 
Steve...
________________________________

From: toad@yahoogroups. com [mailto:toad@ yahoogroups. com] On Behalf Of
hidalgo.horse1
Sent: Tuesday, January 30, 2007 12:15 PM
To: toad@yahoogroups. com
Subject: [toad] Hi Experts - XML Output From PL-SQL Query for Oracle 9



Hi Experts,
I'm new to oracle and toad. I just started using toad 8.6 and
oracle 9. I have requirement to generate XML string from the PL-SQL
query. I tried using certain commands which will create xml string for
each row of the output of the pl-sql query. I need to get one xml
string out of that pl-sql query which can have information about one
or many rows.
Can any one help me out with an example. I need to execute this query
from vb.net.

Thanks
Hidalgo





________________________________

Food fight?
<http://answers.yahoo.com/dir/index;_ylc=X3oDMTFvbGNhMGE3BF9TAzM5NjU0NTE
wOARfcwMzOTY1NDUxMDMEc2VjA21haWxfdGFnbGluZQRzbGsDbWFpbF90YWcx?link=ask&s
id=396545367>  Enjoy some healthy debate
in the Yahoo! Answers Food Drink Q&A.
<http://answers.yahoo.com/dir/index;_ylc=X3oDMTFvbGNhMGE3BF9TAzM5NjU0NTE
wOARfcwMzOTY1NDUxMDMEc2VjA21haWxfdGFnbGluZQRzbGsDbWFpbF90YWcx?link=ask&s
id=396545367>  

 
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: Hi Experts - XML Output From PL-SQL Query for Oracle 9

boothie
In reply to this post by hidalgo.horse1
It will depend upon how long your xml response string is going to need
to be. If it's less than 4000 characters:
RetStr   VARCHAR2(4000) := '';
 
Do as Michael suggests, but instead of the UTL_File command, issue:
 
RetStr := RetStr || cur1.mynode;
 
Return RetStr;

________________________________

From: [hidden email] [mailto:[hidden email]] On Behalf Of
Hidalgo Horse
Sent: Wednesday, January 31, 2007 1:35 PM
To: [hidden email]
Subject: Re: [toad] Hi Experts - XML Output From PL-SQL Query for Oracle
9



Thanks friends, for all your support. Here are the detail information
about my requirement.
Oracle Version 9
Visual Studio 2005 [VB.NET]
VB Code:

1. Open Oracle Connection
2. Execute PL-SQL Query / Stored Procedure / Function / Packages.
3. The return from that execution should be only one XML string for
all rows [I don't want to loop through the record set to get xml string
for each row]
4. Then generate the XML file on my hard disk and pass the virtual
path to the user to download the XML file.

I had tried few commands in PL-SQL but I was able to achieve to an
extend, to get one XML string per record which I have to loop through
the record set to get the complete XML string. Is there a way in oracle
that I get only one output XML string which will have all rows in it and
the column name should be the tag name.
Like I said I'm very new to oracle, looking forward to some experts
help.


"Michael J. Moore" <[hidden email]> wrote:

        Maybe I am not understanding the problem b ut ...
       
        for cur1 in (select '<mytag>'||mytabledata||'</mytag>' as
mynode)
        loop
       
        UTL_FILE.PUT_ LINE( File_handle, cur1.mynode );
        end;
       


        ----- Original Message ----
        From: Booth.Steve <[hidden email]>
        To: [hidden email]
        Sent: Tuesday, January 30, 2007 1:21:08 PM
        Subject: RE: [toad] Hi Experts - XML Output From PL-SQL Query
for Oracle 9
       
       
        Essentially what I've done is to use the UTL_File utilities to
write a f! lat file.
         
       
        File_handle := UTL_FILE.FOPEN( Directry, File_name, Access_mode
);
        UTL_FILE.PUT_ LINE( File_handle, Xml_buffer_end );
       
        UTL_FILE.FCLOSE( File_handle );
        You'll open the file.
       
        write opening xml.
        You'll open the query
        loop
           read each line
           write corresponding xml
        end loop
        write final xml.
        close the file
         
        You're done.
         
        Steve...
________________________________

        From: toad@yahoogroups. com [mailto:toad@ yahoogroups. com] On
Behalf Of hidalgo.horse1
        Sent: Tuesday, January 30, 2007 12:15 PM
        To: toad@yahoogroups. com
        Subject: [toad] Hi Experts - XML Output From PL-SQL Query for
Oracle 9
       
       
        Hi Experts,
        I'm new to oracle and toad. I just started using toad 8.6! and
        oracle 9. I have requirement to generate XML string from the
PL-SQL
        query. I tried using certain commands which will create xml
string for
        each row of the output of the pl-sql query. I need to get one
xml
        string out of that pl-sql query which can have information about
one
        or many rows.
        Can any one help me out with an example. I need to execute this
query
        from vb.net.
       
        Thanks
        Hidalgo
       
       


________________________________

        Food fight?
<http://answers.yahoo.com/dir/index;_ylc=X3oDMTFvbGNhMGE3BF9TAzM5NjU0NTE
wOARfcwMzOTY1NDUxMDMEc2VjA21haWxfdGFnbGluZQRzbGsDbWFpbF90YWcx?link=ask&s
id=396545367>  Enjoy some healthy debate
        in the Yahoo! Answers Food Drink Q&A.
<http://answers.yahoo.com/dir/index;_ylc=X3oDMTFvbGNhMGE3BF9TAzM5NjU0NTE
wOARfcwMzOTY1NDUxMDMEc2VjA21haWxfdGFnbGluZQRzbGsDbWFpbF90YWcx?link=ask&s
id=396545367>  


________________________________

Now that's room service! Choose from over 150,000 hotels
in 45,000 destinations on Yahoo! Travel
<http://travel.yahoo.com/hotelsearchpage;_ylc=X3oDMTFtaTIzNXVjBF9TAzk3ND
A3NTg5BF9zAzI3MTk0ODEEcG9zAzIEc2VjA21haWx0YWdsaW5lBHNsawNxMS0wNw-->  to
find your fit.

 
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: Hi Experts - XML Output From PL-SQL Query for Oracle 9

Jimmy Sanby (J)
In reply to this post by boothie

You can also use the following to generate xml....
 
SELECT XMLELEMENT ("rowset",
                   XMLAGG (XMLELEMENT ("row",
                                       XMLELEMENT ("TABLE",
table_name),
                                       XMLELEMENT ("TABLESPACE",
tablespace_name)
                                  )
                   )
        ).getclobval () xml
  FROM user_tables
 
Jimmy

________________________________

From: [hidden email] [mailto:[hidden email]] On Behalf Of
Booth.Steve
Sent: 30 January 2007 11:21 PM
To: [hidden email]
Subject: RE: [toad] Hi Experts - XML Output From PL-SQL Query for
Oracle 9



Essentially what I've done is to use the UTL_File utilities to write a
flat file.
 
File_handle := UTL_FILE.FOPEN( Directry, File_name, Access_mode );

UTL_FILE.PUT_LINE( File_handle, Xml_buffer_end );

UTL_FILE.FCLOSE( File_handle );

You'll open the file.
write opening xml.
You'll open the query
loop
   read each line
   write corresponding xml
end loop
write final xml.
close the file
 
You're done.
 
Steve...
________________________________

From: [hidden email] [mailto:[hidden email]] On Behalf Of
hidalgo.horse1
Sent: Tuesday, January 30, 2007 12:15 PM
To: [hidden email]
Subject: [toad] Hi Experts - XML Output From PL-SQL Query for Oracle 9



Hi Experts,
I'm new to oracle and toad. I just started using toad 8.6 and
oracle 9. I have requirement to generate XML string from the PL-SQL
query. I tried using certain commands which will create xml string for

each row of the output of the pl-sql query. I need to get one xml
string out of that pl-sql query which can have information about one
or many rows.
Can any one help me out with an example. I need to execute this query
from vb.net.

Thanks
Hidalgo



 


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This e-mail and its contents are subject to the Telkom SA Limited
e-mail legal notice available at
http://www.telkom.co.za/TelkomEMailLegalNotice.PDF
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: Hi Experts - XML Output From PL-SQL Query for Oracle 9

GEORGE.Brennan
In reply to this post by hidalgo.horse1
it looks like the query is the issue try something like..
 
declare
   x as clob;
begin
 
     SELECT XMLELEMENT("OuterTag", XMLAGG( XMLELEMENT ("InnerTag",
t.col)))
         INTO x  
       FROM table t;
 
      -- output limit
     DBMS_OUTPUT.PUT_LINE ( 'x = ' || substr(x ,0, 250) );
 
      -- output x to a file careful of the any length limitations
 
end;
 

George.






________________________________

From: [hidden email] [mailto:[hidden email]] On Behalf Of
Hidalgo Horse
Sent: 31 January 2007 19:35
To: [hidden email]
Subject: Re: [toad] Hi Experts - XML Output From PL-SQL Query for Oracle
9



Thanks friends, for all your support. Here are the detail information
about my requirement.
Oracle Version 9
Visual Studio 2005 [VB.NET]
VB Code:

1. Open Oracle Connection
2. Execute PL-SQL Query / Stored Procedure / Function / Packages.
3. The return from that execution should be only one XML string for
all rows [I don't want to loop through the record set to get xml string
for each row]
4. Then generate the XML file on my hard disk and pass the virtual
path to the user to download the XML file.

I had tried few commands in PL-SQL but I was able to achieve to an
extend, to get one XML string per record which I have to loop through
the record set to get the complete XML string. Is there a way in oracle
that I get only one output XML string which will have all rows in it and
the column name should be the tag name.
Like I said I'm very new to oracle, looking forward to some experts
help.


"Michael J. Moore" <[hidden email]> wrote:

        Maybe I am not understanding the problem but ...
       
        for cur1 in (select '<mytag>'||mytabledata||'</mytag>' as
mynode)
        loop
       
        UTL_FILE.PUT_ LINE( File_handle, cur1.mynode );
        end;
       


        ----- Original Message ----
        From: Booth.Steve <[hidden email]>
        To: [hidden email]
        Sent: Tuesday, January 30, 2007 1:21:08 PM
        Subject: RE: [toad] Hi Experts - XML Output From PL-SQL Query
for Oracle 9
       
       
        Essentially what I've done is to use the UTL_File utilities to
write a flat file.
         
       
        File_handle := UTL_FILE.FOPEN( Directry, File_name, Access_mode
);
        UTL_FILE.PUT_ LINE( File_handle, Xml_buffer_end );
       
        UTL_FILE.FCLOSE( File_handle );
        You'll open the file.
       
        write opening xml.
        You'll open the query
        loop
           read each line
           write corresponding xml
        end loop
        write final xml.
        close the file
         
        You're done.
         
        Steve...
________________________________

        From: toad@yahoogroups. com [mailto:toad@ yahoogroups. com] On
Behalf Of hidalgo.horse1
        Sent: Tuesday, January 30, 2007 12:15 PM
        To: toad@yahoogroups. com
        Subject: [toad] Hi Experts - XML Output From PL-SQL Query for
Oracle 9
       
       
        Hi Experts,
        I'm new to oracle and toad. I just started using toad 8.6 and
        oracle 9. I have requirement to generate XML string from the
PL-SQL
        query. I tried using certain commands which will create xml
string for
        each row of the output of the pl-sql query. I need to get one
xml
        string out of that pl-sql query which can have information about
one
        or many rows.
        Can any one help me out with an example. I need to execute this
query
        from vb.net.
       
        Thanks
        Hidalgo
       
       


________________________________

        Food fight?
<http://answers.yahoo.com/dir/index;_ylc=X3oDMTFvbGNhMGE3BF9TAzM5NjU0NTE
wOARfcwMzOTY1NDUxMDMEc2VjA21haWxfdGFnbGluZQRzbGsDbWFpbF90YWcx?link=ask&s
id=396545367>  Enjoy some healthy debate
        in the Yahoo! Answers Food Drink Q&A.
<http://answers.yahoo.com/dir/index;_ylc=X3oDMTFvbGNhMGE3BF9TAzM5NjU0NTE
wOARfcwMzOTY1NDUxMDMEc2VjA21haWxfdGFnbGluZQRzbGsDbWFpbF90YWcx?link=ask&s
id=396545367>  


________________________________

Now that's room service! Choose from over 150,000 hotels
in 45,000 destinations on Yahoo! Travel
<http://travel.yahoo.com/hotelsearchpage;_ylc=X3oDMTFtaTIzNXVjBF9TAzk3ND
A3NTg5BF9zAzI3MTk0ODEEcG9zAzIEc2VjA21haWx0YWdsaW5lBHNsawNxMS0wNw-->  to
find your fit.

 


******************************************************************************
This E-mail transmission and any attachments may contain confidential or legally privileged information that is intended for the addressee(s) only. Any views or opinions presented are solely those of the author and do not necessarily represent those of Morley Fund Management. If you are not the intended recipient or person responsible for delivering this information to the intended recipient you are hereby notified that any disclosure, copying, distribution or reliance upon the contents of this E-mail is strictly prohibited. If you have received this email transmission in error, please notify the sender immediately, so that Morley Fund Management may arrange for its proper delivery. Please delete the message from your inbox.
 
Morley Fund Management is a business name of Morley Fund Management Limited, registered in England No. 1151805. Authorised and regulated in the UK by the Financial Services Authority and a member of the Investment Management Association. Registered Office: No.1 Poultry, London EC2R 8EJ.  Entered in the Financial Services Authority register, No: 119178  www.fsa.gov.uk/register.

Morley Fund Management Limited charges Value Added Tax (VAT) for its investment management services. The Company's VAT number is 105 4373 00.

Morley Fund Management is also a business name of Morley Fund Management International Limited, registered in England No. 2152949. Registered office: No. 1 Poultry, London, UK, EC2R 8EJ and Morley Fund Services Limited, registered in England No. 5547711. Registered office: No. 1 Poultry, London, UK, EC2R 8EJ which is entered in the Financial Services Authority register, No: 439307  www.fsa.gov.uk/register.   All are Aviva companies.  
 
Telephone calls to Morley Fund Management may be recorded for training or monitoring purposes.   Morley may monitor traffic data of both business and personal e-mails. By replying to this e-mail, you consent to Morley monitoring the content of any e-mails you send to or receive from Morley.
******************************************************************************

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Hi Experts - XML Output From PL-SQL Query for Oracle 9

Pospisil, Pavel
In reply to this post by boothie
You can try use DBMS_XMLQUERY package. For example:

   PROCEDURE getXMLData (sqlstr IN CLOB, xmlLOB IN OUT CLOB)
   IS
      queryCtx DBMS_XMLQuery.ctxtype;

   BEGIN
      queryCtx := DBMS_XMLQuery.NewContext(sqlstr);
      DBMS_XMLQuery.propagateOriginalException(queryCtx, TRUE);
      DBMS_XMLQUERY.setDateFormat(queryCtx, 'yyyy-MM-dd');
      xmlLOB := DBMS_XMLQuery.getXML(queryCtx);
      DBMS_XMLQuery.closeContext(queryCtx);

   END getXMLData;


where sqlstr is your select like 'select tname, tabtype from tab'.
With this package you can generate also complicated XML (user types
and object view must be defined)...

Pavel

--- In [hidden email], "Booth.Steve" <steve.booth@...> wrote:
>
> It will depend upon how long your xml response string is going to
need

> to be. If it's less than 4000 characters:
> RetStr   VARCHAR2(4000) := '';
>  
> Do as Michael suggests, but instead of the UTL_File command, issue:
>  
> RetStr := RetStr || cur1.mynode;
>  
> Return RetStr;
>
> ________________________________
>
> From: [hidden email] [mailto:[hidden email]] On Behalf
Of
> Hidalgo Horse
> Sent: Wednesday, January 31, 2007 1:35 PM
> To: [hidden email]
> Subject: Re: [toad] Hi Experts - XML Output From PL-SQL Query for
Oracle
> 9
>
>
>
> Thanks friends, for all your support. Here are the detail
information
> about my requirement.
> Oracle Version 9
> Visual Studio 2005 [VB.NET]
> VB Code:
>
> 1. Open Oracle Connection
> 2. Execute PL-SQL Query / Stored Procedure / Function /
Packages.
> 3. The return from that execution should be only one XML string
for
> all rows [I don't want to loop through the record set to get xml
string
> for each row]
> 4. Then generate the XML file on my hard disk and pass the
virtual
> path to the user to download the XML file.
>
> I had tried few commands in PL-SQL but I was able to achieve to an
> extend, to get one XML string per record which I have to loop
through
> the record set to get the complete XML string. Is there a way in
oracle
> that I get only one output XML string which will have all rows in
it and

> the column name should be the tag name.
> Like I said I'm very new to oracle, looking forward to some experts
> help.
>
>
> "Michael J. Moore" <hicamel@...> wrote:
>
> Maybe I am not understanding the problem b ut ...
>
> for cur1 in (select '<mytag>'||mytabledata||'</mytag>' as
> mynode)
> loop
>
> UTL_FILE.PUT_ LINE( File_handle, cur1.mynode );
> end;
>
>
>
> ----- Original Message ----
> From: Booth.Steve <steve.booth@...>
> To: [hidden email]
> Sent: Tuesday, January 30, 2007 1:21:08 PM
> Subject: RE: [toad] Hi Experts - XML Output From PL-SQL Query
> for Oracle 9
>
>
> Essentially what I've done is to use the UTL_File utilities to
> write a f! lat file.
>
>
> File_handle := UTL_FILE.FOPEN( Directry, File_name,
Access_mode

> );
> UTL_FILE.PUT_ LINE( File_handle, Xml_buffer_end );
>
> UTL_FILE.FCLOSE( File_handle );
> You'll open the file.
>
> write opening xml.
> You'll open the query
> loop
>   read each line
>   write corresponding xml
> end loop
> write final xml.
> close the file
>
> You're done.
>
> Steve...
> ________________________________
>
> From: toad@yahoogroups. com [mailto:toad@ yahoogroups. com] On
> Behalf Of hidalgo.horse1
> Sent: Tuesday, January 30, 2007 12:15 PM
> To: toad@yahoogroups. com
> Subject: [toad] Hi Experts - XML Output From PL-SQL Query for
> Oracle 9
>
>
> Hi Experts,
> I'm new to oracle and toad. I just started using toad 8.6!
and
> oracle 9. I have requirement to generate XML string from the
> PL-SQL
> query. I tried using certain commands which will create xml
> string for
> each row of the output of the pl-sql query. I need to get one
> xml
> string out of that pl-sql query which can have information
about
> one
> or many rows.
> Can any one help me out with an example. I need to execute
this

> query
> from vb.net.
>
> Thanks
> Hidalgo
>
>
>
>
> ________________________________
>
> Food fight?
>
<http://answers.yahoo.com/dir/index;_ylc=X3oDMTFvbGNhMGE3BF9TAzM5NjU0N
TE
> wOARfcwMzOTY1NDUxMDMEc2VjA21haWxfdGFnbGluZQRzbGsDbWFpbF90YWcx?
link=ask&s
> id=396545367>  Enjoy some healthy debate
> in the Yahoo! Answers Food Drink Q&A.
>
<http://answers.yahoo.com/dir/index;_ylc=X3oDMTFvbGNhMGE3BF9TAzM5NjU0N
TE
> wOARfcwMzOTY1NDUxMDMEc2VjA21haWxfdGFnbGluZQRzbGsDbWFpbF90YWcx?
link=ask&s
> id=396545367>  
>
>
> ________________________________
>
> Now that's room service! Choose from over 150,000 hotels
> in 45,000 destinations on Yahoo! Travel
>
<http://travel.yahoo.com/hotelsearchpage;_ylc=X3oDMTFtaTIzNXVjBF9TAzk3
ND
> A3NTg5BF9zAzI3MTk0ODEEcG9zAzIEc2VjA21haWx0YWdsaW5lBHNsawNxMS0wNw--
>  to
> find your fit.
>


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Re: Hi Experts - XML Output From PL-SQL Query for Oracle 9

Michael J. Moore
In reply to this post by hidalgo.horse1
Pravel's solution is very nice, In fact I might use it in the future.
Here is another solution, but it has it's limits ...

DECLARE
   xmlstr   VARCHAR2 ( 4000 ) := '<root>';
BEGIN
   FOR cur1 IN ( SELECT portal_name
                  FROM tx_portal
                 WHERE ROWNUM < 4 )
   LOOP
      xmlstr := xmlstr || '<abc>' || trim(cur1.portal_name) || '</abc>';
   END LOOP;

   xmlstr := xmlstr || '</root>';
   DBMS_OUTPUT.put_line ( SUBSTR ( xmlstr, 1, 100 ) );
END;

The result (xmlstr) looks like ...
<root><abc>TestPortalForEdu</abc><abc>TestPortalBTOBDev</abc><abc>ADTDEFENDER</abc></root>

This is only good for small amounts of data.
Mike



----- Original Message ----
From: ppospisil2000 <[hidden email]>
To: [hidden email]
Sent: Thursday, February 1, 2007 2:35:39 AM
Subject: [toad] Re: Hi Experts - XML Output From PL-SQL Query for Oracle 9









 


   
            You can try use DBMS_XMLQUERY package. For example:



PROCEDURE getXMLData (sqlstr IN CLOB, xmlLOB IN OUT CLOB)

   IS

      queryCtx DBMS_XMLQuery. ctxtype;



BEGIN

      queryCtx := DBMS_XMLQuery. NewContext( sqlstr);

      DBMS_XMLQuery. propagateOrigina lException( queryCtx, TRUE);

      DBMS_XMLQUERY. setDateFormat( queryCtx, 'yyyy-MM-dd' );

      xmlLOB := DBMS_XMLQuery. getXML(queryCtx) ;

      DBMS_XMLQuery. closeContext( queryCtx) ;



END getXMLData;



where sqlstr is your select like 'select tname, tabtype from tab'.

With this package you can generate also complicated XML (user types

and object view must be defined)...



Pavel



--- In toad@yahoogroups. com, "Booth.Steve" <steve.booth@ ...> wrote:

>

> It will depend upon how long your xml response string is going to

need

> to be. If it's less than 4000 characters:

> RetStr   VARCHAR2(4000) := '';

>  

> Do as Michael suggests, but instead of the UTL_File command, issue:

>  

> RetStr := RetStr || cur1.mynode;

>  

> Return RetStr;

>

> ____________ _________ _________ __

>

> From: toad@yahoogroups. com [mailto:toad@yahoogroups. com] On Behalf

Of

> Hidalgo Horse

> Sent: Wednesday, January 31, 2007 1:35 PM

> To: toad@yahoogroups. com

> Subject: Re: [toad] Hi Experts - XML Output From PL-SQL Query for

Oracle

> 9

>

>

>

> Thanks friends, for all your support. Here are the detail

information

> about my requirement.

> Oracle Version 9

> Visual Studio 2005 [VB.NET]

> VB Code:

>

> 1. Open Oracle Connection

> 2. Execute PL-SQL Query / Stored Procedure / Function /

Packages.

> 3. The return from that execution should be only one XML string

for

> all rows [I don't want to loop through the record set to get xml

string

> for each row]

> 4. Then generate the XML file on my hard disk and pass the

virtual

> path to the user to download the XML file.

>

> I had tried few commands in PL-SQL but I was able to achieve to an

> extend, to get one XML string per record which I have to loop

through

> the record set to get the complete XML string. Is there a way in

oracle

> that I get only one output XML string which will have all rows in

it and

> the column name should be the tag name.

> Like I said I'm very new to oracle, looking forward to some experts

> help.

>

>

> "Michael J. Moore" <hicamel@... > wrote:

>

> Maybe I am not understanding the problem b ut ...

>

> for cur1 in (select '<mytag>'||mytabled ata||'</mytag> ' as

> mynode)

> loop

>

> UTL_FILE.PUT_ LINE( File_handle, cur1.mynode );

> end;

>

>

>

> ----- Original Message ----

> From: Booth.Steve <steve.booth@ ...>

> To: toad@yahoogroups. com

> Sent: Tuesday, January 30, 2007 1:21:08 PM

> Subject: RE: [toad] Hi Experts - XML Output From PL-SQL Query

> for Oracle 9

>

>

> Essentially what I've done is to use the UTL_File utilities to

> write a f! lat file.

>

>

> File_handle := UTL_FILE.FOPEN( Directry, File_name,

Access_mode

> );

> UTL_FILE.PUT_ LINE( File_handle, Xml_buffer_end );

>

> UTL_FILE.FCLOSE( File_handle );

> You'll open the file.

>

> write opening xml.

> You'll open the query

> loop

>   read each line

>   write corresponding xml

> end loop

> write final xml.

> close the file

>

> You're done.

>

> Steve...

> ____________ _________ _________ __

>

> From: toad@yahoogroups. com [mailto:toad@ yahoogroups. com] On

> Behalf Of hidalgo.horse1

> Sent: Tuesday, January 30, 2007 12:15 PM

> To: toad@yahoogroups. com

> Subject: [toad] Hi Experts - XML Output From PL-SQL Query for

> Oracle 9

>

>

> Hi Experts,

> I'm new to oracle and toad. I just started using toad 8.6!

and

> oracle 9. I have requirement to generate XML string from the

> PL-SQL

> query. I tried using certain commands which will create xml

> string for

> each row of the output of the pl-sql query. I need to get one

> xml

> string out of that pl-sql query which can have information

about

> one

> or many rows.

> Can any one help me out with an example. I need to execute

this

> query

> from vb.net.

>

> Thanks

> Hidalgo

>

>

>

>

> ____________ _________ _________ __

>

> Food fight?

>

<http://answers. yahoo.com/ dir/index; _ylc=X3oDMTFvbGN hMGE3BF9TAzM5NjU 0N

TE

> wOARfcwMzOTY1NDUxMD MEc2VjA21haWxfdG FnbGluZQRzbGsDbW FpbF90YWcx?

link=ask&s

> id=396545367>  Enjoy some healthy debate

> in the Yahoo! Answers Food Drink Q&A.

>

<http://answers. yahoo.com/ dir/index; _ylc=X3oDMTFvbGN hMGE3BF9TAzM5NjU 0N

TE

> wOARfcwMzOTY1NDUxMD MEc2VjA21haWxfdG FnbGluZQRzbGsDbW FpbF90YWcx?

link=ask&s

> id=396545367>  

>

>

> ____________ _________ _________ __

>

> Now that's room service! Choose from over 150,000 hotels

> in 45,000 destinations on Yahoo! Travel

>

<http://travel. yahoo.com/ hotelsearchpage; _ylc=X3oDMTFtaTI zNXVjBF9TAzk3

ND

> A3NTg5BF9zAzI3MTk0O DEEcG9zAzIEc2VjA 21haWx0YWdsaW5lB HNsawNxMS0wNw- -

>  to

> find your fit.

>





   
 

   
   




<!--

#ygrp-mlmsg {font-size:13px;font-family:arial,helvetica,clean,sans-serif;}
#ygrp-mlmsg table {font-size:inherit;font:100%;}
#ygrp-mlmsg select, input, textarea {font:99% arial,helvetica,clean,sans-serif;}
#ygrp-mlmsg pre, code {font:115% monospace;}
#ygrp-mlmsg * {line-height:1.22em;}
#ygrp-text{
font-family:Georgia;
}
#ygrp-text p{
margin:0 0 1em 0;
}
#ygrp-tpmsgs{
font-family:Arial;
clear:both;
}
#ygrp-vitnav{
padding-top:10px;
font-family:Verdana;
font-size:77%;
margin:0;
}
#ygrp-vitnav a{
padding:0 1px;
}
#ygrp-actbar{
clear:both;
margin:25px 0;
white-space:nowrap;
color:#666;
text-align:right;
}
#ygrp-actbar .left{
float:left;
white-space:nowrap;
}
.bld{font-weight:bold;}
#ygrp-grft{
font-family:Verdana;
font-size:77%;
padding:15px 0;
}
#ygrp-ft{
font-family:verdana;
font-size:77%;
border-top:1px solid #666;
padding:5px 0;
}
#ygrp-mlmsg #logo{
padding-bottom:10px;
}

#ygrp-vital{
background-color:#e0ecee;
margin-bottom:20px;
padding:2px 0 8px 8px;
}
#ygrp-vital #vithd{
font-size:77%;
font-family:Verdana;
font-weight:bold;
color:#333;
text-transform:uppercase;
}
#ygrp-vital ul{
padding:0;
margin:2px 0;
}
#ygrp-vital ul li{
list-style-type:none;
clear:both;
border:1px solid #e0ecee;
}
#ygrp-vital ul li .ct{
font-weight:bold;
color:#ff7900;
float:right;
width:2em;
text-align:right;
padding-right:.5em;
}
#ygrp-vital ul li .cat{
font-weight:bold;
}
#ygrp-vital a {
text-decoration:none;
}

#ygrp-vital a:hover{
text-decoration:underline;
}

#ygrp-sponsor #hd{
color:#999;
font-size:77%;
}
#ygrp-sponsor #ov{
padding:6px 13px;
background-color:#e0ecee;
margin-bottom:20px;
}
#ygrp-sponsor #ov ul{
padding:0 0 0 8px;
margin:0;
}
#ygrp-sponsor #ov li{
list-style-type:square;
padding:6px 0;
font-size:77%;
}
#ygrp-sponsor #ov li a{
text-decoration:none;
font-size:130%;
}
#ygrp-sponsor #nc {
background-color:#eee;
margin-bottom:20px;
padding:0 8px;
}
#ygrp-sponsor .ad{
padding:8px 0;
}
#ygrp-sponsor .ad #hd1{
font-family:Arial;
font-weight:bold;
color:#628c2a;
font-size:100%;
line-height:122%;
}
#ygrp-sponsor .ad a{
text-decoration:none;
}
#ygrp-sponsor .ad a:hover{
text-decoration:underline;
}
#ygrp-sponsor .ad p{
margin:0;
}
o {font-size:0;}
.MsoNormal {
margin:0 0 0 0;
}
#ygrp-text tt{
font-size:120%;
}
blockquote{margin:0 0 0 4px;}
.replbq {margin:4;}
-->








 
____________________________________________________________________________________
Now that's room service!  Choose from over 150,000 hotels
in 45,000 destinations on Yahoo! Travel to find your fit.
http://farechase.yahoo.com/promo-generic-14795097
Loading...