BookmarkSubscribeRSS Feed
abcd123
Fluorite | Level 6

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.

19 REPLIES 19
Reeza
Super User

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

DB2 Database for Linux, UNIX, and Windows

abcd123
Fluorite | Level 6

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
Fluorite | Level 6

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

NitinDwivedi
Calcite | Level 5

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

NitinDwivedi
Calcite | Level 5

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
Barite | Level 11

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?


---->-- ja karman --<-----
NitinDwivedi
Calcite | Level 5

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
Super User

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
Calcite | Level 5

Kurt, It is needed for online execution

jakarman
Barite | Level 11

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.

---->-- ja karman --<-----
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Barite | Level 11

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?
 
 

---->-- ja karman --<-----

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 19 replies
  • 5744 views
  • 4 likes
  • 6 in conversation