5 Replies Latest reply: Sep 24, 2012 11:59 PM by 944008 RSS

    need help on xml query.

    944008
      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
          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
            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
              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
                Hi Thanks marco ..i did it thanks .for it..
                • 5. Re: need help on xml query.
                  944008
                  Thank you odie