| Accessing Xml elements [message #357063] |
Mon, 03 November 2008 13:56  |
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 ?
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
