5 Replies Latest reply: Jun 6, 2014 10:16 AM by user587700 RSS

    Need help in getting data out of SOAP Envelope response from Web Service

    user587700

      Hi,

      I am new to XML processing and do not know how to get data out of an SOAP Envelope response from a web service.

      Using DBMS_XMLDOM routines to get the data out is not working as expected.

      --------------

       

      l_nodelist

      := 

      DBMS_XMLDOM.getelementsbytagname

      (indomdoc, 

       

      'CRM_MORE_signal'); -- This doesn't care about namespaces 

       

      FOR i IN 0 .. DBMS_XMLDOM.getlength (l_nodelist) - 1 

       

      LOOP

       

      DBMS_XMLDOM.getelementsbytagname

      (p_indomdoc, p_field_name); 

      l_field_node

      := DBMS_XMLDOM.item (l_nodelist_level2, p_index); 

      l_field_value

      := 

      DBMS_XMLDOM.getnodevalue

      (DBMS_XMLDOM.getfirstchild (l_field_node)); 

      DBMS_XMLDOM.freenode

      (l_field_node); 

      DBMS_XMLDOM.freenodelist

      (l_nodelist_level2);

      .

      .

      .

      same code for all fields in the list

      END LOOP;

      When there are optional fields not present in one record, it picks up the valus for the same named field from following records where it is present.

      The XML Looks like the following:

      <?xml version="1.0" encoding="utf-8"?>

      <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

      <soap:Body>

        <GetDataResponse xmlns="urn:Afas.Profit.Services">

         <GetDataResult>

          <AfasGetConnector>

           <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

            <xs:element name="AfasGetConnector">

             <xs:complexType>

              <xs:choice maxOccurs="unbounded">

               <xs:element name="CRM_MORE_signal">

                <xs:complexType>

                 <xs:sequence>

                  <xs:element name="Nummer" type="xs:string" minOccurs="0"/>

                  <xs:element name="MORE_clientnummer" type="xs:string" minOccurs="0"/>

                  <xs:element name="Naam" type="xs:string" minOccurs="0"/>

                  <xs:element name="Nummer_2" type="xs:string" minOccurs="0"/>

                  <xs:element name="Nummer_3" type="xs:string" minOccurs="0"/>

                  <xs:element name="KvK-nummer" type="xs:string" minOccurs="0"/>

                  <xs:element name="D.U.N.S._nummer" type="xs:string" minOccurs="0"/>

                  <xs:element name="Postbusadres" type="xs:boolean" minOccurs="0"/>

                  <xs:element name="Toev._voor_straat" type="xs:string" minOccurs="0"/>

                  <xs:element name="Straat" type="xs:string" minOccurs="0"/>

                  <xs:element name="Huisnummer" type="xs:long" minOccurs="0"/>

                  <xs:element name="Toev._aan_huisnr." type="xs:string" minOccurs="0"/>

                  <xs:element name="Postcode" type="xs:string" minOccurs="0"/>

                  <xs:element name="Woonplaats" type="xs:string" minOccurs="0"/>

                  <xs:element name="Land" type="xs:string" minOccurs="0"/>

                  <xs:element name="Land_2" type="xs:string" minOccurs="0"/>

                  <xs:element name="Particulier" type="xs:boolean" minOccurs="0"/>

                  <xs:element name="Organisatie_persoon" type="xs:string" minOccurs="0"/>

                  <xs:element name="Klant_sinds__MORE_" type="xs:date" minOccurs="0"/>

                  <xs:element name="Actief" type="xs:boolean" minOccurs="0"/>

                  <xs:element name="Type_relatie" type="xs:string" minOccurs="0"/>

                  <xs:element name="Inzake_client" type="xs:boolean" minOccurs="0"/>

                  <xs:element name="Q-Relatie" type="xs:boolean" minOccurs="0"/>

                  <xs:element name="Voornaam" type="xs:string" minOccurs="0"/>

                  <xs:element name="Voorletters" type="xs:string" minOccurs="0"/>

                  <xs:element name="Voorvoegsel" type="xs:string" minOccurs="0"/>

                  <xs:element name="Achternaam" type="xs:string" minOccurs="0"/>

                  <xs:element name="Titel_aanhef" type="xs:string" minOccurs="0"/>

                  <xs:element name="Tekst_t.a.v._man" type="xs:string" minOccurs="0"/>

                  <xs:element name="Tekst_t.a.v._vrouw" type="xs:string" minOccurs="0"/>

                  <xs:element name="Tweede_titel" type="xs:string" minOccurs="0"/>

                  <xs:element name="Omschrijving" type="xs:string" minOccurs="0"/>

                  <xs:element name="Geslacht_code" type="xs:string" minOccurs="0"/>

                 </xs:sequence>

                </xs:complexType>

               </xs:element>

              </xs:choice>

             </xs:complexType>

            </xs:element>

           </xs:schema>

           <CRM_MORE_signal>

            <Nummer>1000096</Nummer>

            <Naam>HEMA</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>Nassaulaan</Straat>

            <Huisnummer>102</Huisnummer>

            <Postcode>5689 HZ</Postcode>

            <Woonplaats>Oirschot</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>false</Particulier>

            <Organisatie_persoon>Organisatie</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1000097</Nummer>

            <Naam>2XL</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>keerweg</Straat>

            <Huisnummer>90</Huisnummer>

            <Postcode>3089 AD</Postcode>

            <Woonplaats>Rotterdam</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>false</Particulier>

            <Organisatie_persoon>Organisatie</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1000110</Nummer>

            <Naam>Testorganisatie 1</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>Kalverstraat</Straat>

            <Huisnummer>1</Huisnummer>

            <Postcode>1065 XL</Postcode>

            <Woonplaats>Amsterdam</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>false</Particulier>

            <Organisatie_persoon>Organisatie</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1000111</Nummer>

            <Naam>Testorganisatie 2</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>Avenue des Champs Elysées</Straat>

            <Huisnummer>100</Huisnummer>

            <Postcode>123 AB</Postcode>

            <Woonplaats>Paris</Woonplaats>

            <Land>F</Land>

            <Land_2>Frankrijk</Land_2>

            <Particulier>false</Particulier>

            <Organisatie_persoon>Organisatie</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1000112</Nummer>

            <Naam>Proef Konijn</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Toev._voor_straat>Duinhuis</Toev._voor_straat>

            <Straat>Strandweg</Straat>

            <Huisnummer>1</Huisnummer>

            <Toev._aan_huisnr.>4 hoog</Toev._aan_huisnr.>

            <Postcode>1234 AA</Postcode>

            <Woonplaats>Zandvoort</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>true</Particulier>

            <Organisatie_persoon>Persoon</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Voornaam>Proef</Voornaam>

            <Voorletters>P.</Voorletters>

            <Achternaam>Konijn</Achternaam>

            <Titel_aanhef>202</Titel_aanhef>

            <Tekst_t.a.v._man>Dhr. mr. drs.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>Mw. mr. drs.</Tekst_t.a.v._vrouw>

            <Tweede_titel>600</Tweede_titel>

            <Omschrijving>Register accountant</Omschrijving>

            <Geslacht_code>M</Geslacht_code>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1019023</Nummer>

            <Naam>Jansen BV</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Toev._voor_straat>Toren 1</Toev._voor_straat>

            <Straat>Catharina van Reneslaan</Straat>

            <Huisnummer>53</Huisnummer>

            <Postcode>1187 HB</Postcode>

            <Woonplaats>Amstelveen</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>false</Particulier>

            <Organisatie_persoon>Organisatie</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1019024</Nummer>

            <Naam>Zach de Wir</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Particulier>true</Particulier>

            <Organisatie_persoon>Persoon</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Voornaam>Zach</Voornaam>

            <Voorletters>Z.</Voorletters>

            <Voorvoegsel>de</Voorvoegsel>

            <Achternaam>Wir</Achternaam>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

            <Geslacht_code>M</Geslacht_code>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1019029</Nummer>

            <Naam>Dun Rite Lawn Maintenance</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>Gording</Straat>

            <Huisnummer>29</Huisnummer>

            <Postcode>5406 CN</Postcode>

            <Woonplaats>Uden</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>false</Particulier>

            <Organisatie_persoon>Organisatie</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1019030</Nummer>

            <Naam>Cinthia van Gessel</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>Middenweg</Straat>

            <Huisnummer>113</Huisnummer>

            <Postcode>1619 BL</Postcode>

            <Woonplaats>Andijk</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>true</Particulier>

            <Organisatie_persoon>Persoon</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Voornaam>Cinthia</Voornaam>

            <Voorletters>C.</Voorletters>

            <Voorvoegsel>van</Voorvoegsel>

            <Achternaam>Gessel</Achternaam>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

            <Geslacht_code>V</Geslacht_code>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1019035</Nummer>

            <Naam>Robbert Sanders Photography</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>Rooseveltlaan</Straat>

            <Huisnummer>81</Huisnummer>

            <Toev._aan_huisnr.>2</Toev._aan_huisnr.>

            <Postcode>1079 AE</Postcode>

            <Woonplaats>Amsterdam</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>false</Particulier>

            <Organisatie_persoon>Organisatie</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1019037</Nummer>

            <Naam>TOMtec</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>Begijnenhof</Straat>

            <Huisnummer>3</Huisnummer>

            <Toev._aan_huisnr.>B</Toev._aan_huisnr.>

            <Postcode>5611 EK</Postcode>

            <Woonplaats>Eindhoven</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>false</Particulier>

            <Organisatie_persoon>Organisatie</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1019039</Nummer>

            <Naam>Jaap van den Boogaard</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>Koningsgracht</Straat>

            <Huisnummer>145</Huisnummer>

            <Toev._aan_huisnr.>A</Toev._aan_huisnr.>

            <Postcode>2715 AM</Postcode>

            <Woonplaats>Zoetermeer</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>true</Particulier>

            <Organisatie_persoon>Persoon</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Voornaam>Jaap</Voornaam>

            <Voorletters>J.</Voorletters>

            <Voorvoegsel>van den</Voorvoegsel>

            <Achternaam>Boogaard</Achternaam>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

            <Geslacht_code>M</Geslacht_code>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1019040</Nummer>

            <Naam>Company.Info B.V.</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>Prins Bernhardplein</Straat>

            <Huisnummer>173</Huisnummer>

            <Postcode>1097 BL</Postcode>

            <Woonplaats>Amsterdam</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>false</Particulier>

            <Organisatie_persoon>Organisatie</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1019041</Nummer>

            <Naam>Tineke Richteren</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>Kaaskenswater</Straat>

            <Huisnummer>19</Huisnummer>

            <Postcode>2715 BC</Postcode>

            <Woonplaats>Zoetermeer</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>true</Particulier>

            <Organisatie_persoon>Persoon</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Voornaam>Tineke</Voornaam>

            <Voorletters>T.A.</Voorletters>

            <Achternaam>Richteren</Achternaam>

            <Titel_aanhef>400</Titel_aanhef>

            <Tekst_t.a.v._man>Dhr. drs.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>Mw. drs.</Tekst_t.a.v._vrouw>

            <Geslacht_code>V</Geslacht_code>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1019042</Nummer>

            <Naam>BexelOrg</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>Graafschapstraat</Straat>

            <Huisnummer>48</Huisnummer>

            <Postcode>1079 PE</Postcode>

            <Woonplaats>Amsterdam</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>false</Particulier>

            <Organisatie_persoon>Organisatie</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1019043</Nummer>

            <Naam>Bryan Exel</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>Graafschapstraat</Straat>

            <Huisnummer>48</Huisnummer>

            <Postcode>1079 PE</Postcode>

            <Woonplaats>Amsterdam</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>true</Particulier>

            <Organisatie_persoon>Persoon</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Voornaam>Bryan</Voornaam>

            <Voorletters>B.A.</Voorletters>

            <Achternaam>Exel</Achternaam>

            <Titel_aanhef>ONB</Titel_aanhef>

            <Tekst_t.a.v._man>dhr./mevr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>dhr./mevr.</Tekst_t.a.v._vrouw>

            <Geslacht_code>M</Geslacht_code>

           </CRM_MORE_signal>

          </AfasGetConnector>

         </GetDataResult>

        </GetDataResponse>

      </soap:Body>

      </soap:Envelope>

      <?xml version="1.0" encoding="utf-8"?>

      <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

      <soap:Body>

        <GetDataResponse xmlns="urn:Afas.Profit.Services">

         <GetDataResult>

          <AfasGetConnector>

           <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

            <xs:element name="AfasGetConnector">

             <xs:complexType>

              <xs:choice maxOccurs="unbounded">

               <xs:element name="CRM_MORE_signal">

                <xs:complexType>

                 <xs:sequence>

                  <xs:element name="Nummer" type="xs:string" minOccurs="0"/>

                  <xs:element name="MORE_clientnummer" type="xs:string" minOccurs="0"/>

                  <xs:element name="Naam" type="xs:string" minOccurs="0"/>

                  <xs:element name="Nummer_2" type="xs:string" minOccurs="0"/>

                  <xs:element name="Nummer_3" type="xs:string" minOccurs="0"/>

                  <xs:element name="KvK-nummer" type="xs:string" minOccurs="0"/>

                  <xs:element name="D.U.N.S._nummer" type="xs:string" minOccurs="0"/>

                  <xs:element name="Postbusadres" type="xs:boolean" minOccurs="0"/>

                  <xs:element name="Toev._voor_straat" type="xs:string" minOccurs="0"/>

                  <xs:element name="Straat" type="xs:string" minOccurs="0"/>

                  <xs:element name="Huisnummer" type="xs:long" minOccurs="0"/>

                  <xs:element name="Toev._aan_huisnr." type="xs:string" minOccurs="0"/>

                  <xs:element name="Postcode" type="xs:string" minOccurs="0"/>

                  <xs:element name="Woonplaats" type="xs:string" minOccurs="0"/>

                  <xs:element name="Land" type="xs:string" minOccurs="0"/>

                  <xs:element name="Land_2" type="xs:string" minOccurs="0"/>

                  <xs:element name="Particulier" type="xs:boolean" minOccurs="0"/>

                  <xs:element name="Organisatie_persoon" type="xs:string" minOccurs="0"/>

                  <xs:element name="Klant_sinds__MORE_" type="xs:date" minOccurs="0"/>

                  <xs:element name="Actief" type="xs:boolean" minOccurs="0"/>

                  <xs:element name="Type_relatie" type="xs:string" minOccurs="0"/>

                  <xs:element name="Inzake_client" type="xs:boolean" minOccurs="0"/>

                  <xs:element name="Q-Relatie" type="xs:boolean" minOccurs="0"/>

                  <xs:element name="Voornaam" type="xs:string" minOccurs="0"/>

                  <xs:element name="Voorletters" type="xs:string" minOccurs="0"/>

                  <xs:element name="Voorvoegsel" type="xs:string" minOccurs="0"/>

                  <xs:element name="Achternaam" type="xs:string" minOccurs="0"/>

                  <xs:element name="Titel_aanhef" type="xs:string" minOccurs="0"/>

                  <xs:element name="Tekst_t.a.v._man" type="xs:string" minOccurs="0"/>

                  <xs:element name="Tekst_t.a.v._vrouw" type="xs:string" minOccurs="0"/>

                  <xs:element name="Tweede_titel" type="xs:string" minOccurs="0"/>

                  <xs:element name="Omschrijving" type="xs:string" minOccurs="0"/>

                  <xs:element name="Geslacht_code" type="xs:string" minOccurs="0"/>

                 </xs:sequence>

                </xs:complexType>

               </xs:element>

              </xs:choice>

             </xs:complexType>

            </xs:element>

           </xs:schema>

           <CRM_MORE_signal>

            <Nummer>1000096</Nummer>

            <Naam>HEMA</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>Nassaulaan</Straat>

            <Huisnummer>102</Huisnummer>

            <Postcode>5689 HZ</Postcode>

            <Woonplaats>Oirschot</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>false</Particulier>

            <Organisatie_persoon>Organisatie</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1000097</Nummer>

            <Naam>2XL</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>keerweg</Straat>

            <Huisnummer>90</Huisnummer>

            <Postcode>3089 AD</Postcode>

            <Woonplaats>Rotterdam</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>false</Particulier>

            <Organisatie_persoon>Organisatie</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1000110</Nummer>

            <Naam>Testorganisatie 1</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>Kalverstraat</Straat>

            <Huisnummer>1</Huisnummer>

            <Postcode>1065 XL</Postcode>

            <Woonplaats>Amsterdam</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>false</Particulier>

            <Organisatie_persoon>Organisatie</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1000111</Nummer>

            <Naam>Testorganisatie 2</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>Avenue des Champs Elysées</Straat>

            <Huisnummer>100</Huisnummer>

            <Postcode>123 AB</Postcode>

            <Woonplaats>Paris</Woonplaats>

            <Land>F</Land>

            <Land_2>Frankrijk</Land_2>

            <Particulier>false</Particulier>

            <Organisatie_persoon>Organisatie</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1000112</Nummer>

            <Naam>Proef Konijn</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Toev._voor_straat>Duinhuis</Toev._voor_straat>

            <Straat>Strandweg</Straat>

            <Huisnummer>1</Huisnummer>

            <Toev._aan_huisnr.>4 hoog</Toev._aan_huisnr.>

            <Postcode>1234 AA</Postcode>

            <Woonplaats>Zandvoort</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>true</Particulier>

            <Organisatie_persoon>Persoon</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Voornaam>Proef</Voornaam>

            <Voorletters>P.</Voorletters>

            <Achternaam>Konijn</Achternaam>

            <Titel_aanhef>202</Titel_aanhef>

            <Tekst_t.a.v._man>Dhr. mr. drs.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>Mw. mr. drs.</Tekst_t.a.v._vrouw>

            <Tweede_titel>600</Tweede_titel>

            <Omschrijving>Register accountant</Omschrijving>

            <Geslacht_code>M</Geslacht_code>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1019023</Nummer>

            <Naam>Jansen BV</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Toev._voor_straat>Toren 1</Toev._voor_straat>

            <Straat>Catharina van Reneslaan</Straat>

            <Huisnummer>53</Huisnummer>

            <Postcode>1187 HB</Postcode>

            <Woonplaats>Amstelveen</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>false</Particulier>

            <Organisatie_persoon>Organisatie</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1019024</Nummer>

            <Naam>Zach de Wir</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Particulier>true</Particulier>

            <Organisatie_persoon>Persoon</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Voornaam>Zach</Voornaam>

            <Voorletters>Z.</Voorletters>

            <Voorvoegsel>de</Voorvoegsel>

            <Achternaam>Wir</Achternaam>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

            <Geslacht_code>M</Geslacht_code>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1019029</Nummer>

            <Naam>Dun Rite Lawn Maintenance</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>Gording</Straat>

            <Huisnummer>29</Huisnummer>

            <Postcode>5406 CN</Postcode>

            <Woonplaats>Uden</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>false</Particulier>

            <Organisatie_persoon>Organisatie</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1019030</Nummer>

            <Naam>Cinthia van Gessel</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>Middenweg</Straat>

            <Huisnummer>113</Huisnummer>

            <Postcode>1619 BL</Postcode>

            <Woonplaats>Andijk</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>true</Particulier>

            <Organisatie_persoon>Persoon</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Voornaam>Cinthia</Voornaam>

            <Voorletters>C.</Voorletters>

            <Voorvoegsel>van</Voorvoegsel>

            <Achternaam>Gessel</Achternaam>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

            <Geslacht_code>V</Geslacht_code>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1019035</Nummer>

            <Naam>Robbert Sanders Photography</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>Rooseveltlaan</Straat>

            <Huisnummer>81</Huisnummer>

            <Toev._aan_huisnr.>2</Toev._aan_huisnr.>

            <Postcode>1079 AE</Postcode>

            <Woonplaats>Amsterdam</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>false</Particulier>

            <Organisatie_persoon>Organisatie</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1019037</Nummer>

            <Naam>TOMtec</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>Begijnenhof</Straat>

            <Huisnummer>3</Huisnummer>

            <Toev._aan_huisnr.>B</Toev._aan_huisnr.>

            <Postcode>5611 EK</Postcode>

            <Woonplaats>Eindhoven</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>false</Particulier>

            <Organisatie_persoon>Organisatie</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1019039</Nummer>

            <Naam>Jaap van den Boogaard</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>Koningsgracht</Straat>

            <Huisnummer>145</Huisnummer>

            <Toev._aan_huisnr.>A</Toev._aan_huisnr.>

            <Postcode>2715 AM</Postcode>

            <Woonplaats>Zoetermeer</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>true</Particulier>

            <Organisatie_persoon>Persoon</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Voornaam>Jaap</Voornaam>

            <Voorletters>J.</Voorletters>

            <Voorvoegsel>van den</Voorvoegsel>

            <Achternaam>Boogaard</Achternaam>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

            <Geslacht_code>M</Geslacht_code>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1019040</Nummer>

            <Naam>Company.Info B.V.</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>Prins Bernhardplein</Straat>

            <Huisnummer>173</Huisnummer>

            <Postcode>1097 BL</Postcode>

            <Woonplaats>Amsterdam</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>false</Particulier>

            <Organisatie_persoon>Organisatie</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1019041</Nummer>

            <Naam>Tineke Richteren</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>Kaaskenswater</Straat>

            <Huisnummer>19</Huisnummer>

            <Postcode>2715 BC</Postcode>

            <Woonplaats>Zoetermeer</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>true</Particulier>

            <Organisatie_persoon>Persoon</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Voornaam>Tineke</Voornaam>

            <Voorletters>T.A.</Voorletters>

            <Achternaam>Richteren</Achternaam>

            <Titel_aanhef>400</Titel_aanhef>

            <Tekst_t.a.v._man>Dhr. drs.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>Mw. drs.</Tekst_t.a.v._vrouw>

            <Geslacht_code>V</Geslacht_code>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1019042</Nummer>

            <Naam>BexelOrg</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>Graafschapstraat</Straat>

            <Huisnummer>48</Huisnummer>

            <Postcode>1079 PE</Postcode>

            <Woonplaats>Amsterdam</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>false</Particulier>

            <Organisatie_persoon>Organisatie</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Titel_aanhef>ALG</Titel_aanhef>

            <Tekst_t.a.v._man>dhr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>mevr.</Tekst_t.a.v._vrouw>

           </CRM_MORE_signal>

           <CRM_MORE_signal>

            <Nummer>1019043</Nummer>

            <Naam>Bryan Exel</Naam>

            <Nummer_2>1000036</Nummer_2>

            <Postbusadres>false</Postbusadres>

            <Straat>Graafschapstraat</Straat>

            <Huisnummer>48</Huisnummer>

            <Postcode>1079 PE</Postcode>

            <Woonplaats>Amsterdam</Woonplaats>

            <Land>NL</Land>

            <Land_2>Nederland</Land_2>

            <Particulier>true</Particulier>

            <Organisatie_persoon>Persoon</Organisatie_persoon>

            <Actief>true</Actief>

            <Inzake_client>false</Inzake_client>

            <Q-Relatie>false</Q-Relatie>

            <Voornaam>Bryan</Voornaam>

            <Voorletters>B.A.</Voorletters>

            <Achternaam>Exel</Achternaam>

            <Titel_aanhef>ONB</Titel_aanhef>

            <Tekst_t.a.v._man>dhr./mevr.</Tekst_t.a.v._man>

            <Tekst_t.a.v._vrouw>dhr./mevr.</Tekst_t.a.v._vrouw>

            <Geslacht_code>M</Geslacht_code>

           </CRM_MORE_signal>

          </AfasGetConnector>

         </GetDataResult>

        </GetDataResponse>

      </soap:Body>

      </soap:Envelope>

       

      Can some one help how to get data for each field and store in the table.

       

      Kind Regards,

        • 1. Re: Need help in getting data out of SOAP Envelope response from Web Service
          user587700

          I am now posting the complete code I am using to get the XML from the web service and XML Processing.

          SET DEFINE OFF;

          SET TERMOUT OFF;

          SET SERVEROUTPUT ON SIZE UNLIMITED

          SET LINES 400

          --SPOOL TEST.lis

          --ALTER SESSION SET NLS_LANGUAGE='American';

          --ALTER SESSION SET tracefile_identifier='10046';

          --ALTER SESSION SET timed_statistics = TRUE;

          --ALTER SESSION SET statistics_level=all;

          --ALTER SESSION SET max_dump_file_size = unlimited;

          --ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';

           

          DECLARE

             errbuf                  VARCHAR2 (2000);

             retcode                 NUMBER;

             -- Private Global variables

             --

             G_PROG_VERS    CONSTANT VARCHAR2 (100) := '115.10.04.128';

             G_PACKAGE      CONSTANT VARCHAR2 (100) := 'XX_MB_CPM_CRM_INTERFACE';

             g_sepline      CONSTANT VARCHAR2 (100)

                := '+---------------------------------------------------------------------------+' ;

             l_url                   VARCHAR2 (200)

                :=    'http://'

                   || fnd_profile.VALUE ('XX_MB_AFAS_CRM_SERVER')

                   || '/ProfitServices/GetConnector.asmx?wsdl'; -- SOAP REQUESTS/RESPONSE

             l_soap_request          VARCHAR2 (8000);

             l_resp_text             VARCHAR2 (32767);

             l_resp_clob             CLOB;

             l_filter_xml            VARCHAR2 (4000);

             l_ph_from               NUMBER;

             l_ph_from_str           VARCHAR2 (6);

             l_increment             NUMBER := 10;

             l_max_empno             NUMBER;

             l_records               NUMBER := 0;

             l_total_records         NUMBER := 0;

             l_process_it            NUMBER := 0;

           

             --

             -- Cursor to get Max Employee Number

             --

             CURSOR c_max_emp

             IS

                SELECT TO_NUMBER (MAX (employee_number)) max_empno

                  FROM per_people_x

                 WHERE employee_number != '009999';

           

             -- HTTP REQUEST/RESPONSE

             l_request               UTL_HTTP.req;

             l_resp                  UTL_HTTP.resp;

             --

             -- XML Parsing of the Response

             --

             indomdoc                DBMS_XMLDOM.domdocument;

             myparser                DBMS_XMLPARSER.parser;

             l_nodelist              DBMS_XMLDOM.domnodelist;

             --

             -- Field Values from XML Document

             --

             l_crm_cust_id           VARCHAR2 (360);

             l_customer_number       VARCHAR2 (360);

             l_customer_name         VARCHAR2 (360);

             l_client_ph1            VARCHAR2 (360);

             l_client_ph2            VARCHAR2 (360);

             l_kvk_number            VARCHAR2 (360);

             l_duns_number           VARCHAR2 (360);

             l_po_box_adrs           VARCHAR2 (360);

             l_street_prefix         VARCHAR2 (360);

             l_street                VARCHAR2 (360);

             l_house_number          VARCHAR2 (360);

             l_house_number_suffix   VARCHAR2 (360);

             l_post_code             VARCHAR2 (360);

             l_city                  VARCHAR2 (360);

             l_country_code          VARCHAR2 (360);

             l_country               VARCHAR2 (360);

             l_customer_type         VARCHAR2 (360);

             l_customer_status       VARCHAR2 (360);

             l_inzake_customer       VARCHAR2 (360);

             l_q_relation            VARCHAR2 (360);

             l_target                VARCHAR2 (360);

             l_first_name            VARCHAR2 (360);

             l_initials              VARCHAR2 (360);

             l_person_name_suffix    VARCHAR2 (360);

             l_last_name             VARCHAR2 (360);

             l_gender                VARCHAR2 (360);

             l_afas_title_code       VARCHAR2 (360);

             l_title_text_m          VARCHAR2 (360);

             l_title_text_f          VARCHAR2 (360);

             l_academic_title_code   VARCHAR2 (360);

             l_academic_title        VARCHAR2 (360);

             --

             -- Get Concurrent request ID if called from a concurrent program

             --

             l_conc_request_id       NUMBER := 1;          --fnd_global.conc_request_id;

           

             -- =====================================================================

             -- Private Procedures and functions

             -- =====================================================================

             PROCEDURE log_message (p_message IN VARCHAR2)

             IS

             BEGIN

                --fnd_file.put_line (fnd_file.LOG, p_message);

                DBMS_OUTPUT.put_line (p_message);

             END log_message;

           

             PROCEDURE debug_message (p_message IN VARCHAR2)

             IS

             BEGIN

                IF NVL (fnd_profile.VALUE ('PA_DEBUG_MODE'), 'N') = 'Y'

                THEN

                   --fnd_file.put_line (fnd_file.LOG, p_message);

                   DBMS_OUTPUT.put_line (p_message);

                END IF;

             END debug_message;

           

             FUNCTION get_field_value (p_indomdoc     IN DBMS_XMLDOM.domdocument,

                                       p_field_name   IN VARCHAR2,

                                       p_index        IN NUMBER)

                RETURN VARCHAR2

             IS

                l_nodelist_level2   DBMS_XMLDOM.domnodelist;

                l_field_node        DBMS_XMLDOM.domnode;

                l_field_value       VARCHAR2 (360);

             BEGIN

                l_field_value := TO_CHAR (NULL);

                l_nodelist_level2 :=

                   DBMS_XMLDOM.getelementsbytagname (p_indomdoc, p_field_name);

                l_field_node := DBMS_XMLDOM.item (l_nodelist_level2, p_index);

                l_field_value :=

                   DBMS_XMLDOM.getnodevalue (DBMS_XMLDOM.getfirstchild (l_field_node));

                DBMS_XMLDOM.freenode (l_field_node);

                DBMS_XMLDOM.freenodelist (l_nodelist_level2);

                log_message (p_field_name || ' = ' || NVL (l_field_value, 'NULL'));

                RETURN l_field_value;

             END get_field_value;

          BEGIN

             errbuf := TO_CHAR (NULL);                                  -- No errors yet

             retcode := 0;                                        -- Success until error

           

             IF l_conc_request_id != -1             -- If called from Concurrent request

             THEN                                                  -- write log messages

                xx_mb_utils.logfile_timestamp;

                log_message (g_sepline);

                log_message (

                      'Start '

                   || g_package

                   || '.populate_local_copy (Version: '

                   || g_prog_vers

                   || ')');

             END IF;

           

             OPEN c_max_emp;

           

             FETCH c_max_emp INTO l_max_empno;

           

             IF c_max_emp%NOTFOUND

             THEN

                l_max_empno := 9900;

             END IF;

           

             CLOSE c_max_emp;

           

             --

             -- Loop get Connector 10 PH customers per itteration

             --

             l_ph_from := 0;                                      -- We start PH 1 0001?

             l_increment := 10;                                        -- Can be 1 or 10

           

             WHILE l_ph_from = 0                         -- <= l_max_empno + l_increment

             LOOP

                l_process_it := 1;

           

                BEGIN

                   IF l_ph_from = 0 -- PH 0 i.e., first all records from AFAS which are not in MOrE

                   THEN

                      log_message (g_sepline);

                      xx_mb_utils.logfile_timestamp;

                      log_message ('Get AFAS CRM Records without MOrE Customer number');

                      log_message (g_sepline);

                      --

                      -- Prepare filter XML for this itteration

                      --

                      l_filter_xml :=

                            '<?xml version="1.0"?><Filters><Filter FilterId="1"><Field FieldId="MORE_clientnummer" OperatorType="8"/>'

                         || '</Filter></Filters>';

                   ELSE

                      IF l_increment = 1

                      THEN

                         l_ph_from_str := TRIM (TO_CHAR (l_ph_from, '0000')); -- 1 PH Filter String

                      ELSIF l_increment = 10

                      THEN

                         l_ph_from_str :=

                            SUBSTR (TRIM (TO_CHAR (l_ph_from, '0000')), 1, 3) || '%'; -- 10 PH Filter

                      ELSE

                         l_increment := 1;

                         l_ph_from_str := TRIM (TO_CHAR (l_ph_from, '0000')); -- 1 PH Filter String

                         log_message ('Set increment to 1');

                      END IF;

           

                      log_message (g_sepline);

                      xx_mb_utils.logfile_timestamp;

                      log_message (

                            'Get AFAS CRM Records for PH 1 from: '

                         || TO_CHAR (l_ph_from)

                         || ' upto: '

                         || TO_CHAR (l_ph_from + l_increment - 1)

                         || ' Begins with filter condition: '

                         || l_ph_from_str);

                      log_message (g_sepline);

           

                      --

                      -- Prepare filter XML for this itteration

                      IF l_increment = 1

                      THEN

                         l_filter_xml :=

                               '<?xml version="1.0"?><Filters><Filter FilterId="1"><Field FieldId="Nummer_2" OperatorType="1">'

                            || l_ph_from_str

                            || '</Field><Field FieldId="MORE_clientnummer" OperatorType="9"/></Filter></Filters>'; -- 1 PH Filter

                      ELSE

                         l_filter_xml :=

                               '<?xml version="1.0"?><Filters><Filter FilterId="1"><Field FieldId="Nummer_2" OperatorType="10">'

                            || l_ph_from_str

                            || '</Field><Field FieldId="MORE_clientnummer" OperatorType="9"/></Filter></Filters>'; -- 10 or PH Filter

                      END IF;

                   END IF;                                   -- If l_ph_from is 0 or not

           

                   l_records := 0;

           

                   --DELETE FROM xx_mb_cpm_crm_cust_tmp;  -- Delete Session temporay table

           

                   --

                   -- Create SOAP request via HTTP

                   --

                   l_soap_request :=

                         '<?xml version="1.0" encoding="utf-8"?>'

                      || '<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">'

                      || '  <soap:Body>'

                      || '     <GetData xmlns="urn:Afas.Profit.Services">'

                      || '        <environmentId>CRM</environmentId>'

                      || '        <userId>MEIJBURGTRANSFER</userId>'

                      || '        <password></password>'

                      || '        <logonAs></logonAs>'

                      || '        <connectorId>CRM_MORE_signal</connectorId>'

                      || '        <filtersXml><![CDATA['

                      || l_filter_xml

                      || ']]></filtersXml>'

                      || '     </GetData>'

                      || '  </soap:Body>'

                      || '</soap:Envelope>';

           

                   IF l_conc_request_id != -1       -- If called from Concurrent request

                   THEN                                            -- write log messages

                      xx_mb_utils.logfile_timestamp;

                      log_message (g_sepline);

                      log_message ('Call UTL_HTTP.bigin_request');

                   END IF;

           

                   l_request := UTL_HTTP.begin_request (url => l_url, method => 'POST');

                   UTL_HTTP.set_header (l_request,

                                        'Content-Type',

                                        'text/xml; charset=utf-8');

                   UTL_HTTP.set_header (l_request,

                                        'Content-Length',

                                        LENGTH (l_soap_request));

                   UTL_HTTP.set_header (l_request,

                                        'SOAPAction',

                                        'urn:Afas.Profit.Services/GetData');

                   UTL_HTTP.write_text (l_request, l_soap_request);

           

                   --

                   -- Invoke Request and get Response.

                   --

                   IF l_conc_request_id != -1       -- If called from Concurrent request

                   THEN                                            -- write log messages

                      xx_mb_utils.logfile_timestamp;

                      log_message (g_sepline);

                      log_message ('Call UTL_HTTP.get_response');

                   END IF;

           

                   l_resp := UTL_HTTP.get_response (l_request);

           

                   IF l_conc_request_id != -1       -- If called from Concurrent request

                   THEN                                            -- write log messages

                      xx_mb_utils.logfile_timestamp;

                      log_message (g_sepline);

                      log_message ('HTTP Response Status Code: ' || l_resp.status_code);

                      log_message (

                         'HTTP Response Reason Phrase: ' || l_resp.reason_phrase);

                   END IF;

           

                   IF l_resp.status_code = UTL_HTTP.HTTP_OK                        --200

                   THEN                                           -- If response OK then

                      DBMS_LOB.createtemporary (l_resp_clob, FALSE);

           

                      -- Read and Copy the response into the CLOB.

                      BEGIN

                         LOOP

                            l_resp_text := '';

                            UTL_HTTP.read_text (l_resp, l_resp_text, 32766);

                            log_message (

                               REPLACE (

                                  REPLACE (REPLACE (l_resp_text, '&gt;', '>'),

                                           '&lt;',

                                           '<'),

                                  '&amp;amp;',

                                  '&amp;'));

                            --debug_message (l_resp_text);

                            DBMS_LOB.writeappend (l_resp_clob,

                                                  LENGTH (l_resp_text),

                                                  l_resp_text);

                         END LOOP;

                      EXCEPTION

                         WHEN UTL_HTTP.end_of_body

                         THEN

                            UTL_HTTP.end_response (l_resp);

                         WHEN OTHERS

                         THEN

                            UTL_HTTP.end_response (l_resp);

                            log_message (

                               'Failed to get data for ph: ' || l_ph_from_str);

                            log_message ('ERROR: ' || SQLCODE || ' ' || SQLERRM);

                            l_process_it := 0;

                            log_message ('Resopnse Text');

                            log_message (g_sepline);

                            log_message (l_resp_text);

                            log_message (g_sepline);

                      END;

           

                      IF l_process_it = 1

                      THEN

                         --

                         -- Parse Output from Soap xml response

                         --

                         myparser := DBMS_XMLPARSER.newparser;

                         DBMS_XMLPARSER.parseclob (

                            myparser,

                            REPLACE (

                               REPLACE (REPLACE (l_resp_clob, '&gt;', '>'),

                                        '&lt;',

                                        '<'),

                               '&amp;amp;',

                               '&amp;'));

                         DBMS_OUTPUT.put_line (

                            REPLACE (

                               REPLACE (REPLACE (l_resp_clob, '&gt;', '>'),

                                        '&lt;',

                                        '<'),

                               '&amp;amp;',

                               '&amp;'));

                         indomdoc := DBMS_XMLPARSER.getdocument (myparser);

                         l_nodelist :=

                            DBMS_XMLDOM.getelementsbytagname (indomdoc,

                                                              'CRM_MORE_signal'); -- This doesn't care about namespaces

           

                         FOR i IN 0 .. DBMS_XMLDOM.getlength (l_nodelist) - 1

                         LOOP

                            l_crm_cust_id := get_field_value (indomdoc, 'Nummer', i); -- Get Value for Field Nummer

                            l_customer_number :=

                               get_field_value (indomdoc, 'MORE_clientnummer', i); -- Get Value for Field MORE_clientnummer

                            l_customer_name := get_field_value (indomdoc, 'Naam', i); -- Get Value for Field Naam

                            l_client_ph1 := get_field_value (indomdoc, 'Nummer_2', i); -- Get Value for Field Nummer_2

                            l_client_ph2 := get_field_value (indomdoc, 'Nummer_3', i); -- Get Value for Field Nummer_3

                            l_kvk_number := get_field_value (indomdoc, 'KvK-nummer', i); -- Get Value for Field KvK-nummer

                            l_duns_number :=

                               get_field_value (indomdoc, 'D.U.N.S._nummer', i); -- Get Value for Field D.U.N.S._nummer

                            l_po_box_adrs :=

                               get_field_value (indomdoc, 'Postbusadres', i); -- Get Value for Field Postbusadres

                            l_street_prefix :=

                               get_field_value (indomdoc, 'Toev._voor_straat', i); -- Get Value for Field Toev._voor_straat

                            l_street := get_field_value (indomdoc, 'Straat', i); -- Get Value for Field Straat

                            l_house_number :=

                               get_field_value (indomdoc, 'Huisnummer', i); -- Get Value for Field Huisnummer

                            l_house_number_suffix :=

                               get_field_value (indomdoc, 'Toev._aan_huisnr.', i); -- Get Value for Field Toev._aan_huisnr.

                            l_post_code := get_field_value (indomdoc, 'Postcode', i); -- Get Value for Field Postcode

                            l_city := get_field_value (indomdoc, 'Woonplaats', i); -- Get Value for Field Woonplaats

                            l_country_code := get_field_value (indomdoc, 'Land', i); -- Get Value for Field Land

                            l_country := get_field_value (indomdoc, 'Land_2', i); -- Get Value for Field Land_2

                            l_customer_type :=

                               get_field_value (indomdoc, 'Organisatie_persoon', i); -- Get Value for Field Organisatie_persoon

                            l_customer_status := get_field_value (indomdoc, 'Actief', i); -- Get Value for Field Actief

                            l_inzake_customer :=

                               get_field_value (indomdoc, 'Inzake_client', i); -- Get Value for Field Inzake_client

                            l_q_relation := get_field_value (indomdoc, 'Q-Relatie', i); -- Get Value for Field Q-Relatie

                            l_target := get_field_value (indomdoc, 'Type_relatie', i); -- Get Value for Field Type_relatie

                            l_first_name := get_field_value (indomdoc, 'Voornaam', i); -- Get Value for Field Voornaam

                            l_initials := get_field_value (indomdoc, 'Voorletters', i); -- Get Value for Field Voorletters

                            l_person_name_suffix :=

                               get_field_value (indomdoc, 'Voorvoegsel', i); -- Get Value for Field Voorvoegsel

                            l_last_name := get_field_value (indomdoc, 'Achternaam', i); -- Get Value for Field Achternaam

                            l_gender := get_field_value (indomdoc, 'Geslacht_code', i); -- Get Value for Field Geslacht_code

                            l_afas_title_code :=

                               get_field_value (indomdoc, 'Titel_aanhef', i); -- Get Value for Field Titel_aanhef

                            l_title_text_m :=

                               get_field_value (indomdoc, 'Tekst_t.a.v._man', i); -- Get Value for Field Tekst_t.a.v._man

                            l_title_text_f :=

                               get_field_value (indomdoc, 'Tekst_t.a.v._vrouw', i); -- Get Value for Field Tekst_t.a.v._vrouw

                            l_academic_title_code :=

                               get_field_value (indomdoc, 'Tweede_titel', i); -- Get Value for Field Tweede_titel

                            l_academic_title :=

                               get_field_value (indomdoc, 'Omschrijving', i); -- Get Value for Field Omschrijving

           

                            --

                            -- Insert record in the session temporary table

                            --

                            --

                            -- Insert record in the session temporary table

                            --

                            INSERT INTO xx_mb_cpm_crm_cust_tmp (crm_cust_id,

                                                                customer_number,

                                                                customer_name,

                                                                client_ph1,

                                                                client_ph2,

                                                                kvk_number,

                                                                duns_number,

                                                                po_box_adrs,

                                                                street_prefix,

                                                                street,

                                                                house_number,

                                                                house_number_suffix,

                                                                post_code,

                                                                city,

                                                                country_code,

                                                                country,

                                                                customer_type,

                                                                customer_status,

                                                                inzake_customer,

                                                                q_relation,

                                                                target,

                                                                first_name,

                                                                initials,

                                                                person_name_suffix,

                                                                last_name,

                                                                gender,

                                                                afas_title_code,

                                                                title_text,

                                                                academic_title_code,

                                                                academic_title)

                                    VALUES (

                                              UTL_I18N.unescape_reference (

                                                 l_crm_cust_id),

                                              UTL_I18N.unescape_reference (

                                                 l_customer_number),

                                              UTL_I18N.unescape_reference (

                                                 l_customer_name),

                                              UTL_I18N.unescape_reference (

                                                 l_client_ph1),

                                              UTL_I18N.unescape_reference (

                                                 l_client_ph2),

                                              UTL_I18N.unescape_reference (

                                                 l_kvk_number),

                                              UTL_I18N.unescape_reference (

                                                 l_duns_number),

                                              UTL_I18N.unescape_reference (

                                                 l_po_box_adrs),

                                              UTL_I18N.unescape_reference (

                                                 l_street_prefix),

                                              UTL_I18N.unescape_reference (l_street),

                                              UTL_I18N.unescape_reference (

                                                 l_house_number),

                                              UTL_I18N.unescape_reference (

                                                 l_house_number_suffix),

                                              UTL_I18N.unescape_reference (l_post_code),

                                              UTL_I18N.unescape_reference (l_city),

                                              UTL_I18N.unescape_reference (

                                                 l_country_code),

                                              UTL_I18N.unescape_reference (l_country),

                                              UTL_I18N.unescape_reference (

                                                 l_customer_type),

                                              DECODE (

                                                 NVL (

                                                    UTL_I18N.unescape_reference (

                                                       l_customer_status),

                                                    'true'),

                                                 'true', 'Actief',

                                                 'Inactief'),

                                              UTL_I18N.unescape_reference (

                                                 DECODE (

                                                    NVL (l_inzake_customer, 'false'),

                                                    'true', 'Ja',

                                                    'Nee')),

                                              UTL_I18N.unescape_reference (

                                                 DECODE (NVL (l_q_relation, 'false'),

                                                         'true', 'Ja',

                                                         'Nee')),

                                              DECODE (

                                                 NVL (

                                                    UTL_I18N.unescape_reference (

                                                       l_target),

                                                    '001'),

                                                 '001', 'Nee',

                                                 'Ja'),

                                              DECODE (

                                                 l_customer_type,

                                                 'Organisatie', TO_CHAR (NULL),

                                                 UTL_I18N.unescape_reference (

                                                    l_first_name)),

                                              DECODE (

                                                 l_customer_type,

                                                 'Organisatie', TO_CHAR (NULL),

                                                 UTL_I18N.unescape_reference (

                                                    l_initials)),

                                              DECODE (

                                                 l_customer_type,

                                                 'Organisatie', TO_CHAR (NULL),

                                                 UTL_I18N.unescape_reference (

                                                    l_person_name_suffix)),

                                              DECODE (

                                                 l_customer_type,

                                                 'Organisatie', TO_CHAR (NULL),

                                                 UTL_I18N.unescape_reference (

                                                    l_last_name)),

                                              DECODE (

                                                 l_customer_type,

                                                 'Organisatie', TO_CHAR (NULL),

                                                 UTL_I18N.unescape_reference (l_gender)),

                                              DECODE (

                                                 l_customer_type,

                                                 'Organisatie', TO_CHAR (NULL),

                                                 DECODE (

                                                    l_gender,

                                                    'M', UTL_I18N.unescape_reference (

                                                            l_afas_title_code),

                                                    'V', UTL_I18N.unescape_reference (

                                                            l_afas_title_code),

                                                    TO_CHAR (NULL))),

                                              DECODE (

                                                 l_customer_type,

                                                 'Organisatie', TO_CHAR (NULL),

                                                 DECODE (

                                                    l_gender,

                                                    'M', UTL_I18N.unescape_reference (

                                                            l_title_text_m),

                                                    'V', UTL_I18N.unescape_reference (

                                                            l_title_text_f),

                                                    TO_CHAR (NULL))),

                                              DECODE (

                                                 l_customer_type,

                                                 'Organisatie', TO_CHAR (NULL),

                                                 UTL_I18N.unescape_reference (

                                                    l_academic_title_code)),

                                              DECODE (

                                                 l_customer_type,

                                                 'Organisatie', TO_CHAR (NULL),

                                                 UTL_I18N.unescape_reference (

                                                    l_academic_title)));

           

                            l_records := l_records + 1;

                            l_total_records := l_total_records + 1;

                            COMMIT;

                         END LOOP;

           

                         DBMS_XMLDOM.freenodelist (l_nodelist); -- Free resources associated with this node list

                         DBMS_XMLDOM.freedocument (indomdoc);

                         DBMS_XMLPARSER.freeparser (myparser);

                         -- Relase the resources associated with the temporary LOB.

                         DBMS_LOB.freetemporary (l_resp_clob);

                         --               MERGE INTO xx_mb_cpm_crm_customers mcs

                         --                    USING (SELECT crm_cust_id,

                         --                                  customer_number,

                         --                                  customer_name,

                         --                                  client_ph1,

                         --                                  client_ph2,

                         --                                  kvk_number,

                         --                                  duns_number,

                         --                                  po_box_adrs,

                         --                                  street_prefix,

                         --                                  street,

                         --                                  house_number,

                         --                                  house_number_suffix,

                         --                                  post_code,

                         --                                  city,

                         --                                  country_code,

                         --                                  country,

                         --                                  customer_type,

                         --                                  customer_status,

                         --                                  inzake_customer,

                         --                                  q_relation,

                         --                                  target

                         --                             FROM xx_mb_cpm_crm_cust_tmp) acs

                         --                       ON (acs.crm_cust_id = mcs.crm_cust_id)

                         --               WHEN MATCHED

                         --               THEN

                         --                  UPDATE SET mcs.customer_number = acs.customer_number

                         --               WHEN NOT MATCHED

                         --               THEN

                         --                  INSERT     (crm_cust_id,

                         --                              customer_number,

                         --                              customer_name,

                         --                              client_ph1,

                         --                              client_ph2,

                         --                              kvk_number,

                         --                              duns_number,

                         --                              po_box_adrs,

                         --                              street_prefix,

                         --                              street,

                         --                              house_number,

                         --                              house_number_suffix,

                         --                              post_code,

                         --                              city,

                         --                              country_code,

                         --                              country,

                         --                              customer_type,

                         --                              customer_status,

                         --                              inzake_customer,

                         --                              q_relation,

                         --                              target)

                         --                      VALUES (acs.crm_cust_id,

                         --                              acs.customer_number,

                         --                              acs.customer_name,

                         --                              acs.client_ph1,

                         --                              acs.client_ph2,

                         --                              acs.kvk_number,

                         --                              acs.duns_number,

                         --                              acs.po_box_adrs,

                         --                              acs.street_prefix,

                         --                              acs.street,

                         --                              acs.house_number,

                         --                              acs.house_number_suffix,

                         --                              acs.post_code,

                         --                              acs.city,

                         --                              acs.country_code,

                         --                              acs.country,

                         --                              acs.customer_type,

                         --                              acs.customer_status,

                         --                              acs.inzake_customer,

                         --                              acs.q_relation,

                         --                              acs.target);

                         COMMIT;

                      END IF;                                       -- End IF process_it

                   ELSE

                      IF l_conc_request_id != -1    -- If called from Concurrent request

                      THEN                                         -- write log messages

                         log_message ('HTTP request failed.');

                         xx_mb_utils.logfile_timestamp;

                         log_message (g_sepline);

                         retcode := 1;

                         errbuf :=

                               'HTTP Request to AFAS Server failed with code: '

                            || l_resp.status_code;

                         UTL_HTTP.read_text (l_resp, l_resp_text, 32766);

                         log_message (g_sepline);

                         log_message ('Further details from AFAS:');

                         log_message (g_sepline);

                         log_message (l_resp_text);

                         log_message (g_sepline);

                      END IF;

           

                      UTL_HTTP.end_response (l_resp);

                   END IF;                                       -- If HTTP response 200

           

                   IF l_ph_from = 0 -- PH 0 i.e., first all records from AFAS which are not in MOrE

                   THEN

                      log_message (g_sepline);

                      xx_mb_utils.logfile_timestamp;

                      log_message ('Records processed: ' || TO_CHAR (l_records));

                      log_message (

                         'Done AFAS CRM Records without MOrE Customer number');

                   ELSE

                      log_message (g_sepline);

                      xx_mb_utils.logfile_timestamp;

                      log_message ('Records processed: ' || TO_CHAR (l_records));

                      log_message (

                            'Done AFAS CRM Records for PH 1 from: '

                         || TO_CHAR (l_ph_from)

                         || ' upto: '

                         || TO_CHAR (l_ph_from + l_increment - 1));

                   END IF;

           

                   l_ph_from := l_ph_from + 10;                               -- Next PH

                EXCEPTION

                   WHEN OTHERS

                   THEN

                      log_message ('Exception getting data for this PH range');

                      log_message (SQLCODE - SQLERRM);

                      log_message ('Continuing for next range');

                END;

             END LOOP;                                  -- End loop while not last_done;

           

             IF l_conc_request_id != -1             -- If called from Concurrent request

             THEN                                                  -- write log messages

                xx_mb_utils.logfile_timestamp;

                log_message ('Total Records processed: ' || TO_CHAR (l_total_records));

                log_message ('End ' || g_package || '.populate_local_copy');

                log_message (g_sepline);

             END IF;

          EXCEPTION

             WHEN UTL_HTTP.end_of_body

             THEN

                UTL_HTTP.end_response (l_resp);

                log_message ('Closing connection in exception');

                retcode := 2;

                errbuf := 'UTL_HTTP.end_of_body exception in populate_local_copy';

                xx_mb_utils.logfile_timestamp;

                log_message (g_sepline);

             WHEN OTHERS

             THEN

                UTL_HTTP.end_response (l_resp);

                log_message ('Closing connection in exception');

                retcode := 2;

                log_message ('ERROR: ' || SQLCODE || ' ' || SQLERRM);

                xx_mb_utils.logfile_timestamp;

                log_message (g_sepline);

          END;

          /

           

          EXIT;

          • 2. Re: Need help in getting data out of SOAP Envelope response from Web Service
            odie_63

            Hi,

             

            Please read and answer the following questions as clearly as possible :

             

            - What's your database version ? (SELECT * FROM v$version)

             

            - You've posted two copies of the same XML message, is it a mistake?

             

            - Why are you using this :

            REPLACE (

                                 REPLACE (REPLACE (l_resp_clob, '&gt;', '>'),

                                          '&lt;',

                                          '<'),

                                 '&amp;amp;',

                                 '&amp;'))

            or this :

            UTL_I18N.unescape_reference

            ?

             

            Both of those immediately make me think you're not dealing with XML correctly.

            • 3. Re: Need help in getting data out of SOAP Envelope response from Web Service
              user587700

              Hi,

              Thanks for the quick response.

               

              The XML I am receiving is from AFAS CRM ( A local dutch company)'s  Get Connector web service which, provides the XML with < and > also escaped as in HTML!!,  and therefore, before passing it to XML parser I need to replace tham.

              UTL_I18N.unescape_reference is used to unescape (i.e., translate back to UTF8 character for those special European Characters ).

              I am using Oracle 11g R2 Patchset 3 (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 64 bit).

               

              I have now found a much simple solution which works - By listing all nodes in the document and then looping over with each required XML tag and breaking on Record tag and storing it into a PL/SQL table and then loop thru the PL/SQL table to identify records and the optional fields within.

               

              See the code:

              ------------------------------------------------------------------------------

                 --

                 -- Procedure to get table of nodes and values from an XML document

                 --

                 PROCEDURE get_xml_elements (

                    p_xmldoc                      DBMS_XMLDOM.DOMDocument,

                    x_node_and_value_tab   IN OUT xml_node_and_value_tab)

                 IS

                    node_list   DBMS_XMLDOM.DOMNodeList;

                    node        DBMS_XMLDOM.DOMNode;

                    len         NUMBER;

                    idx         NUMBER := 0;

                 BEGIN

                    x_node_and_value_tab.delete;                              -- Clear Table

                    -- get all elements

                    node_list := DBMS_XMLDOM.getElementsByTagName (p_xmldoc, '*');

                    len := DBMS_XMLDOM.getLength (node_list);

               

                    FOR i IN 0 .. len - 1                           -- Loop for all elements

                    LOOP

                       idx := idx + 1;

                       node := DBMS_XMLDOM.item (node_list, i);

                       x_node_and_value_tab (idx).node_name :=

                          DBMS_XMLDOM.getNodeName (node);

                       x_node_and_value_tab (idx).node_value :=

                          DBMS_XMLDOM.getNodeValue (DBMS_XMLDOM.getFirstChild (node));

                    END LOOP;

                 END get_xml_elements;

               

              ------------------------------------------------------------------------------

               

              I know it is not a nice solution, but it works and due to the deadline of the project, i.e., 9th June 2014 I cannot wait.

              • 4. Re: Need help in getting data out of SOAP Envelope response from Web Service
                odie_63

                The XML I am receiving is from AFAS CRM ( A local dutch company)'s  Get Connector web service which, provides the XML with < and > also escaped as in HTML!!,  and therefore, before passing it to XML parser I need to replace tham.

                You mean the whole XML message (starting at <soap:Envelope>) is escaped like this or just a part of it?

                Anyway, DBMS_XMLGEN.CONVERT can deal with this situation in a single call (see example below).

                 

                If you want a nicer solution then do not use that tedious DOM approach at all but XMLTABLE instead :

                l_resp_clob := dbms_xmlgen.convert(l_resp_clob, dbms_xmlgen.ENTITY_DECODE);

                Then, for the first fields :

                select x.*

                from xmltable(

                       xmlnamespaces(

                         'http://schemas.xmlsoap.org/soap/envelope/' as "s"

                       , default 'urn:Afas.Profit.Services'

                       )

                     , '/s:Envelope/s:Body/GetDataResponse/GetDataResult/AfasGetConnector/CRM_MORE_signal'

                       passing xmlparse(document l_resp_clob)

                       columns Nummer       number       path 'Nummer'

                             , Naam         varchar2(30) path 'Naam'

                             , Nummer_2     number       path 'Nummer_2'

                             , Postbusadres varchar2(5)  path 'Postbusadres'

                     ) x

                ;

                 

                Job done!

                You can even directly insert or merge the result set into your target relational table.

                • 5. Re: Need help in getting data out of SOAP Envelope response from Web Service
                  user587700

                  Thanks.

                  This has simplified my code further.

                  I will tryout the direct merge.

                   

                  Kind Regards,

                  Nitin