Forum Stats

  • 3,874,105 Users
  • 2,266,680 Discussions
  • 7,911,728 Comments

Discussions

novice trying to work with xquery

933972
933972 Member Posts: 23
edited Aug 14, 2012 5:58PM in 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(
      '/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.

Answers

  • AlexAnd
    AlexAnd Member Posts: 2,554 Gold Trophy
    edited Aug 14, 2012 2:34AM
    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
  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    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.
  • 933972
    933972 Member Posts: 23
    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)?
This discussion has been closed.