3 Replies Latest reply on Apr 24, 2012 6:43 PM by odie_63

    XMLTable with multiple namespaces

    ant7
      I have a web service that returns the following XML:
      <s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
         <s:Body>
            <GetGeoLocationIDByWellTagIDResponse xmlns="http://tempuri.org/">
               <GetGeoLocationIDByWellTagIDResult xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
                  <a:int>9110910</a:int>
               </GetGeoLocationIDByWellTagIDResult>
            </GetGeoLocationIDByWellTagIDResponse>
         </s:Body>
      </s:Envelope>
      There is the possibility of more than one Int being returned, so I am trying to use xmltable to pull out this information. But I am having some difficulty pulling out the ID node, because (I am guessing) the node is named "a:int". Here is an example of how I am attempting to get this data:
      select x.*
      from 
      (select xmltype.createxml('<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
         <s:Body>
            <GetGeoLocationIDByWellTagIDResponse xmlns="http://tempuri.org/">
               <GetGeoLocationIDByWellTagIDResult xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
                  <a:int>9110910</a:int>
               </GetGeoLocationIDByWellTagIDResult>
            </GetGeoLocationIDByWellTagIDResponse>
         </s:Body>
      </s:Envelope>') xml from dual) t,
      xmltable(
       xmlnamespaces (
       'http://tempuri.org/' as "e",
       'http://schemas.microsoft.com/2003/10/Serialization/Arrays' as "a"),
       '//e:GetGeoLocationIDByWellTagIDResponse/a:GetGeoLocationIDByWellTagIDResult'
       passing t.xml
       columns
       loc_id int path 'a:int'
      ) x;
      This just returns null.

      How do I references the "a:int" node in the columns clause to correctly get this record back?
        • 1. Re: XMLTable with multiple namespaces
          AlexAnd
          smth
          Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
          Connected as apps
           
          SQL> 
          SQL> select x.*
            2  from
            3  (select xmltype('<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
            4     <s:Body>
            5        <GetGeoLocationIDByWellTagIDResponse xmlns="http://tempuri.org/">
            6           <GetGeoLocationIDByWellTagIDResult xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
            7              <a:int>9110910</a:int>
            8           </GetGeoLocationIDByWellTagIDResult>
            9        </GetGeoLocationIDByWellTagIDResponse>
           10     </s:Body>
           11  </s:Envelope>') xml from dual) t,
           12  xmltable(
           13   xmlnamespaces (
           14   'http://tempuri.org/' as "e",
           15   'http://schemas.microsoft.com/2003/10/Serialization/Arrays' as "a"
           16   , 'http://schemas.xmlsoap.org/soap/envelope/' as "s"
           17   ,default 'http://tempuri.org/'
           18   ),
           19   --'//e:GetGeoLocationIDByWellTagIDResponse/a:GetGeoLocationIDByWellTagIDResult'
           20   's:Envelope/s:Body/GetGeoLocationIDByWellTagIDResponse/GetGeoLocationIDByWellTagIDResult'
           21   passing t.xml
           22   columns
           23   loc_id number path 'a:int'
           24  ) x;
           
              LOC_ID
          ----------
             9110910
           
          SQL>
          • 2. Re: XMLTable with multiple namespaces
            ant7
            AlexAnd -

            Thanks for the reply. It is working great for the single instance. I just tried it against one of the results with multiple records and it failed with

            ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
            19279. 00000 - "XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence"
            *Cause:    The XQuery sequence passed in had more than one item.
            *Action:   Correct the XQuery expression to return a single item sequence.

            Here is the XML
            <s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
               <s:Body>
                  <GetGeoLocationIDByWellTagIDResponse xmlns="http://tempuri.org/">
                     <GetGeoLocationIDByWellTagIDResult xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
                        <a:int>60395867</a:int>
                        <a:int>7948500</a:int>
                        <a:int>2282774</a:int>
                        <a:int>47054172</a:int>
                        <a:int>47434315</a:int>
                        <a:int>52639251</a:int>
                     </GetGeoLocationIDByWellTagIDResult>
                  </GetGeoLocationIDByWellTagIDResponse>
               </s:Body>
            </s:Envelope>
            Tony
            • 3. Re: XMLTable with multiple namespaces
              odie_63
              SQL> with sample_data as (
                2    select xmltype('<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
                3     <s:Body>
                4        <GetGeoLocationIDByWellTagIDResponse xmlns="http://tempuri.org/">
                5           <GetGeoLocationIDByWellTagIDResult xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
                6              <a:int>60395867</a:int>
                7              <a:int>7948500</a:int>
                8              <a:int>2282774</a:int>
                9              <a:int>47054172</a:int>
               10              <a:int>47434315</a:int>
               11              <a:int>52639251</a:int>
               12           </GetGeoLocationIDByWellTagIDResult>
               13        </GetGeoLocationIDByWellTagIDResponse>
               14     </s:Body>
               15  </s:Envelope>') xml
               16    from dual
               17  )
               18  select x.loc_id
               19  from sample_data t
               20     , xmltable(
               21         xmlnamespaces (
               22           'http://schemas.microsoft.com/2003/10/Serialization/Arrays' as "a"
               23         , 'http://schemas.xmlsoap.org/soap/envelope/' as "s"
               24         , default 'http://tempuri.org/'
               25         )
               26       , '/s:Envelope/s:Body/GetGeoLocationIDByWellTagIDResponse/GetGeoLocationIDByWellTagIDResult/a:int'
               27         passing t.xml
               28         columns
               29           loc_id number path '.'
               30       ) x
               31  ;
               
                  LOC_ID
              ----------
                60395867
                 7948500
                 2282774
                47054172
                47434315
                52639251
               
              6 rows selected
               
              1 person found this helpful