This discussion is archived
3 Replies Latest reply: Aug 14, 2012 2:58 PM by 933972 RSS

novice trying to work with xquery

933972 Newbie
Currently Being Moderated
Unfortunately my XML skills are no where near my DBA skills, and now I'm starting to dabble, I'm finding this really interesting.

I'm trying to get a query from SQLPlus to read off a website using XQuery.

The below returned 'no rows selected', and I presume it is b/c the /Weather should be /Weather version (but no spaces are allowed).
set scan off

select *
 from xmltable(
      '/Weather'
      passing xmlparse(document
               xmlcast(
                xmlquery(
                 'declare default element namespace "http://weather.cobbnz.com"; /string'
                 passing httpuritype('http://weather.cobbnz.com/weatherservice/xml.aspx').getXML()
                 returning content
                ) as varchar2(4000)
               )
              )
      columns currenttemp   varchar2(100) path 'TemperatureCurrent'
            , feelslike     varchar2(100) path 'TemperatureFeelsLike'
            , lowestever    varchar2(100) path 'TemperatureAllTimeMin'
 );
Also,... I have seen some web service XML sites where you can parse in a substitution variable. I can 'set scan off' in SQLPlus, but how do I do the equivalent in SQL Developer? (without getting prompted for the variable)

Thanks in advance.
  • 1. Re: novice trying to work with xquery
    AlexAnd Guru
    Currently Being Moderated
    what about
    SQL> select *
      2   from xmltable(
      3        '/Weather'
      4        passing httpuritype('http://weather.cobbnz.com/weatherservice/xml.aspx').getXML()
      5        columns currenttemp   varchar2(100) path 'TemperatureCurrent'
      6              , feelslike     varchar2(100) path 'TemperatureFeelsLike'
      7              , lowestever    varchar2(100) path 'TemperatureAllTimeMin'
      8   )
      9  /
     
    CURRENTTEMP                                                                      FEELSLIKE                                                                        LOWESTEVER
    -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
    9.61                                                                             8.2                                                                              -5.2
     
    SQL> 
    you can find more examples on forum for httpuritype as example

    Edited by: AlexAnd on Aug 13, 2012 11:33 PM
  • 2. Re: novice trying to work with xquery
    odie_63 Guru
    Currently Being Moderated
    The URL returns this :
    <Weather version="1.00">
      <TemperatureCurrent>9.33</TemperatureCurrent>
      <TemperatureFeelsLike>7.3</TemperatureFeelsLike>
      <TemperatureAllTimeMin>-5.2</TemperatureAllTimeMin>
      <TemperatureAllTimeMinDateTime>8/Jul/2007 07:58:00</TemperatureAllTimeMinDateTime>
      ...
    </Weather>
    I don't see any <string> element, or any namespace declaration ;)

    See Alex's example.
  • 3. Re: novice trying to work with xquery
    933972 Newbie
    Currently Being Moderated
    thank you both very much.

    Are either of you able to advise what the equivalent of 'set scan off' is in SQL Developer so I don't get prompted for the substitution variable (where the URL has a ampersand in it)?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points