Home » Developer & Programmer » JDeveloper, Java & XML » Accessing Xml elements (oracle 10g)
Accessing Xml elements [message #357063] Mon, 03 November 2008 13:56 Go to next message
kanis
Messages: 61
Registered: November 2006
Member
I am using oracle 10.2.0.1.
In my database XML files are stored as blob .

My req :
I will input a XPATH . It should give me all the values present in that xpath.


MY Code :
------------
create or replace function r_child
(
IN_ID IN T_data.data_id%TYPE,
IN_XPATH IN VARCHAR2,
RETVAL OUT NUMBER,
ERR_MSG OUT VARCHAR2,
)
RETURN VARCHAR2
AS
L_CLOB CLOB;
L_PARSER DBMS_XMLPARSER.PARSER;
L_DOC DBMS_XMLDOM.DOMDOCUMENT;
L_NL DBMS_XMLDOM.DOMNODELIST;
L_N DBMS_XMLDOM.DOMNODE;
V_STATEMENT VARCHAR2(1000);
ERRMSG VARCHAR2(1000);
NNM XMLDOM.DOMNAMEDNODEMAP;
ATTRNAME VARCHAR2(100);
ATTRVAL VARCHAR2(100);
LEN2 NUMBER;
N DBMS_XMLDOM.DOMNODE;
len number;
Nodename varchar2(100);
BEGIN

IF IN_XPATH IS NULL THEN
RETVAL :=1;
ERR_MSG :='No XPATH Defined ';
RETURN 'NO DATA FOUND';
END IF;

--B2CLOB function change blob to clob
SELECT B2CLOB(DSR_XML_FILE) INTO L_CLOB FROM T_DATA where data_id = in_id;

V_STATEMENT :='CREATE A PARSER';
L_PARSER := DBMS_XMLPARSER.NEWPARSER;

V_STATEMENT :='PARSE THE DOCUMENT AND CREATE A NEW DOM DOCUMENT';
DBMS_XMLPARSER.PARSECLOB(L_PARSER, L_CLOB);
L_DOC := DBMS_XMLPARSER.GETDOCUMENT(L_PARSER);


-- GET A LIST OF ALL THE NODES IN THE DOCUMENT USING THE XPATH SYNTAX.
L_NL := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.MAKENODE(L_DOC),IN_XPATH);

len :=XMLDOM.GETLENGTH(L_NL);

for i in 0..len-1
loop
L_N := DBMS_XMLDOM.ITEM(L_NL, i);

Nodename :=dbms_xmldom.getNodeName(L_N);

Nodeval := dbms_xmldom.getNodeValue(L_N);


DBMS_OUTPUT.PUT_LINE('GETNODE NAME ' || Nodename || ' = ' || Nodename);
DBMS_OUTPUT.PUT_LINE('GETNODE NAME ' || Nodeval || ' = ' || Nodeval);
end loop;



-- FREE ANY RESOURCES ASSOCIATED WITH THE DOCUMENT NOW IT
-- IS NO LONGER NEEDED.
DBMS_XMLDOM.FREEDOCUMENT(L_DOC);

EXCEPTION WHEN OTHERS
THEN
BEGIN
RETVAL :=1;
ERR_MSG := V_STATEMENT || SQLCODE || ': ' || SQLERRM;
ERRMSG := 'IN_ID=' || IN_ID
||'IN_XPATH=' || IN_XPATH;
ERRMSG := PREPARE_LOG ('GETVALUE_FROMXPATH', ERRMSG, V_STATEMENT, SQLCODE, SQLERRM);
WRITE_LOG(ERRMSG);
RETURN ERRMSG;
DBMS_LOB.FREETEMPORARY(L_CLOB);
DBMS_XMLPARSER.FREEPARSER(L_PARSER);
DBMS_XMLDOM.FREEDOCUMENT(L_DOC);
END;
END;
------------------------------------


My XMl File Like :

<?xml version="1.0" encoding="UTF-8" ?>
<a>
<b>
<p>10</p>
<d>apple</d>
</b>
<b>
<p>100</p>
<d>orange</d>
</b>
<b>
<p>110</p>
<d>mango</d>
</b>
</a>

or can be like

<?xml version="1.0" encoding="UTF-8" ?>
<fruits>
<fruit>
<detail>
<cost>10</cost>
</detail>
<name>apple</name>
</fruit>
<fruit>
<detail>
<cost>10</cost>
</detail>
<name>orange</name>
</fruit>
<fruit>
<detail>
<cost>10</cost>
</detail>
<name>mango</name>
</fruit>
</fruits>

in put xpath for
xml 1. \\a\b\d
the out using the above code is
GETNODE NAME = d
GETNODE NAME =
GETNODE NAME = d
GETNODE NAME =
GETNODE NAME = d
GETNODE NAME =

xml 2. \\fruits\fruit\detail\cost


GETNODE NAME = cost
GETNODE NAME =
GETNODE NAME = cost
GETNODE NAME =
GETNODE NAME = cost
GETNODE NAME =



No Node value ... can ay one let me know where the problem is ?
and How to fix it ?
Re: Accessing Xml elements [message #357067 is a reply to message #357063] Mon, 03 November 2008 14:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Accessing Xml elements [message #357071 is a reply to message #357067] Mon, 03 November 2008 15:02 Go to previous message
kanis
Messages: 61
Registered: November 2006
Member
Ok .. Sorry .. to post like this ..
I am stuck with this problem .. badly .. very strict deadline ..
Please help ..
Previous Topic: Error while running a OA framework Page
Next Topic: error: java.lang.IndexOutOfBoundsException: SelectOne submittedValue's index 14 is out of bounds. It
Goto Forum:
  


Current Time: Thu Sep 18 07:23:00 CDT 2014

Total time taken to generate the page: 0.05046 seconds