proc sql query to extract value out of xml data

[abcd123]

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.

[Reeza]

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

DB2 Database for Linux, UNIX, and Windows

[abcd123]

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;

[abcd123]

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

[NitinDwivedi]

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';

[RW9]

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

[NitinDwivedi]

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.

[jakarman]

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?

[NitinDwivedi]

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.

[Kurt_Bremser]

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.

[NitinDwivedi]

Kurt, It is needed for online execution

[Kurt_Bremser]

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

[jakarman]

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.

[RW9]

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.

[jakarman]

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?