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
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| 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 | ||||
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 |