Home > Default > Parse a soap envelope

Parse a soap envelope

October 11Hits:0
Advertisement
I have a SOAP response envelope that I am trying to parse using extractvalue.
The response is stored in a xmltype field in a table.
Table
create table ws_results
(x xmltype);SOAP Response
<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
     <s:Header>
          <ActivityId CorrelationId="12855f8e-d2be-40c7-81d8-fafa3cf9a779" xmlns="http://schemas.microsoft.com/2004/09/ServiceModel/Diagnostics">d24174d3-e095-460a-b9f8-2bae66efe813</ActivityId>
     </s:Header>
     <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
          <FetchResponse xmlns="urn: WA.Ecy.ADS.FacilitySite.Services">
               <FacilitySiteId>99997167</FacilitySiteId>
          </FetchResponse>
     </s:Body>
</s:Envelope>Here is the SQL that I attempting to use to get the Facility Site ID out
select extractvalue(x,'/FetchResponse/FacilitySiteId') from ws_results;
select extractvalue(x,'/FetchResponse/FacilitySiteId','xmlns="http://microsoft.com/wsdl/types/"')
from ws_resultsThese returns a null.
Seems like this should be pretty straightforward and I am sure that I am missing something small. Any help would be appreciated.
Thanks, Tony

Answers

The problem seems to be (at least with me testing) is the xmlns="urn: WA.Ecy.ADS.FacilitySite.Services". Oracle considers it invalid xpath. If it is replaced with "http://www.ms.com/xml", then it seems OK with xpath. You can test this:
declare
     ---v_xmlText          clob ;      ---varchar2(32765);
     v_xmlText          sys.xmltype;
     v_FacID               varchar2(50) := null;
     v_FacilitySiteId     varchar2(50) := null;
     v_FacilitySiteName     varchar2(50) := null;
begin
     v_xmlText := sys.xmlType('
<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
     <s:Header>
          <ActivityId CorrelationId="48538673-36c0-4f6d-8c05-94b753d0e3ab"
          xmlns="http://schemas.microsoft.com/2004/09/ServiceModel/Diagnostics">
          b4af9688-a929-4b9f-a187-fb68f3927240
          </ActivityId>
     </s:Header>
     <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xmlns:xsd="http://www.w3.org/2001/XMLSchema">
          <FacilitySiteResponse xmlns="http://www.ms.com/xml">
               <FacilitySiteEntity>
                    <Id>99997167</Id>
                    <FacilitySiteId>99997167</FacilitySiteId>
                    <FacilitySiteName>My New Test Facility</FacilitySiteName>
                    <FacilitySiteKeySearchName>My New Te</FacilitySiteKeySearchName>
                    <VirtualSiteFlag>78</VirtualSiteFlag>
                    <GeographicLocationId>99997167</GeographicLocationId>
                    <CreatedDate>2008-11-07T07:32:21.07</CreatedDate>
                    <ModifiedByName>taus461</ModifiedByName>
                    <ModifiedDate>2008-11-07T07:44:20.723</ModifiedDate>
                    <CreatedByName>taus461</CreatedByName>
               </FacilitySiteEntity>
               <GeographicLocationEntity>
                    <Id>99997167</Id>
                    <GeographicLocationId>99997167</GeographicLocationId>
                    <AddressLine1>2020 22nd Ave SE</AddressLine1>
                    <CityName>Olympia</CityName>
                    <CongressionalDistrictNumber xsi:nil="true"/>
                    <COORD_XTNT_CD xsi:nil="true"/>
                    <HorizontalDatumCode>3</HorizontalDatumCode>
                    <HorizontalAccuracyLevelCode>13</HorizontalAccuracyLevelCode>
                    <HorizontalCollectionMethodCode>2</HorizontalCollectionMethodCode>
                    <GeographicPositionCode>8</GeographicPositionCode>
                    <LatitudeDecimalNumber xsi:nil="true"/>
                    <LatitudeDegreeNumber xsi:nil="true"/>
                    <LocationVerifiedFlag xsi:nil="true"/>
                    <LatitudeMinutesNumber xsi:nil="true"/>
                    <LatitudeSecondsNumber xsi:nil="true"/>
                    <LongitudeDecimalNumber xsi:nil="true"/>
                    <LongitudeDegreeNumber xsi:nil="true"/>
                    <LongitudeMinutesNumber xsi:nil="true"/>
                    <LongitudeSecondsNumber xsi:nil="true"/>
                    <BaseReferenceCode>SPCS</BaseReferenceCode>
                    <SOURCE_SCALE_CD>99</SOURCE_SCALE_CD>
                    <VerticalMeasureNumber xsi:nil="true"/>
                    <VerticalMeasureUnitCode>FT</VerticalMeasureUnitCode>
                    <VerticalReferenceCode xsi:nil="true"/>
                    <VerticalCollectionMethodCode xsi:nil="true"/>
                    <VerticalDatumCode xsi:nil="true"/>
                    <VerticalAccuracyLevelCode xsi:nil="true"/>
                    <CountyCodeNumber xsi:nil="true"/>
                    <LocationDescription1>This is my test facility decription</LocationDescription1>
                    <EPARegionName xsi:nil="true"/>
                    <GISVerifiedFlag xsi:nil="true"/>
                    <LegislativeDistrictNumber xsi:nil="true"/>
                    <IndianLandFlag>78</IndianLandFlag>
                    <RangeDirectionCode xsi:nil="true"/>
                    <RangeNumber xsi:nil="true"/>
                    <RegionCode/>
                    <SectionNumber xsi:nil="true"/>
                    <StateCode>WA</StateCode>
                    <SPCSXCoordinateNumber xsi:nil="true"/>
                    <SPCSYCoordinateNumber xsi:nil="true"/>
                    <SPCSZoneCode xsi:nil="true"/>
                    <TownshipDirectionCode xsi:nil="true"/>
                    <TownshipNumber xsi:nil="true"/>
                    <UTMXCoordinateNumber xsi:nil="true"/>
                    <UTMYCoordinateNumber xsi:nil="true"/>
                    <UTMZoneCode xsi:nil="true"/>
                    <WRIAIdNumber xsi:nil="true"/>
                    <ZipCode>98501</ZipCode>
                    <PLAIndicatorCode xsi:nil="true"/>
                    <GISReferenceNumber xsi:nil="true"/>
                    <GISCalculatedLatDecimalNumber xsi:nil="true"/>
                    <GISCalculatedLongDecimalNumber xsi:nil="true"/>
                    <ModifiedByName>taus461</ModifiedByName>
                    <ModifiedDate>2008-11-07T07:44:20.74</ModifiedDate>
                    <CreatedDate xsi:nil="true"/>
                    <CreatedByName>taus461</CreatedByName>
                    <FacilitySite>
                         <Id>99997167</Id>
                         <FacilitySiteId>99997167</FacilitySiteId>
                         <FacilitySiteName>My New Test Facility</FacilitySiteName>
                         <FacilitySiteKeySearchName>My New Te</FacilitySiteKeySearchName>
                         <VirtualSiteFlag>78</VirtualSiteFlag>
                         <GeographicLocationId>99997167</GeographicLocationId>
                         <CreatedDate>2008-11-07T07:32:21.07</CreatedDate>
                         <ModifiedByName>taus461</ModifiedByName>
                         <ModifiedDate>2008-11-07T07:44:20.723</ModifiedDate>
                         <CreatedByName>taus461</CreatedByName>
                    </FacilitySite>
               </GeographicLocationEntity>
               <IsOperationSuccess>true</IsOperationSuccess>
          </FacilitySiteResponse>
     </s:Body>
</s:Envelope>');
     select fac.facID, fac.FacilitySiteId, fac.FacilitySiteName
          into v_FacID, v_FacilitySiteId, v_FacilitySiteName
     from
     xmltable
          xmlnamespaces
               default 'http://www.ms.com/xml'     
               ---'http://www.ms.com/xml'
               ---'http://schemas.xmlsoap.org/ws/2004/08/addressing' as "wsa",
               ---'http://www.w3.org/2003/05/soap-envelope' as "soap"
          '//FacilitySiteResponse'
          passing v_xmlText
          columns
          FacID               varchar2(50)     path     '//FacilitySiteEntity/Id',
          FacilitySiteId          varchar2(50)     path     '//FacilitySiteEntity/FacilitySiteId',
          FacilitySiteName     varchar2(50)     path     '//FacilitySiteEntity/FacilitySiteName'
     ) fac;
     dbms_output.put_line('FacID = ' || v_FacID);
     dbms_output.put_line('FacSiteID = ' || v_FacilitySiteId);
     dbms_output.put_line('FacSiteName = ' || v_FacilitySiteName);
                select extractValue(v_xmlText, '//FacilitySiteResponse/FacilitySiteEntity/Id', 'xmlns="http://www.ms.com/xml"')
     into v_FacID from dual;
               dbms_output.put_line('FacID = ' || v_FacID);
end;
/FacID = 99997167
FacSiteID = 99997167
FacSiteName = My New Test Facility
FacID = 99997167
PL/SQL procedure successfully completed.

Read other 12 answers

Tags:

Related Articles

  • Parse a soap envelopeOctober 11

    I have a SOAP response envelope that I am trying to parse using extractvalue. The response is stored in a xmltype field in a table. Table create table ws_results (x xmltype);SOAP Response <s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelo

  • JAXB 1.3 can't parse W3 SOAP 2003-05 envelope XML schemaOctober 11

    Hello, I was trying to use JAXB to parse the MMAP schema from the SMS Forum (http://www.smsforum.net/schemas/mmap/v1.0/mmap.xsd). I finally tracked it down to XJC choking on the included SOAP envelope schema's use of the xml:lang attribute for a tag

  • Parsing SOAP EnvelopeNovember 30

    I am new to mobile web services. I need to create a parser but my code didnot work:( Could you please help me? * To change this template, choose Tools | Templates * and open the template in the editor. import java.io.*; import javax.microedition.midl

  • Web service parsing soap envelope problemNovember 30

    I'm trying to parse an XML document found in a collection. I can do it in TOAD but get a "ORA-01008: not all variables bound" message in APEX.I accidently hit the post before the messag was composed. I'm trying to parse an XML soap envelope foun

  • Trying to parse a Soap MessageOctober 11

    I'm working on creating webservices that will be consumed by Director and working with the client programmer, we've come to recognize the problem, but we don't know how to resolve it. Problem: the XMLParser doesn't follow href parameters in tags that

  • Error during parsing of SOAP headerOctober 11

    Hi all, I have configured the j-meter to send an http request to inegration engine of PI. The request xml that I am sending is as follows: <SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/"> <SOAP:Header> <SOAP:

  • SOAP scenario "Do not use SOAP Envelope" check problemsOctober 11

    Hi Gurus! I'm again here. I've an scenario with SOAP Receiver, the WS do I need consume, looks like: POST /SumTotalws1/services/Authentication.asmx HTTP/1.1 Host: xxx.xxx.xxx.xxx Content-Type: text/xml; charset=utf-8 Content-Length: length SOAPAction

  • HTTP response code 500 : Error during parsing of SOAP headerOctober 11

    Hi Experts, I have a MAIL to IDOC scenario.  An external third party emails invoices to our inbox - which we pick up and process the attachment.  All is well when I test the scenario internally, but when the third party emails it fails with (see ERRO

  • Bug in EWS Java API 1.2: Does not support namespace prefix "s" as in "s:Envelope".. Expects "soap:Envelope"October 11

    It can NOT parse .... <?xml version="1.0" encoding="utf-8"?><s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/"><s:Header><h:ServerVersionInfo MajorVersion="14" MinorVersion="2&

  • '/soap:Envelope ' in extract throws invalid token errorOctober 11

    I'm receiving xml via webservice/soap and placing in an XMLTYPE table. Upon trying to execute a test query: SELECT EXTRACTVALUE(VALUE(d),'Destinations/CountryID') AS ID FROM X_TEST p, TABLE (xmlsequence(EXTRACT(p.object_value,'/soap:Envelope/soap:Bod

Copyright (C) 2019 wisumpire.com, All Rights Reserved. webmaster#wisumpire.com 14 q. 0.669 s.