Home » SQL & PL/SQL » SQL & PL/SQL » Reading XML help
Reading XML help [message #188727] Mon, 21 August 2006 05:46 Go to next message
cacheboy
Messages: 21
Registered: August 2006
Junior Member
My XML

<Request>
<e1>
<e1a>x</e1a>
<e1b>y</e1b>
<e1c>z</e1c>
</e2>
<e2>ldsflksdjklnmnmxcnvxcvxcvskljasdasdasdsa adasdasd</e2>
</Request>

My procedure

PROCEDURE displayXMLvalues(insert_xml IN XMLType)
IS
l_xml XMLTYPE := insert_xml;
e1 varchar2(800);
e2 varchar2(800);
e1a varchar2(800);
e1b varchar2(800);
e1c varchar2(800);
err_num NUMBER;
err_msg VARCHAR2(100);
BEGIN
FOR x IN ( SELECT EXTRACTVALUE(VALUE(t), '/Request/e1') e1,
EXTRACTVALUE(VALUE(t), '/Request/e2') e2,
EXTRACTVALUE(VALUE(tb), '/e1/e1a') e1a,
EXTRACTVALUE(VALUE(tb), '/e1/e1b') e1b,
EXTRACTVALUE(VALUE(tb), '/e1/e1c') e1c
FROM TABLE(XMLSEQUENCE(EXTRACT(l_xml, '/Request'))) t,
TABLE(XMLSEQUENCE(EXTRACT(VALUE(t), 'Request/e1'))) tb
)
LOOP
htp.p(x.e1 || ',' || x.e2 || ',' || x.e1a || ',' || x.e1b || ',' || x.e1c);
END LOOP;
htp.p('DONE!');

EXCEPTION -- exception handlers begin
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
htp.p('Err: ' || err_msg);
END;

My runtime error

ORA-19025: EXTRACTVALUE returns value of only one node

What went wrong? Would it mean that I have to change the extractvalue function to extract?
Re: Reading XML help [message #188756 is a reply to message #188727] Mon, 21 August 2006 09:00 Go to previous message
hobbes
Messages: 173
Registered: January 2006
Senior Member
As the message indicates, EXTRACTVALUE can work on a single node value only. EXTRACTVALUE(VALUE(t), '/Request/e1') leads to an error as the tag <e1> has an XML fragment within it.
Quote:

Would it mean that I have to change the extractvalue function to extract?

Depends on what you want as the output. Do you want the values of the XML leaf nodes {e1a, e1b, e1c, e2} only, or also the XML fragments within <e1> and <e2>?

If the node values only, restructure the SELECT to:
   SELECT EXTRACTVALUE(VALUE(t), '/Request/e1/e1a') e1a,
          EXTRACTVALUE(VALUE(t), '/Request/e1/e1b') e1b,
          ...
          EXTRACTVALUE(VALUE(t), '/Request/e2') e2
          FROM TABLE(XMLSEQUENCE(EXTRACT(l_xml, '/Request'))) t

If you want XML fragments, use EXTRACT:
   SELECT EXTRACT(VALUE(t), '/Request/e1') e1,
          EXTRACT(VALUE(t), '/Request/e2') e2,
          EXTRACTVALUE(VALUE(t), '/Request/e1/e1a') e1a
          ...
          FROM TABLE(XMLSEQUENCE(EXTRACT(l_xml, '/Request'))) t

A couple of suggestions:
- You can do with a single TABLE expression; the join is redundant.
- You don't need the variable declarations e1, e2 etc. - they are implicitly defined because of the FOR loop.
Previous Topic: Image Pblm
Next Topic: Calling procedures
Goto Forum:
  


Current Time: Fri Dec 09 00:25:29 CST 2016

Total time taken to generate the page: 0.07942 seconds