topic Re: sas proc sql pass through xml in SAS Procedures

sas proc sql pass through xml

abcd123 — Thu, 21 May 2015 16:24:50 GMT

Hello,

I am using proc sql pass through to extract xml data from a db2 server, the query looks like following:

select xmlquery('data($DATA/*:annotation/*:EventStart/@data)') as MyData

from db2server

The returned data is like following:

<?xml version="1.0" encoding="windows-1252" ?>1234567

But all I need is the numeric part, i.e., 1234567. How to remove the leading <?xml version="1.0" encoding="windows-1252" ?>?

Thanks.

Re: sas proc sql pass through xml

Anotherdream — Thu, 21 May 2015 16:36:14 GMT

You could use the TRANSTRN function to simply remove your code.

Aka something like

newstring=strip(Transtrn(oldvar,'<?xml version="1.0" encoding="windows-1252" ?>',''))

Of if you cannot guarantee that hte first section will always have this exact wording, you could simply substring the results starting at the first end position of >.

Aka something like

Newstring=substr(oldstring,find(oldstring,'>')+1,length(oldstring));

Note I didn't test either of these but in theory should work.

Re: sas proc sql pass through xml

abcd123 — Thu, 21 May 2015 18:36:30 GMT

Thanks for reply. I am sure your method would work out, but I feel there should be a more xml-like way to do the job.

Re: sas proc sql pass through xml

Patrick — Thu, 21 May 2015 19:46:13 GMT

The more XML like way would need to happen as part of your XMLQuery expression. Once you get the result back it's just a string in a variable and you will need some string operation to get what you want. For the example string you've posted a simple RegEx should do though.

proc sql;

create table test as

select MyData, prxchange('s/<[^>]+>//oi',-1,MyData) as MyData_TagRemoved

from

/* and here your pass-through SQL block */

(

select '<?xml version="1.0" encoding="windows-1252" ?>1234567' as MyData

from sashelp.class(obs=1)

)

;

quit;

Regular Expressions are also implemented in DB2 IBM Knowledge Center. If you can't set-up your XMLQuery in a way that you only get back what you need then I'd probably would try and implement the string parsing/replacement within DB2 - so what I've done in above example with prxchange() - to not transfer unnecessary data from the Database to SAS.