Home » Developer & Programmer » JDeveloper, Java & XML » query a column containing xml formatted data (11g)
query a column containing xml formatted data [message #513340] Sun, 26 June 2011 22:32 Go to next message
apollo
Messages: 20
Registered: September 2007
Junior Member
I understand how to use dbms_xmlgen to take the results of a query and convert it into a XML formatted data. But if a column (clob) contains data in an XML format, is there a way to query that column and easily parse the data? I know that I could write loops that do string searches for each of the XML tags and nested tags, but I didn't know if there was some built-in functionality for easily doing this. Thanks
Re: query a column containing xml formatted data [message #513345 is a reply to message #513340] Sun, 26 June 2011 22:57 Go to previous messageGo to next message
apollo
Messages: 20
Registered: September 2007
Junior Member
nevermind. When I wrote that I didn't even know there was an xmltype data type. I will read up on this.
Re: query a column containing xml formatted data [message #513352 is a reply to message #513345] Sun, 26 June 2011 23:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7995
Registered: November 2002
Location: California, USA
Senior Member
I have provided a simple example below to get your started, including a method prior to 11g and the new 11g method.

-- test table and data:
SCOTT@orcl_11gR2> desc dept
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                             NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

SCOTT@orcl_11gR2> select * from dept
  2  /

DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON

4 rows selected.


-- insert data into clob column of table in xml format:
SCOTT@orcl_11gR2> create table test_tab
  2    (test_col  clob)
  3  /

Table created.

SCOTT@orcl_11gR2> insert into test_tab (test_col)
  2  select dbms_xmlgen.getxml ('select * from dept')
  3  from   dual
  4  /

1 row created.

SCOTT@orcl_11gR2> select * from test_tab
  2  /

TEST_COL
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DEPTNO>10</DEPTNO>
  <DNAME>ACCOUNTING</DNAME>
  <LOC>NEW YORK</LOC>
 </ROW>
 <ROW>
  <DEPTNO>20</DEPTNO>
  <DNAME>RESEARCH</DNAME>
  <LOC>DALLAS</LOC>
 </ROW>
 <ROW>
  <DEPTNO>30</DEPTNO>
  <DNAME>SALES</DNAME>
  <LOC>CHICAGO</LOC>
 </ROW>
 <ROW>
  <DEPTNO>40</DEPTNO>
  <DNAME>OPERATIONS</DNAME>
  <LOC>BOSTON</LOC>
 </ROW>
</ROWSET>


1 row selected.


-- select from xml data in clob column prior to 11g:
SCOTT@orcl_11gR2> column deptno format 9999
SCOTT@orcl_11gR2> column dname	format a14
SCOTT@orcl_11gR2> column loc	format a13
SCOTT@orcl_11gR2> select to_number (extractvalue (x.column_value, '/ROW/DEPTNO')) deptno,
  2  	    extractvalue (x.column_value, '/ROW/DNAME') dname,
  3  	    extractvalue (x.column_value, '/ROW/LOC') loc
  4  from   test_tab t,
  5  	    table (xmlsequence (extract (xmltype (t.test_col), '/ROWSET/ROW'))) x
  6  /

DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON

4 rows selected.

SCOTT@orcl_11gR2> clear  columns


-- select from xml data in clob column in 11g:
SCOTT@orcl_11gR2> select x.deptno, x.dname, x.loc
  2  from   test_tab t,
  3  	    xmltable
  4  	      ('/ROWSET/ROW'
  5  	       passing xmltype (t.test_col)
  6  	       columns
  7  		 "DEPTNO"  number	 path '/ROW/DEPTNO',
  8  		 "DNAME"   varchar2 (14) path '/ROW/DNAME',
  9  		 "LOC"	   varchar2 (13) path '/ROW/LOC') x
 10  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.

SCOTT@orcl_11gR2>

Re: query a column containing xml formatted data [message #513419 is a reply to message #513352] Mon, 27 June 2011 07:38 Go to previous messageGo to next message
apollo
Messages: 20
Registered: September 2007
Junior Member
Thanks Barbara!
Re: query a column containing xml formatted data [message #514151 is a reply to message #513419] Fri, 01 July 2011 21:02 Go to previous messageGo to next message
apollo
Messages: 20
Registered: September 2007
Junior Member
When I query XML data where each element appears only once, it works fine. But when an element appears multiple times, as in your example above, I get the following error:

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

This was using your 11g example.
Re: query a column containing xml formatted data [message #514152 is a reply to message #514151] Fri, 01 July 2011 21:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7995
Registered: November 2002
Location: California, USA
Senior Member
Please provide a copy and paste of a run from SQL*Plus, of a reproducible test case, like I did, including sample data, line numbers, and errors.
Re: query a column containing xml formatted data [message #514153 is a reply to message #514152] Fri, 01 July 2011 21:21 Go to previous messageGo to next message
apollo
Messages: 20
Registered: September 2007
Junior Member
I am unable to log into SQL Plus at this very moment. If what I provide below is not enough, I can provide SQL Plus examples later.

--one instance of an element works fine.
SELECT x."deptno"
 FROM  (SELECT '<departments><deptno>1</deptno></departments>' col FROM dual) xml_data,
        XMLTABLE
         ('/departments'
           PASSING XMLTYPE (xml_data.col)
           COLUMNS
           "deptno"  number  path '/departments/deptno') x;

deptno
------
     1


--multiple instances of an element results in error.
SELECT x."deptno"
 FROM  (SELECT '<departments><deptno>1</deptno><deptno>2</deptno></departments>' col FROM dual) xml_data,
        XMLTABLE
         ('/departments'
           PASSING XMLTYPE (xml_data.col)
           COLUMNS
           "deptno"  number  path '/departments/deptno') x;

ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
Re: query a column containing xml formatted data [message #514154 is a reply to message #514153] Fri, 01 July 2011 21:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7995
Registered: November 2002
Location: California, USA
Senior Member
It depends on how you want it displayed. If the following doesn't help, then please provide another example that includes what results you want.

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.

SCOTT@orcl_11gR2>


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

   deptno1    deptno2
---------- ----------
         1          2

1 row selected.

SCOTT@orcl_11gR2>

[Updated on: Fri, 01 July 2011 21:44]

Report message to a moderator

Re: query a column containing xml formatted data [message #514755 is a reply to message #514154] Wed, 06 July 2011 05:35 Go to previous messageGo to next message
pointers
Messages: 350
Registered: May 2008
Senior Member
Lots of XML stuff. Thanks Barbara.

Can you please provide any link which gives a flow of understanding of XML stuff in oracle.
I searched in tahiti.oracle.com and found this

Not sure that really helps but lot of stuff to read in it Shocked .

Regards,
Pointers
Re: query a column containing xml formatted data [message #514816 is a reply to message #514755] Wed, 06 July 2011 12:20 Go to previous message
Barbara Boehmer
Messages: 7995
Registered: November 2002
Location: California, USA
Senior Member
There is an overwhelming amount of scattered information. In addition to the xmldb stuff, there are various xml functions that can be found in the SQL Command Reference and the PL/SQL Packages and Types Reference. The following website has some nice simple summaries with examples.

http://psoug.org/reference/xml_functions.html
http://psoug.org/reference/dbms_xmlgen.html
http://psoug.org/reference/xmlquery.html
http://psoug.org/reference/xmltable.html
Previous Topic: PLS-00201 errot was displayed when i call a pl/sql function from java program
Next Topic: java.sql.SQLException: executeBatch, Exception = 1
Goto Forum:
  


Current Time: Wed Oct 22 16:59:28 CDT 2014

Total time taken to generate the page: 0.08886 seconds