BookmarkSubscribeRSS Feed
abcd123
Fluorite | Level 6

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.

3 REPLIES 3
Anotherdream
Quartz | Level 8

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.

abcd123
Fluorite | Level 6

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.

Patrick
Opal | Level 21

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.

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
  • 3 replies
  • 1565 views
  • 1 like
  • 3 in conversation