sas proc sql pass through xml

[abcd123]

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.

[Anotherdream]

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]

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]

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.