Home » Developer & Programmer » JDeveloper, Java & XML » Process XML data passed in parameter (Oracle 10g Enterprise Edition Release 10.2.0.3.0 - 64bi; )
Process XML data passed in parameter [message #526019] Fri, 07 October 2011 12:16 Go to next message
louisg
Messages: 3
Registered: October 2011
Location: Louisiana, USA
Junior Member
I have a requirement to extract data from an XML formatted string that is passed as an IN parameter to a database stored procedure. I do not have a table to store the XML, nor an XML schema, nor a namespace, just the XML string. While new to XML, I do understand the basics. Using XMLTable I am able to parse out the simplest of XML data. My problem is this; attempting to extract data from nested XML nodes results in the error

'ORA-19279: XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence'.

Testing a solution example I found on this site by Barbara Boehmer, I still get the ORA-19279 error. I suspect it may be because I have version 10g while she has version 11g. Her solutions is from #msg_514154. Here is that code that gives me the error:

SCOTT@orcl_11gR2> SELECT x."deptno"
  2   FROM  (SELECT '<departments><deptno>1</deptno><deptno>2</deptno></departments>' col FROM dual) xml_data,
  3  	     XMLTABLE
  4  	      ('/departments/deptno'
  5  		PASSING XMLTYPE (xml_data.col)
  6  		COLUMNS
  7  		"deptno"  number  path '/deptno') x
  8  /

    deptno
----------
         1
         2

2 rows selected.

As I said, this example generates the ORA-19279 error. Can someone please guide me in processing XML data from an IN parameter? (I'm really getting desperate.)
Much thanks in advance,
Louis

[Updated on: Fri, 07 October 2011 12:21] by Moderator

Report message to a moderator

Re: Process XML data passed in parameter [message #526022 is a reply to message #526019] Fri, 07 October 2011 12:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It works in 10.2.0.4
SQL> SELECT x."deptno"
  2  FROM  (SELECT '<departments><deptno>1</deptno><deptno>2</deptno></departments>' col FROM dual) xml_data,
  3       XMLTABLE
  4        ('/departments/deptno'
  5   PASSING XMLTYPE (xml_data.col)
  6   COLUMNS
  7   "deptno"  number  path '/deptno') x
  8  /
    deptno
----------
         1
         2

2 rows selected.

SQL> 
SQL> @v

Version Oracle : 10.2.0.4.0

So either upgrade or try this other way:
SQL> with 
  2    data as (
  3      SELECT '<departments><deptno>1</deptno><deptno>2</deptno></departments>' col FROM dual
  4    )
  5  select to_number(extractvalue(value(x),'/deptno')) deptno
  6  from data,
  7       table(xmlsequence(extract(xmltype(col), '/departments/deptno'))) x
  8  /
    DEPTNO
----------
         1
         2

2 rows selected.

Regards
Michel

Report message to a moderator

Re: Process XML data passed in parameter [message #526366 is a reply to message #526022] Mon, 10 October 2011 13:46 Go to previous messageGo to next message
louisg
Messages: 3
Registered: October 2011
Location: Louisiana, USA
Junior Member
Thanks for the prompt response Michel, and both of your solutions worked! I can't explain why I was getting that error but when I copy/paste your XMLTABLE entry I did not get the error!?! (I'll research that more when I have time.) I prefer the XMLTABLE method over the other method as it seems easier to wrap in a loop.
Now, I am having problems taking it a step further with nested nodes. I have tried and tried to figure this out but can't get it (feeling really dumb here).
The closest I can get is the code below, but it generates the error: ORA-19279: XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

SELECT x."deptno", x."emp"
FROM (SELECT '<departments><department><deptno>1</deptno><emps><emp>Joe</emp><emp>Mary</emp></emps></department><department><deptno >2</deptno><emps><emp>Fred</emp><emp>Lucy</emp></emps></department></departments>' col FROM dual) xml_data,
XMLTABLE
('/departments/department'
PASSING XMLTYPE (xml_data.col)
COLUMNS
"deptno" number path '/department/deptno',
"emp" varchar2(100) path '/department/emps/emp'
) x
/

(I apologize for the lack of line numbers, but TOAD won't let me copy the line numbers.)

Thanks for your time,
Louis

Report message to a moderator

Re: Process XML data passed in parameter [message #526378 is a reply to message #526366] Mon, 10 October 2011 14:53 Go to previous messageGo to next message
louisg
Messages: 3
Registered: October 2011
Location: Louisiana, USA
Junior Member
Oh wait! A slight mod to the "emp" path got me closer.

SELECT x."deptno" , x."emp"
FROM (SELECT '<departments><department><deptno>1</deptno><emps><emp>Joe</emp><emp>Mary</emp></emps></department><department><deptno >2</deptno><emps><emp>Fred</emp><emp>Lucy</emp></emps></department></departments>' col FROM dual) xml_data,
XMLTABLE
('/departments/department'
PASSING XMLTYPE (xml_data.col)
COLUMNS
"deptno" number path '/department/deptno,'
"emp" varchar2(100) path '/department/emps'
) x
/

results:
deptno emp
------ ---
1 JoeMary
2 FredLucy

But, as you can see the emp names are returned into a single non-delimited string. My hope is to process the XML in a loop to extract the data to be inserted into multiple tables. Is there a better way?

Thanks yet again,
Louis

Report message to a moderator

Re: Process XML data passed in parameter [message #526393 is a reply to message #526378] Mon, 10 October 2011 18:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
Senior Member
 
SCOTT@orcl_11gR2> COLUMN emp FORMAT A30
SCOTT@orcl_11gR2> WITH
  2    data AS
  3  	    (SELECT '<departments>
  4  		       <department>
  5  			 <deptno>1</deptno>
  6  			 <emps>
  7  			   <emp>Joe</emp>
  8  			   <emp>Mary</emp>
  9  			 </emps>
 10  		       </department>
 11  		       <department>
 12  			 <deptno >2</deptno>
 13  			 <emps>
 14  			   <emp>Fred</emp>
 15  			   <emp>Lucy</emp>
 16  			 </emps>
 17  		       </department>
 18  		     </departments>' col
 19  	     FROM    dual)
 20  SELECT TO_NUMBER
 21  	      (EXTRACTVALUE
 22  		(VALUE (x), '/department/deptno')) deptno,
 23  	      EXTRACTVALUE
 24  		(VALUE (t), '/emp') emp
 25  FROM   data,
 26  	    TABLE
 27  	      (XMLSEQUENCE
 28  		(EXTRACT
 29  		  (XMLTYPE (col),
 30  		    '/departments/department'))) x,
 31  	    TABLE
 32  	      (XMLSEQUENCE
 33  		(EXTRACT
 34  		  (EXTRACT (VALUE (x), '/department/emps'),
 35  		    '/emps/emp'))) t
 36  /

    DEPTNO EMP
---------- ------------------------------
         1 Joe
         1 Mary
         2 Fred
         2 Lucy

4 rows selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: Process XML data passed in parameter [message #526396 is a reply to message #526393] Mon, 10 October 2011 19:26 Go to previous message
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
Senior Member
Or, if you prefer xmltable:

SCOTT@orcl_11gR2> COLUMN emp FORMAT A30
SCOTT@orcl_11gR2> WITH
  2    data AS
  3  	    (SELECT '<departments>
  4  		       <department>
  5  			 <deptno>1</deptno>
  6  			 <emps>
  7  			   <emp>Joe</emp>
  8  			   <emp>Mary</emp>
  9  			 </emps>
 10  		       </department>
 11  		       <department>
 12  			 <deptno >2</deptno>
 13  			 <emps>
 14  			   <emp>Fred</emp>
 15  			   <emp>Lucy</emp>
 16  			 </emps>
 17  		       </department>
 18  		     </departments>' col
 19  	     FROM    dual)
 20  SELECT x."deptno", t."emp"
 21  FROM   data,
 22  	    XMLTABLE
 23  	      ('/departments/department'
 24  	       PASSING XMLTYPE (data.col)
 25  	       COLUMNS
 26  		 "deptno" number       path '/department/deptno',
 27  		 "emps"   xmltype) x,
 28  	    XMLTABLE
 29  	      ('/emps/emp'
 30  	       PASSING x."emps"
 31  	       COLUMNS
 32  		 "emp"	  varchar2(30) path '.') t
 33  /

    deptno emp
---------- ------------------------------
         1 Joe
         1 Mary
         2 Fred
         2 Lucy

4 rows selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Previous Topic: Extract Data from Clob field using Regular expression
Next Topic: call a Java function with a (array of) complex data type
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 18 18:55:57 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.