| query a column containing xml formatted data [message #513340] |
Sun, 26 June 2011 22:32  |
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 #513352 is a reply to message #513345] |
Sun, 26 June 2011 23:55  |
 |
Barbara Boehmer
Messages: 9103
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 #514151 is a reply to message #513419] |
Fri, 01 July 2011 21:02 |
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 #514153 is a reply to message #514152] |
Fri, 01 July 2011 21:21 |
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 |
|
Barbara Boehmer
Messages: 9103
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 |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
.