3 Replies Latest reply on Aug 14, 2012 9:58 PM by 933972

    novice trying to work with xquery

      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(
            passing xmlparse(document
                       '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
          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
          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
            The URL returns this :
            <Weather version="1.00">
              <TemperatureAllTimeMinDateTime>8/Jul/2007 07:58:00</TemperatureAllTimeMinDateTime>
            I don't see any <string> element, or any namespace declaration ;)

            See Alex's example.
            • 3. Re: novice trying to work with xquery
              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)?