04-07-2014 04:40 PM
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?
04-07-2014 06:20 PM
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';
create table test as
select * from DB2_SALES.north_region;
07-14-2014 03:20 AM
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';
07-14-2014 05:54 AM
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.
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(
CREDIT_ASSESSMENT_SUBMISSION as CAS
WHERE CREDIT_CASE_ID = '001234bc-4e38-4dee-ad5d-b0e2d91457e8' );
Can one guide me that why there is an issue and how it can be resolved.
07-14-2014 06:29 AM
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?
07-14-2014 06:56 AM
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.
07-14-2014 07:06 AM
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.
07-14-2014 07:26 AM
Maybe your DB admin can convert the offending fields to suitable strings, which you then parse in SAS.
07-14-2014 05:34 AM
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.
07-14-2014 05:57 AM
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.
07-14-2014 06:11 AM
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?