Solved

How can Oracle's DBMS_XMLDOM package be use to create an XML document?

Posted on 2004-04-28
9
13,568 Views
Last Modified: 2009-07-29
How can Oracle's DBMS_XMLDOM package be use to create an XML document?  I am just learning about using XML with Oracle so simple examples would be helpful.

Thanks,
Troy
0
Comment
Question by:tdsimpso
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 470 total points
ID: 10941976
See if this will help get you started (I picked up this example somewhere):


declare
 -- xmlparser
 p xmlparser.parser;
 -- Document
 doc xmldom.DOMDocument;
 -- Elements
 el xmldom.DOMElement;
 -- Nodes
 buffyNode xmldom.DOMNode;
 docNode xmldom.DOMNode;
 vampire_node xmldom.DOMNode;
 demon_node xmldom.DOMNode;
 enemy_node xmldom.DOMNode;
 friend_node xmldom.DOMNode;
 node xmldom.DOMNode;
 --Attributes
 attr xmldom.DOMAttr;
 -- Varchar2
 temp1 varchar2(10000);
 temp2 varchar2(255);

 BEGIN
 p := xmlparser.newParser;
 xmlparser.parseBuffer(p,'<BuffyEnemies/>');
 doc := xmlparser.getDocument(p);
 xmldom.setVersion(doc, '1.0');
 docNode := xmldom.makeNode(doc);
 buffyNode := xmldom.getLastChild(docNode); --The last child is the quickbuy node
 -- ok

 el := xmldom.createElement(doc, 'Friend');
 friend_node := xmldom.appendChild(buffyNode, xmldom.makeNode(el));
 el := xmldom.createElement(doc, 'Enemy');
 enemy_node := xmldom.appendChild(buffyNode, xmldom.makeNode(el));


 el := xmldom.createElement(doc, 'Vampire');
 xmldom.setAttribute(el, 'name','Spike');
 xmldom.setAttribute(el, 'danger','zero');
 vampire_node := xmldom.appendChild(friend_node, xmldom.makeNode(el));

 el := xmldom.createElement(doc, 'Vampire');
 xmldom.setAttribute(el, 'name','Harmony Kendal');
 xmldom.setAttribute(el, 'danger','high');
 vampire_node := xmldom.appendChild(enemy_node, xmldom.makeNode(el));

 el := xmldom.createElement(doc, 'Demon');
 xmldom.setAttribute(el, 'name','Ovu Mobani');
 xmldom.setAttribute(el, 'danger','high');
 demon_node := xmldom.appendChild(enemy_node, xmldom.makeNode(el));

 el := xmldom.createElement(doc, 'Human');
 xmldom.setAttribute(el, 'name','Lunch Lady');
 xmldom.setAttribute(el, 'danger','extreme');
 demon_node := xmldom.appendChild(enemy_node, xmldom.makeNode(el));


 xmldom.writeToBuffer(doc, temp1);
 temp2 := substr(temp1,1,250);
 DBMS_OUTPUT.PUT_LINE(temp2);

 temp2 := substr(temp1,251,250);
 DBMS_OUTPUT.PUT_LINE(temp2);


 -- deal with exceptions
 exception

 when xmldom.INDEX_SIZE_ERR then
 -- raise_application_error(--20120, 'Index Size error');
 DBMS_OUTPUT.PUT_LINE('Index Size error');
 when xmldom.DOMSTRING_SIZE_ERR then
 -- raise_application_error(--20120, 'String Size error');
 DBMS_OUTPUT.PUT_LINE('String Size error');
 when xmldom.HIERARCHY_REQUEST_ERR then
 -- raise_application_error(--20120, 'Hierarchy request error');
 DBMS_OUTPUT.PUT_LINE('Hierarchy request error');
 when xmldom.WRONG_DOCUMENT_ERR then
 -- raise_application_error(--20120, 'Wrong doc error');
 DBMS_OUTPUT.PUT_LINE('Wrong doc error');
 when xmldom.INVALID_CHARACTER_ERR then
 -- raise_application_error(--20120, 'Invalid Char error');
 DBMS_OUTPUT.PUT_LINE('Invalid char error');
 when xmldom.NO_DATA_ALLOWED_ERR then
 -- raise_application_error(--20120, 'Nod data allowed error');
 DBMS_OUTPUT.PUT_LINE('No data allowed error');
 when xmldom.NO_MODIFICATION_ALLOWED_ERR then
 -- raise_application_error(--20120, 'No mod allowed error');
 DBMS_OUTPUT.PUT_LINE('No mode allowed error');
 when xmldom.NOT_FOUND_ERR then
 -- raise_application_error(--20120, 'Not found error');
 DBMS_OUTPUT.PUT_LINE('Not found error');
 when xmldom.NOT_SUPPORTED_ERR then
 -- raise_application_error(--20120, 'Not supported error');
 DBMS_OUTPUT.PUT_LINE('Not supported error');
 when xmldom.INUSE_ATTRIBUTE_ERR then
 -- raise_application_error(--20120, 'In use attr error');
 DBMS_OUTPUT.PUT_LINE('In use attr error');
 END;
 /
0
 
LVL 23

Assisted Solution

by:seazodiac
seazodiac earned 30 total points
ID: 10942295
also take a look at this classic example from asktom:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:17309127931717
0
 

Author Comment

by:tdsimpso
ID: 10943615
I have having a problem setting a Node value.  Here is my code:

DECLARE
  d      dbms_xmldom.DOMDocument;
  d_node dbms_xmldom.DOMNode;
  e      dbms_xmldom.DOMElement;
  n      dbms_xmldom.DOMNode;
  n_description dbms_xmldom.DOMNode;
  dom_text dbms_xmldom.DOMText;
  buf VARCHAR2(2000);
BEGIN
  -- New Document
  d := dbms_xmldom.newDOMDocument;
  dbms_xmldom.setVersion( d, '1.0' );
  d_node := dbms_xmldom.makeNode( d );
  -- Create Element node.
  e := dbms_xmldom.createElement( d, 'description' );
  --
  n := dbms_xmldom.makeNode( e );
  n_description := dbms_xmldom.appendChild( d_node, n );
 --
  -- The Next Line is what is causeing the ERROR.....
  dbms_xmldom.setNodeValue( n, 'descriptionValue' );
  --
  -- make node.
  --DBMS_OUTPUT.PUT_LINE( dbms_xmldom.getNodeName( n ) );
  --DBMS_OUTPUT.PUT_LINE( dbms_xmldom.getNodeValue( n ) );
  --DBMS_OUTPUT.PUT_LINE( dbms_xmldom.getNodeType( n ) );
  --e := dbms_xmldom.getDocumentElement( d );
  --n := dbms_xmldom.makenode( e );
  --DBMS_OUTPUT.PUT_LINE( dbms_xmldom.getNodeName( n ) );
  dbms_xmldom.writetobuffer( dbms_xmldom.makenode(d), buf );
  --DBMS_OUTPUT.PUT_LINE( dbms_xmldom.getDoctype( d ) );
  DBMS_OUTPUT.PUT_LINE(buf);
EXCEPTION
--
 WHEN xmldom.INDEX_SIZE_ERR THEN
 -- raise_application_error(--20120, 'Index Size error');
 DBMS_OUTPUT.PUT_LINE('Index Size error');
 WHEN xmldom.DOMSTRING_SIZE_ERR THEN
 -- raise_application_error(--20120, 'String Size error');
 DBMS_OUTPUT.PUT_LINE('String Size error');
 WHEN xmldom.HIERARCHY_REQUEST_ERR THEN
 -- raise_application_error(--20120, 'Hierarchy request error');
 DBMS_OUTPUT.PUT_LINE('Hierarchy request error');
 WHEN xmldom.WRONG_DOCUMENT_ERR THEN
 -- raise_application_error(--20120, 'Wrong doc error');
 DBMS_OUTPUT.PUT_LINE('Wrong doc error');
 WHEN xmldom.INVALID_CHARACTER_ERR THEN
 -- raise_application_error(--20120, 'Invalid Char error');
 DBMS_OUTPUT.PUT_LINE('Invalid char error');
 WHEN xmldom.NO_DATA_ALLOWED_ERR THEN
 -- raise_application_error(--20120, 'Nod data allowed error');
 DBMS_OUTPUT.PUT_LINE('No data allowed error');
 WHEN xmldom.NO_MODIFICATION_ALLOWED_ERR THEN
 -- raise_application_error(--20120, 'No mod allowed error');
 DBMS_OUTPUT.PUT_LINE('No mode allowed error');
 WHEN xmldom.NOT_FOUND_ERR THEN
 -- raise_application_error(--20120, 'Not found error');
 DBMS_OUTPUT.PUT_LINE('Not found error');
 WHEN xmldom.NOT_SUPPORTED_ERR THEN
 -- raise_application_error(--20120, 'Not supported error');
 DBMS_OUTPUT.PUT_LINE('Not supported error');
 WHEN xmldom.INUSE_ATTRIBUTE_ERR THEN
 -- raise_application_error(--20120, 'In use attr error');
 DBMS_OUTPUT.PUT_LINE('In use attr error');
END;
/
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:tdsimpso
ID: 10944219
I think I have it figured out.  I had to create an ElementNode and a TextNode.  I assigned he TextNode to the ElementNode and then assigne the Element Node to the Document.

Document - Element - TextNode.

Here is the code.
Not much resoucse on the Web to figure this one out.  I just stumbled upon it.
-------------------------------
Code Starts here.
---------------------------
DECLARE
  dom_doc     dbms_xmldom.DOMDocument;
  n_doc       dbms_xmldom.DOMNode;
  e           dbms_xmldom.DOMElement;
  n           dbms_xmldom.DOMNode;
  n_directory dbms_xmldom.DOMNode;
  n_temp      dbms_xmldom.DOMNode;
  t           dbms_xmldom.DOMText;
  buf VARCHAR2(2000);
BEGIN
  -- New Document
  dom_doc := dbms_xmldom.newDOMDocument;
  dbms_xmldom.setVersion( dom_doc, '1.0' );
  n_doc := dbms_xmldom.makeNode( dom_doc );
  --
  -- Create Root Element.
  e := dbms_xmldom.createElement( dom_doc, 'description' );
  dbms_xmldom.setAttribute( e, 'xmlns:xsi', 'http://www.w3.org/2001/XMLSchema-instance' );
  n := dbms_xmldom.makeNode( e );
  n_directory := dbms_xmldom.appendChild( n_doc, n );
  --
  -- Create description_id element.
  e := dbms_xmldom.createElement( dom_doc, 'description_id' );
  n := dbms_xmldom.makeNode( e );
  n := dbms_xmldom.appendChild( n_directory, n );
  t := dbms_xmldom.createTextNode( dom_doc, '456' );
  n := dbms_xmldom.appendChild( n, dbms_xmldom.makeNode( t ) );
  --
  -- Create anatomical_notes element
  e := dbms_xmldom.createElement( dom_doc, 'anatomical_notes' );
  n := dbms_xmldom.makeNode( e );
  n := dbms_xmldom.appendChild( n_directory, n );
  t := dbms_xmldom.createTextNode( dom_doc, 'anatomical notes & works' );
  n := dbms_xmldom.appendChild( n, dbms_xmldom.makeNode( t ) );
  --
  -- make node.
  --DBMS_OUTPUT.PUT_LINE( dbms_xmldom.getNodeName( n_d ) );
  --DBMS_OUTPUT.PUT_LINE( dbms_xmldom.getNodeValue( n_d ) );
  --DBMS_OUTPUT.PUT_LINE( dbms_xmldom.getNodeType( n_d ) );
  --e := dbms_xmldom.getDocumentElement( d );
  --n := dbms_xmldom.makenode( e );
  --DBMS_OUTPUT.PUT_LINE( dbms_xmldom.ELEMENT_NODE );
  dbms_xmldom.writetobuffer( n_doc, buf );
  --DBMS_OUTPUT.PUT_LINE( dbms_xmldom.getDoctype( d ) );
  DBMS_OUTPUT.PUT_LINE(buf);
EXCEPTION
--
 WHEN xmldom.INDEX_SIZE_ERR THEN
 -- raise_application_error(--20120, 'Index Size error');
 DBMS_OUTPUT.PUT_LINE('Index Size error');
 WHEN xmldom.DOMSTRING_SIZE_ERR THEN
 -- raise_application_error(--20120, 'String Size error');
 DBMS_OUTPUT.PUT_LINE('String Size error');
 WHEN xmldom.HIERARCHY_REQUEST_ERR THEN
 -- raise_application_error(--20120, 'Hierarchy request error');
 DBMS_OUTPUT.PUT_LINE('Hierarchy request error');
 WHEN xmldom.WRONG_DOCUMENT_ERR THEN
 -- raise_application_error(--20120, 'Wrong doc error');
 DBMS_OUTPUT.PUT_LINE('Wrong doc error');
 WHEN xmldom.INVALID_CHARACTER_ERR THEN
 -- raise_application_error(--20120, 'Invalid Char error');
 DBMS_OUTPUT.PUT_LINE('Invalid char error');
 WHEN xmldom.NO_DATA_ALLOWED_ERR THEN
 -- raise_application_error(--20120, 'Nod data allowed error');
 DBMS_OUTPUT.PUT_LINE('No data allowed error');
 WHEN xmldom.NO_MODIFICATION_ALLOWED_ERR THEN
 -- raise_application_error(--20120, 'No mod allowed error');
 DBMS_OUTPUT.PUT_LINE('No mode allowed error');
 WHEN xmldom.NOT_FOUND_ERR THEN
 -- raise_application_error(--20120, 'Not found error');
 DBMS_OUTPUT.PUT_LINE('Not found error');
 WHEN xmldom.NOT_SUPPORTED_ERR THEN
 -- raise_application_error(--20120, 'Not supported error');
 DBMS_OUTPUT.PUT_LINE('Not supported error');
 WHEN xmldom.INUSE_ATTRIBUTE_ERR THEN
 -- raise_application_error(--20120, 'In use attr error');
 DBMS_OUTPUT.PUT_LINE('In use attr error');
END;
/
-----------------------------
Results are here.
------------------------
<description xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <description_id>456</description_id>
  <anatomical_notes>anatomical notes &amp; works</anatomical_notes>
</description>
0
 

Author Comment

by:tdsimpso
ID: 10944619
One problem though, my output is missing the following header:

<?xml version = '1.0' encoding = 'UTF-8'?>

How do I get that in?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 10949061
The following 2 lines should get you what you need, with 1 comment to follow:
   xmldom.setVersion(doc, '1.0');
   xmldom.setCharset( doc, 'UTF-8' );

I've tried using dbms_xmldom in 9.2.0.4 and it doesn't show the <?xml... tag.  Try it with the older version: xmldom.  This worked in 9.2.0.4

I noticed this difference in our 2 samples.

FYI:
Neither work in 10.1.0.2 (Windows version)
0
 

Author Comment

by:tdsimpso
ID: 10949544
Can't Oracle be consistent.  In dbms_xmlgen, I can't turn the <?xml...> declaration off and with the dbms_xmldom, I can't turn the declaration on.  What gives?

 LOL  

;-)

Thanks,
Troy
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 10949884
lol.....

Believe me, I agree fully with you.  In my opinion, Larry Ellison wants to be first to market with EVERYTHING so, The developers throw something together to get to market and then take the time to redo it again and again and again.....

As with the 2 different DOM packages, guess what:  there are 2 query packages:
dbms_xmlGen (older) and dbms_xmlquery (replacement).  Maybe you can turn it off in xmlQuery......
0
 

Author Comment

by:tdsimpso
ID: 10950602
I believe I found just what I am looking for.  There are a ste of basic XML functions described in the Oracle9i SQL Reference Manual.  They all start like XML*.  For example, XMLAGG, XMLELEMENT, XMLFOREST, etc.  I can use these to query various tables, parse the returning data so that it is XML Friendly, then concat it all together.  

I feel like I ran around the block to visit my neight how live just next door.  LOL

Anyway I learned a lot about what I can do, which is a good thing.

DECLARE
  y XMLTYPE;
  x CLOB;
BEGIN
SELECT xmlelement( "description", 'Jack & Jill < or > Me' ) INTO y FROM dual;
DBMS_OUTPUT.PUT_LINE( y.getClobVal() );
SELECT XMLForest( descriptionID AS "description_id" ) INTO y FROM v_description WHERE descriptionID = 690;
DBMS_OUTPUT.PUT_LINE( y.getClobVal() );
END;

Here are the results:

<description>Jack &amp; Jill &lt; or &gt; Me</description>
<description_id>690</description_id>

WhooooAAAAaaaa!

Thanks for all your help.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Alternative to GTT for a temp table for further reuse in Oracle 8 61
Email query results in HTML 6 49
Checking for column width 8 49
oracle forms question 22 61
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

738 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