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.
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.