8 Replies Latest reply: Aug 30, 2012 1:09 AM by 944008 RSS

    a doubt in xml ..should i use extract or make my own plsql function..

    944008
      I am using apex.. and fetchiNG data in xml format..
      Now in plsql ..There are functions ..like extract which can be used to extract the values..
      But i find it difficult to understand
      since i find making a function which shall use substring and instr to find the value .. .
      Wait let me show you the example of xml..data
      <?xml version="1.0" encoding="WINDOWS-1252"?>
      <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>

      Now how do i extract values of town, postal_country building_name,
      I know we can use extract to get the result ..But how exactly to use extract here I dotn know..
      The other method is a easier method.. which involves creating your own function which uses substr ..and instr
      It shal lsearch for <organisation_name> and find the orgonizatio..

      I dont know which approch is betTER .. I find making my own customised function a easier and better way..
      But any one has idea on extract function ..please let me know..
      Db version is 11g

      Any information shall be appreciated
      Thanks
        • 1. Re: a doubt in xml ..should i use extract or make my own plsql function..
          Marco Gralike
          Use XMLTABLE (or XQuery if you are comfy) with it. Don't wright your "own xmlparser" via string functions. Its already done for you.
          select xt1.org as "ORG_NAME"
          from xmltable ('/CraftyResponse'
                         passing <xmltype content>
                         columns
                           org varchar2(500) path 'address_data_paf_compact/organisation_name'
                        ) xt1;
          I didn't test the code. There might be some typo's

          Edited by: Marco Gralike on Aug 29, 2012 10:09 AM
          • 2. Re: a doubt in xml ..should i use extract or make my own plsql function..
            odie_63
            I dont know which approch is betTER .. I find making my own customised function a easier and better way..
            To second Marco's reply, how does building your own function be better than built-in, optimized functionalities specifically designed for the job?

            Learn about XMLTable (and related stuff) here : http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_xquery.htm#CBAGCBGJ
            Lots of practical examples in this forum too.

            To help you further : where does the XML reside? variable, column (which datatype)? External file?
            • 3. Re: a doubt in xml ..should i use extract or make my own plsql function..
              944008
              Hi Thanks Marco, and odie
              Tried it.. some synatx issues..
              Was trying to understand about these functions..
              This is the structure of my table ..
              desc xml_data_tab
              Name Null Type
              -------- ---- ---------
              XML_DATA XMLTYPE()

              This is the xml data
              <?xml version = '1.0' encoding = 'UTF-8'?><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></department_name>
              <po_box_number></po_box_number>
              <building_number>1</building_number>
              <sub_building_name></sub_building_name>
              <building_name></building_name>
              <udprn>12345678</udprn>
              </delivery_point>
              <delivery_point>
              <organisation_name>FILMS R US</organisation_name>
              <department_name></department_name>
              <po_box_number></po_box_number>
              <building_number>3</building_number>
              <sub_building_name></sub_building_name>
              <building_name></building_name>
              <udprn>12345679</udprn>
              </delivery_point>
              <delivery_point>
              <organisation_name>FAMILY BUTCHER</organisation_name>
              <department_name></department_name>
              <po_box_number></po_box_number>
              <building_number>7</building_number>
              <sub_building_name></sub_building_name>
              <building_name></building_name>
              <udprn>12345680</udprn>
              </delivery_point>
              <delivery_point>
              <organisation_name></organisation_name>
              <department_name></department_name>
              <po_box_number></po_box_number>
              <building_number></building_number>
              <sub_building_name></sub_building_name>
              <building_name>BIG HOUSE</building_name>
              <udprn>12345681</udprn>
              </delivery_point>
              <delivery_point>
              <organisation_name></organisation_name>
              <department_name></department_name>
              <po_box_number></po_box_number>
              <building_number>17</building_number>
              <sub_building_name></sub_building_name>
              <building_name>LITTLE COTTAGE</building_name>
              <udprn>12345682</udprn>
              </delivery_point>
              <dependent_thoroughfare_name></dependent_thoroughfare_name>
              <dependent_thoroughfare_descriptor></dependent_thoroughfare_descriptor>
              <thoroughfare_name>HIGH</thoroughfare_name>
              <thoroughfare_descriptor>STREET</thoroughfare_descriptor>
              </thoroughfare>
              <double_dependent_locality></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>

              Edited by: susf on Aug 29, 2012 7:00 PM
              • 4. Re: a doubt in xml ..should i use extract or make my own plsql function..
                944008
                I had passed the following command..
                select xt1.org as "ORG_NAME"
                from xml_data_tab ('/CraftyResponse' passing xml_data
                columns org varchar2(500) path 'address_data_paf_compact/organisation_name') xt1;


                ERROR at line 2:
                ORA-00933: SQL command not properly ended


                Am a bit confused.. on xml_data_tab ('/CraftyResponse' passing xml_data
                When do you use / and //
                i will actually like to have a sql which shall show me all data with no xml..
                Bit tricky and time consuming ..again thanks for trying to help.
                Thanks again.
                • 5. Re: a doubt in xml ..should i use extract or make my own plsql function..
                  jmcnaug2
                  susf, can you provide a test case please, so the people who are trying to help you can see the syntax errors you're getting?

                  Also, which version of the database are you running on?

                  Hint: When posting code, enclose your code within the tags, i.e.:

                  {noformat}
                  Your XML goes here
                  {noformat}
                  • 6. Re: a doubt in xml ..should i use extract or make my own plsql function..
                    944008
                    The version i am using in 11g xe..
                    Below is the actual xml code, and ..structure of table is xml_data_type column present is xml_data
                    <?xml version = '1.0' encoding = 'UTF-8'?><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></department_name>
                    <po_box_number></po_box_number>
                    <building_number>1</building_number>
                    <sub_building_name></sub_building_name>
                    <building_name></building_name>
                    <udprn>12345678</udprn>
                    </delivery_point>
                    <delivery_point>
                    <organisation_name>FILMS R US</organisation_name>
                    <department_name></department_name>
                    <po_box_number></po_box_number>
                    <building_number>3</building_number>
                    <sub_building_name></sub_building_name>
                    <building_name></building_name>
                    <udprn>12345679</udprn>
                    </delivery_point>
                    <delivery_point>
                    <organisation_name>FAMILY BUTCHER</organisation_name>
                    <department_name></department_name>
                    <po_box_number></po_box_number>
                    <building_number>7</building_number>
                    <sub_building_name></sub_building_name>
                    <building_name></building_name>
                    <udprn>12345680</udprn>
                    </delivery_point>
                    <delivery_point>
                    <organisation_name></organisation_name>
                    <department_name></department_name>
                    <po_box_number></po_box_number>
                    <building_number></building_number>
                    <sub_building_name></sub_building_name>
                    <building_name>BIG HOUSE</building_name>
                    <udprn>12345681</udprn>
                    </delivery_point>
                    <delivery_point>
                    <organisation_name></organisation_name>
                    <department_name></department_name>
                    <po_box_number></po_box_number>
                    <building_number>17</building_number>
                    <sub_building_name></sub_building_name>
                    <building_name>LITTLE COTTAGE</building_name>
                    <udprn>12345682</udprn>
                    </delivery_point>
                    <dependent_thoroughfare_name></dependent_thoroughfare_name>
                    <dependent_thoroughfare_descriptor></dependent_thoroughfare_descriptor>
                    <thoroughfare_name>HIGH</thoroughfare_name>
                    <thoroughfare_descriptor>STREET</thoroughfare_descriptor>
                    </thoroughfare>
                    <double_dependent_locality></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>
                    • 7. Re: a doubt in xml ..should i use extract or make my own plsql function..
                      user503699
                      susf wrote:
                      The version i am using in 11g xe..
                      Below is the actual xml code, and ..structure of table is xml_data_type column present is xml_data
                      You should be really reading the documentation as suggested earlier.
                      An xml is hierarchial structure. With your structure, something like following should work (not testd)
                      select x1.org
                        from xml_data_type x, xmltable('/CraftyResponse/address_data_paf_compact/thoroughfare/delivery_point' passing x.xml_data
                                                                     columns
                                                                         org varchar2(500) path 'organisation_name') x1 ;
                      • 8. Re: a doubt in xml ..should i use extract or make my own plsql function..
                        944008
                        hello thanks it worked and from this I shall able to know how it works..will also check the documentation..
                        But was not able to understand was getting confused..By this example i should be able to understand everything
                        Thanks again.