In real time applications, there is always a requirement to generate the data selected from database in XML format.
using Oracle, generating XML Structured data becomes easier using the inbuilt package DBMS_XMLGEN. This package can be used to convert the OUTPUT of the SELECT Query in to XML Format.
The below code can be used to generate the SELECT Query OUTPUT in XML Format: -
-
DECLARE
-
my_context NUMBER := 0;
-
sample_xml_output VARCHAR2(32767);
-
data_size NUMBER := 0;
-
data_chunk NUMBER := 0;
-
offset NUMBER := 0;
-
-
BEGIN
-
-
-- Create a New Context
-
-
my_context:= DBMS_XMLGEN.NEWCONTEXT ('SELECT * FROM emp order by 1');
-
DBMS_OUTPUT.PUT_LINE('My New Context ID is:'||my_context);
-
-
-- Set the Root Element
-
-
DBMS_XMLGEN.SETROWSETTAG(my_context,'EMP_DETAILS');
-
-
-- Set the Row Element
-
-
DBMS_XMLGEN.SETROWTAG(my_context,'EMP');
-
-
-- Generate XML Output
-
-
SELECT DBMS_XMLGEN.GETXML(my_context) INTO sample_xml_output FROM DUAL;
-
data_chunk := LENGTH(sample_xml_output);
-
offset := 0;
-
data_size := 200;
-
-
-- Print 200 Chunk of data on Screen
-
-
LOOP
-
DBMS_OUTPUT.PUT_LINE(SUBSTR(sample_xml_output,offset + 1,data_size));
-
offset:= offset + data_size;
-
data_size := LEAST(200,data_chunk - data_size);
-
data_chunk := data_chunk - data_size;
-
EXIT WHEN data_chunk <= 0;
-
END LOOP;
-
-
-- Close the Context
-
-
DBMS_XMLGEN.CLOSECONTEXT(my_context);
-
-
--Exception Block
-
-
EXCEPTION
-
WHEN OTHERS THEN
-
NULL; -- Add your exception handler message here
-
END;
-
/
-
-
Let me explain each Functions/Procedures used in the above code: DBMS_XMLGEN.NEW CONTEXT
This function is used to create a NEW CONTEXT that returns the CONTEXTID. This CONTEXTID will be required when other functions/procedures of DBMS_XMLGEN package is used DBMS_XMLGEN.SET ROWSETTAG
This Procedure is used to SET the ROOT ELEMENT for the XML Data. In the above example, I am setting the ROOT ELEMENT as EMP_DETAILS. You can check the ROOT ELEMENT in the Output posted below which will be set to EMP_DETAILS. The Root Element can be set to anything depending on the type of data it holds. DBMS_XMLGEN.SET ROWTAG
This Procedure is used to SET the ROW TAG for the XML Data. Since the output is Employee details, I am setting the ROW TAG to EMP DBMS_XMLGEN.GET XML
This Function is used to generate the XML Structured output for the New Context that we have created. While creating a New Context, we passed a SELECT Query as an Input Parameter. So using this Function will Convert the Output of the SELECT Query to XML Format Data using the Corresponding ROW TAG andROWSET TAG that we have defined using SETROWSETTAG and SETROWTAG Procedures as shown above
Check below Sample Output generated by the code posted above: 4 9957
I found it very useful .... coordination of oracle and xml is good.
Hi
I Have a doubt.
These are my emp table records
EmpId EmpName
1 AAA
2 BBB
3 CCC
I want to generate xml in below format.
<emp>
<1>AAA</1>
<2>BBB</2>
<3>CCC</3>
</emp>
Can you kindly help me?
Thank you for this exampel it has helped me alot. Much appreciated.
Thanks for your post. It helped me
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Santah |
last post by:
hi
I'm new to C++
and I'm currently working on Visual C++ 6.0
I'm trying to open a text file, and read some data from it
part of the text file looks like this:
--------
|
by: DaveS |
last post by:
Hello,
I'm using Oracle 9.2 and would like to automatically save a copy of
each deleted record in XML format to another database table. There
would be one XML record for each deleted record. I'd like to store
the XML record in a CLOB. I've created a table and added a trigger to
capture the delete event but I'm not sure how to create the XML
document. Each deleted record is comprised of a single row from a
parent table and multiple...
|
by: Da-Breegster |
last post by:
Hi. I'm attempting to write a roguelike (think nethack, crawl, angband,
tome, adom, and yes, I suppose even rogue) in Perl and I've ran into a
problem regarding the data structure for the map and handling it easily.
A map is one 2D level, a simple grid. Each tile on the grid will be a
hash of data about the grid. The map object itself has other bits of
data, so that's why I don't just make the object an anonymous array:
$map->{Map} = {...
|
by: Siva |
last post by:
Hi,
I have a 3 tier ASP.Net app for a handheld which needs to fetch
orders from database via a DAL and populate it in a gridview using
objectdatasource. In the search page I have a few parameters and when
the search button is clicked I am sending those parameters via query
string ( I need to do this since my app will run in a handheld which
has certain restrictions). In the order list page, I am passing those
parameters to the DAL as...
|
by: deepdata |
last post by:
Hi,
I am trying to fetch data from db2 (express version) database by
calling stored procedure. I have tried to use both cursor and for loop
but still i am getting error.
--======Start procedure=============
Create PROCEDURE get_timedout_scripts
(
| |
by: Charles Law |
last post by:
Here's one that should probably have the sub-heading "I'm sure I asked this
once before, but ...".
Two users are both looking at the same data, from a database. One user
changes the data and commits it. How does the other user get the updated
view without polling for changes? Is there some sort of callback mechanism
that can be set up on the dataset or connection?
TIA
|
by: rpsetzer |
last post by:
I have to create a big web application and I was thinking of using a
data layer. For each entity in the database, I'll define a class that
maps the table structure, having sub-objects for each foreign key,
having insert/delete/update methods, the usual deal. Yet, I am very
concerned about performance. For example, there are lots of cases when
I may just be needing the employee name. Yet using this model, I will
have to instantiate an...
|
by: winsletmathew |
last post by:
I need the backup of only the structure of database, not the data.
I tried, this procedure , but it is copying the whole database
create Procedure BackupAlldatabases
as
|
by: rakesh19 |
last post by:
Hi,
I am building an application similar to google autosuggest. I want to fetch the list of items to be suggested using PHP. Those items are stored in a database. Is it a good idea to make a database connection everytime or store them in a batch in some kind of PHP data structure. Latter seems more viable. What kind of data structure in PHP is best suited. Is it arrays or some kind of storables. I am new to PHP. Also, the no. of items will...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |