topic Re: proc sql query to extract value out of xml data in SAS Procedures

proc sql query to extract value out of xml data

abcd123 — Mon, 07 Apr 2014 20:40:52 GMT

Hello, all

I have a table (which is on a db2 database) with several columns. One of the column is actually xml data type. I need to extract some value out of this xml data.

I am trying to use proc sql to accomplish this job. However after I google online, it seems there is no such proc sql query for xml data.

My question is: how to extract value from xml data? is proc sql capable of this? or is there any other tool in sas?

Thanks.

Re: proc sql query to extract value out of xml data

Reeza — Mon, 07 Apr 2014 20:58:11 GMT

Use SQL pass through directly to use native DB2 SQL commands.

DB2 Database for Linux, UNIX, and Windows

Re: proc sql query to extract value out of xml data

abcd123 — Mon, 07 Apr 2014 22:20:19 GMT

Thank you very much.

I have never used pass through facility. I just googled on line and try to make pass through work, but couldn't (most likely my pass through grammar is not right). The following is a workable proc sql statement. Could you please help me to write a pass through version of this statement? Thank you very much again.

libname DB2_SALES db2 datasrc = vagerdb2 user = 'hxf' password = "A2b56" schema='SALES_INFO';

proc sql;

create table test as

select * from DB2_SALES.north_region;

quit;

Re: proc sql query to extract value out of xml data

abcd123 — Mon, 07 Apr 2014 23:18:10 GMT

Thanks. I have figured outthe correct format of pass through for db2 server.

Re: proc sql query to extract value out of xml data

NitinDwivedi — Mon, 14 Jul 2014 07:20:39 GMT

Hi ,

I am facing similar problem, Can you tell me what were the steps taken to achieve.

Please explain what eact term signify marked in bold

libname db2 datasrc = vagerdb2 user = 'hxf' password = "A2b56" schema='SALES_INFO';

Re: proc sql query to extract value out of xml data

RW9 — Mon, 14 Jul 2014 08:19:45 GMT

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001355231.htm

Re: proc sql query to extract value out of xml data

jakarman — Mon, 14 Jul 2014 09:34:29 GMT

I do not get it. a/ A database is a way to represent a relational table. b/ An XML file is a way to represent a relational table.

Is the question how to access a table that is stored in a table? That is not a normal approach for table access.

Re: proc sql query to extract value out of xml data

NitinDwivedi — Mon, 14 Jul 2014 09:54:03 GMT

Hi ,

My PROC SQL is working fine for other columns but while trying to fetch column containing XML data, getting following error.

SQL is executing in Z/OS environment.

DB2 ERROR:

RESULT OF SQL STATEMENT:

DSN00804E AN ERROR WAS FOUND IN THE APPLICATION PROGRAM INPUT PARAMETERS FOR THE SQL STATEMENT, REASON 16

A DUMP OF THE SQLCA FOR THE GET DIAGNOSTICS REQUEST FOLLOWS:

ROW NUMBER: 0 ERROR CONDITION: 1 REASON CODE: 16

SQLCODE: -804 SQLSTATE: 07002 SQLERRP: DSNXROHB

SQL query is as follows :

PROC SQL NOPRINT;

CONNECT TO DB2 (SSID=DB0T);

CREATE TABLE FBLM AS

SELECT * FROM CONNECTION TO DB2(

SELECT

CREDIT_ASSESSMENT_SUBMISSION as CAS

FROM SYSTEMF.CREDIT_CASE

WHERE CREDIT_CASE_ID = '001234bc-4e38-4dee-ad5d-b0e2d91457e8' );

QUIT;

Can one guide me that why there is an issue and how it can be resolved.

Re: proc sql query to extract value out of xml data

RW9 — Mon, 14 Jul 2014 09:57:00 GMT

Yep, I thought exactly that also, then I read the following: http://en.wikipedia.org/wiki/PureXML

Apparently mixing/matching is now the in thing.

Re: proc sql query to extract value out of xml data

jakarman — Mon, 14 Jul 2014 10:11:31 GMT

RW9 When I read that wikipedia link then I am interpreting that the DB2 calls have been enhanced to use classic DB/2 RDBMS as storage type and XML as storage type. Both accessible from the SQL DB/2 interface.
The same as SAS can read XML files, libname xml using mapper when needed, and classic datasets. It is also mentioning sometimes people have stored the XML as string field into classic DB/2 storage.
What is the case with abcd123?

Re: proc sql query to extract value out of xml data

jakarman — Mon, 14 Jul 2014 10:29:45 GMT

NitinDwivedi, you are hijacking an other op-s question. Working with Db/2 youa are in the IBM world (z/OS).
IBM has one of the best documentation on errors and all kind of stuff. However the accessibility is not optimal sometimes needing to understand how all is working.
This is the reference for DB/2 at ibm.com IBM Knowledge Center ( DB2 for z/OS 10.0.0>Troubleshooting for DB2>DB2 codes>SQL codes>SQL error codes)
It is complaing about a SQl construct. you can try to analyze with sastrace.
Are you aware of :

- doing a SQL explicit pass through for getting the data. But a the create table is outside that block the creation is done at the SAS environment ?

- With the select * your are asking to convert all data from db/2 to SAS even when datatypes are incompatible?

Re: proc sql query to extract value out of xml data

jakarman — Mon, 14 Jul 2014 10:33:26 GMT

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

Re: proc sql query to extract value out of xml data

NitinDwivedi — Mon, 14 Jul 2014 10:56:21 GMT

Jaap, Thanks. Yes, the issue is data incompablity and I am not sure how can I fix this issue. Right now , not aware about SQL explicit pass through for getting the data.

Re: proc sql query to extract value out of xml data

Kurt_Bremser — Mon, 14 Jul 2014 11:06:24 GMT

An alternate method to get the data is to let the DB admin unload the data into a structured flat file and read the data from there with a data step with formatted column input.

This is what we do around here.

Re: proc sql query to extract value out of xml data

NitinDwivedi — Mon, 14 Jul 2014 11:07:55 GMT

Kurt, It is needed for online execution

Re: proc sql query to extract value out of xml data

Kurt_Bremser — Mon, 14 Jul 2014 11:26:38 GMT

Maybe your DB admin can convert the offending fields to suitable strings, which you then parse in SAS.

Re: proc sql query to extract value out of xml data

jakarman — Mon, 14 Jul 2014 11:30:52 GMT

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.

Re: proc sql query to extract value out of xml data

NitinDwivedi — Tue, 15 Jul 2014 15:36:01 GMT

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.

Re: proc sql query to extract value out of xml data

jakarman — Tue, 15 Jul 2014 17:31:01 GMT

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.

Re: proc sql query to extract value out of xml data

NitinDwivedi — Wed, 16 Jul 2014 12:58:14 GMT

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.