Usage Note 40002: Reading an Oracle column that is defined as XMLType data-type
First, you need to create an Oracle table with an XMLType column defined. Then insert the data. This code is Oracle specific code, and will need to be done using SQL*Plus.
CREATE TABLE XMLTest (
myKEY INTEGER
,myXML XMLType
);
INSERT INTO xmltest (mykey, myxml) values
(1, '<howami><disposition>Awesome</disposition></howami>');
COMMIT;
Now, try to read it with SAS.
LIBNAME myora ORACLE USER=userid PW=password PATH=path;
PROC PRINT DATA=myora.xmltest;
RUN;
SAS will give an error:
NOTE: The following 1 column(s) in MYORA.XMLTEST have been automatically
dropped because they have a datatype that is not supported by this engine:
MYXML
If your table has only XMLType columns, you might see an error message similar to this
ERROR: Error trying to read from a DBMS table. At least one column must
be selected.
To get around this you can create a view in Oracle and then read the view.
It's not pretty but it does work. Note: Below is Oracle specific syntax.
create view xmlview as (
select mykey, extractvalue(myxml, '/howami/disposition') myxml
from xmltest
);
Now you can read the view from SAS.
PROC PRINT DATA=myora.xmlview;
RUN;
Operating System and Release Information
| SAS System | SAS/ACCESS Interface to Oracle | Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.21_M2 | | 9.2 TS2M2 | |
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.21_M2 | | 9.2 TS2M2 | |
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.21_M2 | | 9.2 TS2M2 | |
| Microsoft Windows XP 64-bit Edition | 9.21_M2 | | 9.2 TS2M2 | |
| Microsoft® Windows® for x64 | 9.21_M2 | | 9.2 TS2M2 | |
| Microsoft Windows Server 2003 Datacenter Edition | 9.21_M2 | | 9.2 TS2M2 | |
| Microsoft Windows Server 2003 Enterprise Edition | 9.21_M2 | | 9.2 TS2M2 | |
| Microsoft Windows Server 2003 Standard Edition | 9.21_M2 | | 9.2 TS2M2 | |
| Microsoft Windows Server 2003 for x64 | 9.21_M2 | | 9.2 TS2M2 | |
| Microsoft Windows Server 2008 | 9.21_M2 | | 9.2 TS2M2 | |
| Microsoft Windows Server 2008 for x64 | 9.21_M2 | | 9.2 TS2M2 | |
| Microsoft Windows XP Professional | 9.21_M2 | | 9.2 TS2M2 | |
| Windows 7 Enterprise 32 bit | 9.21_M2 | | 9.2 TS2M2 | |
| Windows 7 Enterprise x64 | 9.21_M2 | | 9.2 TS2M2 | |
| Windows 7 Home Premium 32 bit | 9.21_M2 | | 9.2 TS2M2 | |
| Windows 7 Home Premium x64 | 9.21_M2 | | 9.2 TS2M2 | |
| Windows 7 Professional 32 bit | 9.21_M2 | | 9.2 TS2M2 | |
| Windows 7 Professional x64 | 9.21_M2 | | 9.2 TS2M2 | |
| Windows 7 Ultimate 32 bit | 9.21_M2 | | 9.2 TS2M2 | |
| Windows 7 Ultimate x64 | 9.21_M2 | | 9.2 TS2M2 | |
| Windows Vista | 9.21_M2 | | 9.2 TS2M2 | |
| Windows Vista for x64 | 9.21_M2 | | 9.2 TS2M2 | |
| 64-bit Enabled AIX | 9.21_M2 | | 9.2 TS2M2 | |
| 64-bit Enabled HP-UX | 9.21_M2 | | 9.2 TS2M2 | |
| 64-bit Enabled Solaris | 9.21_M2 | | 9.2 TS2M2 | |
| HP-UX IPF | 9.21_M2 | | 9.2 TS2M2 | |
| Linux | 9.21_M2 | | 9.2 TS2M2 | |
| Linux for x64 | 9.21_M2 | | 9.2 TS2M2 | |
| OpenVMS on HP Integrity | 9.21_M2 | | 9.2 TS2M2 | |
| Solaris for x64 | 9.21_M2 | | 9.2 TS2M2 | |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
To get around this you can create a view in Oracle and then read the view.
| Type: | Usage Note |
| Priority: | |
| Topic: | Data Management ==> Data Sources ==> External Databases ==> Oracle
|
| Date Modified: | 2010-12-16 17:05:59 |
| Date Created: | 2010-06-15 15:25:41 |