This discussion is archived
5 Replies Latest reply: Sep 24, 2012 9:59 PM by 944008 RSS

need help on xml query.

944008 Newbie
Currently Being Moderated
pls click here to sample of xml data
http://pcls1.craftyclicks.co.uk/xml/rapidaddress?postcode=AA11AA
I make the following query to retrive values of xml data
select x1.org,x1.org2,x1.org3,x1.org4,x1.org5 from xml_data_tab x, xmltable('/CraftyResponse/address_data_paf_compact/thoroughfare/delivery_point' passing x.xml_data
columns
org varchar2(500) path 'organisation_name' ,org2 varchar2(500) path 'building_number',org3 varchar2(500) path 'building_name',
org4 varchar2(500) path 'dependent_locality',org5 varchar2(500) path 'town') x1 ;
This is the output ..

org4 and org5 is emply can any one guide me what should i do in the script ..

SQL> /

ORG ORG2 ORG3 ORG4 ORG5
---------- -------------------- -------------------- -------------------- ----------
THE BAKERY 1
FILMS R US 3
FAMILY BUT 7
CHER

BIG HOUSE
17 LITTLE COTTAGE

SQL> spool off









Below is sample of xml
<CraftyResponse>
<address_data_paf_compact>
<thoroughfare_count>1</thoroughfare_count>
<thoroughfare>
<delivery_point_count>5</delivery_point_count>
<delivery_point>
<organisation_name>THE BAKERY</organisation_name>
<department_name/>
<po_box_number/>
<building_number>1</building_number>
<sub_building_name/>
<building_name/>
<udprn>12345678</udprn>
</delivery_point>
<delivery_point>
<organisation_name>FILMS R US</organisation_name>
<department_name/>
<po_box_number/>
<building_number>3</building_number>
<sub_building_name/>
<building_name/>
<udprn>12345679</udprn>
</delivery_point>
<delivery_point>
<organisation_name>FAMILY BUTCHER</organisation_name>
<department_name/>
<po_box_number/>
<building_number>7</building_number>
<sub_building_name/>
<building_name/>
<udprn>12345680</udprn>
</delivery_point>
<delivery_point>
<organisation_name/>
<department_name/>
<po_box_number/>
<building_number/>
<sub_building_name/>
<building_name>BIG HOUSE</building_name>
<udprn>12345681</udprn>
</delivery_point>
<delivery_point>
<organisation_name/>
<department_name/>
<po_box_number/>
<building_number>17</building_number>
<sub_building_name/>
<building_name>LITTLE COTTAGE</building_name>
<udprn>12345682</udprn>
</delivery_point>
<dependent_thoroughfare_name/>
<dependent_thoroughfare_descriptor/>
<thoroughfare_name>HIGH</thoroughfare_name>
<thoroughfare_descriptor>STREET</thoroughfare_descriptor>
</thoroughfare>
<double_dependent_locality/>
<dependent_locality>CRAFTY VALLEY</dependent_locality>
<town>BIG CITY</town>
<postal_county>POSTAL COUNTY</postal_county>
<traditional_county>TRADITIONAL COUNTY</traditional_county>
<postcode>AA1 1AA</postcode>
</address_data_paf_compact>
</CraftyResponse>
  • 1. Re: need help on xml query.
    944008 Newbie
    Currently Being Moderated
    Was expecting these 2 values to be retrived..
    What am i doing wrong..
    <dependent_locality>CRAFTY VALLEY</dependent_locality>
    <town>BIG CITY</town>
    any informatino is appreciated
  • 2. Re: need help on xml query.
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    Those two values are not available within the section <delivery_point> but outside this section.

    So don't use

    /CraftyResponse/address_data_paf_compact/thoroughfare/delivery_point

    but nest it one step less

    /CraftyResponse/address_data_paf_compact

    or "travel upwards" in the tree via parents thoroughfare and delivery_point
    or split the result in two xmltable sections via passing the XML of /CraftyResponse/address_data_paf_compact into a second xmltable
  • 3. Re: need help on xml query.
    odie_63 Guru
    Currently Being Moderated
    e.g.
    SQL> select x2.org
      2       , x2.org2
      3       , x2.org3
      4       , x1.org4
      5       , x1.org5
      6  from xmltable('/CraftyResponse/address_data_paf_compact'
      7         passing httpuritype('http://pcls1.craftyclicks.co.uk/xml/rapidaddress?postcode=AA11AA').getxml()
      8         columns org4 varchar2(20) path 'dependent_locality'
      9               , org5 varchar2(20) path 'town'
     10               , delivery_points xmltype path 'thoroughfare/delivery_point'
     11       ) x1
     12     , xmltable('/delivery_point'
     13         passing x1.delivery_points
     14         columns org  varchar2(20) path 'organisation_name'
     15               , org2 varchar2(20) path 'building_number'
     16               , org3 varchar2(20) path 'building_name'
     17       ) x2
     18  ;
     
    ORG                  ORG2                 ORG3                 ORG4                 ORG5
    -------------------- -------------------- -------------------- -------------------- --------------------
    THE BAKERY           1                                         CRAFTY VALLEY        BIG CITY
    FILMS R US           3                                         CRAFTY VALLEY        BIG CITY
    FAMILY BUTCHER       7                                         CRAFTY VALLEY        BIG CITY
                                              BIG HOUSE            CRAFTY VALLEY        BIG CITY
                         17                   LITTLE COTTAGE       CRAFTY VALLEY        BIG CITY
     
  • 4. Re: need help on xml query.
    944008 Newbie
    Currently Being Moderated
    Hi Thanks marco ..i did it thanks .for it..
  • 5. Re: need help on xml query.
    944008 Newbie
    Currently Being Moderated
    Thank you odie

Legend

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