tdsimpso
asked on
How can Oracle's DBMS_XMLDOM package be use to create an XML document?
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
Thanks,
Troy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.getDocumentEle ment( 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('Inde x Size error');
WHEN xmldom.DOMSTRING_SIZE_ERR THEN
-- raise_application_error(-- 20120, 'String Size error');
DBMS_OUTPUT.PUT_LINE('Stri ng Size error');
WHEN xmldom.HIERARCHY_REQUEST_E RR THEN
-- raise_application_error(-- 20120, 'Hierarchy request error');
DBMS_OUTPUT.PUT_LINE('Hier archy request error');
WHEN xmldom.WRONG_DOCUMENT_ERR THEN
-- raise_application_error(-- 20120, 'Wrong doc error');
DBMS_OUTPUT.PUT_LINE('Wron g doc error');
WHEN xmldom.INVALID_CHARACTER_E RR THEN
-- raise_application_error(-- 20120, 'Invalid Char error');
DBMS_OUTPUT.PUT_LINE('Inva lid 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_ALL OWED_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</descr iption_id>
<anatomical_notes>anatomic al notes & works</anatomical_notes>
</description>
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(
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(
n := dbms_xmldom.makeNode( e );
n := dbms_xmldom.appendChild( n_directory, n );
t := dbms_xmldom.createTextNode
n := dbms_xmldom.appendChild( n, dbms_xmldom.makeNode( t ) );
--
-- Create anatomical_notes element
e := dbms_xmldom.createElement(
n := dbms_xmldom.makeNode( e );
n := dbms_xmldom.appendChild( n_directory, n );
t := dbms_xmldom.createTextNode
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.getDocumentEle
--n := dbms_xmldom.makenode( e );
--DBMS_OUTPUT.PUT_LINE( dbms_xmldom.ELEMENT_NODE );
dbms_xmldom.writetobuffer(
--DBMS_OUTPUT.PUT_LINE( dbms_xmldom.getDoctype( d ) );
DBMS_OUTPUT.PUT_LINE(buf);
EXCEPTION
--
WHEN xmldom.INDEX_SIZE_ERR THEN
-- raise_application_error(--
DBMS_OUTPUT.PUT_LINE('Inde
WHEN xmldom.DOMSTRING_SIZE_ERR THEN
-- raise_application_error(--
DBMS_OUTPUT.PUT_LINE('Stri
WHEN xmldom.HIERARCHY_REQUEST_E
-- raise_application_error(--
DBMS_OUTPUT.PUT_LINE('Hier
WHEN xmldom.WRONG_DOCUMENT_ERR THEN
-- raise_application_error(--
DBMS_OUTPUT.PUT_LINE('Wron
WHEN xmldom.INVALID_CHARACTER_E
-- raise_application_error(--
DBMS_OUTPUT.PUT_LINE('Inva
WHEN xmldom.NO_DATA_ALLOWED_ERR
-- raise_application_error(--
DBMS_OUTPUT.PUT_LINE('No data allowed error');
WHEN xmldom.NO_MODIFICATION_ALL
-- raise_application_error(--
DBMS_OUTPUT.PUT_LINE('No mode allowed error');
WHEN xmldom.NOT_FOUND_ERR THEN
-- raise_application_error(--
DBMS_OUTPUT.PUT_LINE('Not found error');
WHEN xmldom.NOT_SUPPORTED_ERR THEN
-- raise_application_error(--
DBMS_OUTPUT.PUT_LINE('Not supported error');
WHEN xmldom.INUSE_ATTRIBUTE_ERR
-- raise_application_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</descr
<anatomical_notes>anatomic
</description>
ASKER
One problem though, my output is missing the following header:
<?xml version = '1.0' encoding = 'UTF-8'?>
How do I get that in?
<?xml version = '1.0' encoding = 'UTF-8'?>
How do I get that in?
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)
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)
ASKER
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
LOL
;-)
Thanks,
Troy
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......
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......
ASKER
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 & Jill < or > Me</description>
<description_id>690</descr iption_id>
WhooooAAAAaaaa!
Thanks for all your help.
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 & Jill < or > Me</description>
<description_id>690</descr
WhooooAAAAaaaa!
Thanks for all your help.
ASKER
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(
--
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.getDocumentEle
--n := dbms_xmldom.makenode( e );
--DBMS_OUTPUT.PUT_LINE( dbms_xmldom.getNodeName( n ) );
dbms_xmldom.writetobuffer(
--DBMS_OUTPUT.PUT_LINE( dbms_xmldom.getDoctype( d ) );
DBMS_OUTPUT.PUT_LINE(buf);
EXCEPTION
--
WHEN xmldom.INDEX_SIZE_ERR THEN
-- raise_application_error(--
DBMS_OUTPUT.PUT_LINE('Inde
WHEN xmldom.DOMSTRING_SIZE_ERR THEN
-- raise_application_error(--
DBMS_OUTPUT.PUT_LINE('Stri
WHEN xmldom.HIERARCHY_REQUEST_E
-- raise_application_error(--
DBMS_OUTPUT.PUT_LINE('Hier
WHEN xmldom.WRONG_DOCUMENT_ERR THEN
-- raise_application_error(--
DBMS_OUTPUT.PUT_LINE('Wron
WHEN xmldom.INVALID_CHARACTER_E
-- raise_application_error(--
DBMS_OUTPUT.PUT_LINE('Inva
WHEN xmldom.NO_DATA_ALLOWED_ERR
-- raise_application_error(--
DBMS_OUTPUT.PUT_LINE('No data allowed error');
WHEN xmldom.NO_MODIFICATION_ALL
-- raise_application_error(--
DBMS_OUTPUT.PUT_LINE('No mode allowed error');
WHEN xmldom.NOT_FOUND_ERR THEN
-- raise_application_error(--
DBMS_OUTPUT.PUT_LINE('Not found error');
WHEN xmldom.NOT_SUPPORTED_ERR THEN
-- raise_application_error(--
DBMS_OUTPUT.PUT_LINE('Not supported error');
WHEN xmldom.INUSE_ATTRIBUTE_ERR
-- raise_application_error(--
DBMS_OUTPUT.PUT_LINE('In use attr error');
END;
/