proc sql query to extract value out of xml data

[jakarman]

To pay attention for is datatypes DB/2 to SAS see: SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Third Edition .   does not  support BLOB, CLOB, DBCLOB, ROWID, and some kinds of distinct DB2 

[jakarman]

Kurt's proposal is one that will work. See the Clob/Lob definition by IBM. IBM Knowledge Center  . The 32Kb length limit is common for char-types, that is also the limit with SAS char-types.
The file-system on Z/OS was designed in 1970's. That is before we got common to use all those file types seen with Windows (Unix) pdf zip xml doc xlsx wav vid and much more.
IBM missed an implementation at z/OS fo those types, the only one coming near to that is the RECFM=u (used for binaries/loadables).
With DB/2 they implemented something that could also store data common to a OS file-system, the -lob objects.  There are more DBMS-systems doing this.

As SAS is not seeing that -Lob object as a OS-filesystem you should convert it by yourself.
In the special case of CLOB and not being longer as 32Kb you could do a conversion to the standard char-types. Whether that restriction applies is something to evaluate by you.    

[NitinDwivedi]

Jaap,  It is exactly the same question asked by ABCD123,not hijacked Also It is a ‘SAS interface to db2 variable problem, not a db2 problem as such.

[jakarman]

NitinDwivedim sorry if I was not friendly to you.

I started with the "proc sql query to extract value out of xml data" question. We ended with a DB2 design decision (LOB) objects that is not compatible with common RDBMS usage.

The incompatibility is no bodies fault. IBM with DB/2 is not guilty, and SAS going for common RDBMS data interface is not guilty. In this case a sFTP access method to the DB/2 to some SAS accessible location would be nice. That can be posted as enhancement.  

Getting into some trouble with this kind of stuff of incompatibilities is a fact of the IT world.  We have to deal with that.

[NitinDwivedi]

Thanks Jaap, Your help is  well appreciated. Yeah it seems incompatablities issue and nothing can be done on this, SAS doesn't support this XML data-type and now need to look around for other solution.