This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Jun 26, 2012 12:24 PM by odie_63 Branched to a new discussion. RSS

Extracting the data from web service response

Known_Sword Explorer
Currently Being Moderated
Hi,

I called a web service and in response got an XML data, now I have to extract the values and store in table

My steps,

1,call web service
2,stored response in CLOB column
3,Then used following query

SELECT loc.*
FROM my_xml PO,
XMLTable(XMLNAMESPACES(
'urn:schemas-microsoft-com:xml-diffgram-v1' as "diffgr"
,'http://api.***.com/' as "ns0"
)
,'/ns0:DataSet/diffgr:diffgram/NewDataSet/OptOutAll'
PASSING xmltype.createxml(PO.xml_data)
COLUMNS
"Email" CHAR(100) PATH 'Email',
"Reason" CHAR(150) PATH 'Reason'
) As loc
WHERE ROWNUM <= 10;

I have written it by using this example

But it is not working, dont know why

/Zab
  • 1. Re: Extracting the data from web service response
    odie_63 Guru
    Currently Being Moderated
    But it is not working, dont know why
    Thanks for letting us know... :)

    Do you want help?

    If so, provide :
    - error message (if any)
    - the database version : SELECT * FROM v$version
    - a sample XML document returned by the WS call
  • 2. Re: Extracting the data from web service response
    Known_Sword Explorer
    Currently Being Moderated
    Hi,

    Thanks for reply as usual...

    Yes,

    Nothing is selected
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

    <?xml version="1.0" encoding="utf-8" ?>
    - <DataSet xmlns="http://api.***.com/">
    - <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    - <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
    - <xs:complexType>
    - <xs:choice minOccurs="0" maxOccurs="unbounded">
    - <xs:element name="OptOutAll">
    - <xs:complexType>
    - <xs:sequence>
    <xs:element name="Email" type="xs:string" minOccurs="0" />
    <xs:element name="Reason" type="xs:string" minOccurs="0" />
    </xs:sequence>
    </xs:complexType>
    </xs:element>
    </xs:choice>
    </xs:complexType>
    </xs:element>
    </xs:schema>
    - <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
    - <NewDataSet xmlns="">
    - <OptOutAll diffgr:id="OptOutAll1" msdata:rowOrder="0" diffgr:hasChanges="inserted">
    <Email>abc@yahoo.com</ Email >
    <Reason>Subscriber Opt out from lists</Reason>
    </OptOutAll>
    - <OptOutAll diffgr:id="OptOutAll2" msdata:rowOrder="1" diffgr:hasChanges="inserted">
    <Email>abc@yahoo.com</ Email >
    <Reason>Subscriber Opt out from lists</Reason>
    </OptOutAll>
    </NewDataSet>
    </diffgr:diffgram>
    </DataSet>
  • 3. Re: Extracting the data from web service response
    odie_63 Guru
    Currently Being Moderated
    Thanks for the details, however the sample XML is not correct.

    This is not a wellformed tag :
    <Email>abc@yahoo.com</ Email > 
    You've obviously copied the content from a browser and edited it afterwards.

    Apart from that, after correcting the Email tag the query works OK for me.
    Maybe there's an issue in the way you get and store the response from the WS?

    Could you post a sample from the CLOB column?
  • 4. Re: Extracting the data from web service response
    Known_Sword Explorer
    Currently Being Moderated
    Hi,

    Thanks, you are correct. When I query the clob it give me additional 'Soap' tags also.

    <?xml version="1.0" encoding="utf-8"?>
    <soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <soap:Body><GetOptOutAllResponse mlns="http://api.***.com/">
    <GetOptOutAllResult><xs:schema id="NewDataSet"
    xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
    <xs:complexType>
    <xs:choice minOccurs="0" maxOccurs="unbounded">
    <xs:element name="OptOutAll">
    <xs:complexType>
    <xs:sequence>
    <xs:element name="Email" type="xs:string" minOccurs="0" />
    <xs:element name="SendQueueId" type="xs:int" minOccurs="0" />
    <xs:element name="Reason" type="xs:string" minOccurs="0" />
    </xs:sequence>
    </xs:complexType></xs:element></xs:choice>
    </xs:complexType>
    </xs:element>
    </xs:schema>
    <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
    xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
    <NewDataSet xmlns="">
    <OptOutAll diffgr:id="OptOutAll1" msdata:rowOrder="0" diffgr:hasChanges="inserted">
    <Email>abc@yahoo.com</ Email >
    <Reason>Subscriber confirmed Opt-out from all lists</Reason>
    </OptOutAll>
    </NewDataSet>
    </diffgr:diffgram>
    </DataSet>

    May be I have to write some thing more for 'SOAP'.
  • 5. Re: Extracting the data from web service response
    Known_Sword Explorer
    Currently Being Moderated
    Hi,

    I tried this version for 11.0 also

    DECLARE
    soap_doc xmltype;
    res varchar2(32000);
    BEGIN

    select xmlcast(
    xmlquery(
    'declare namespace soap="http://www.w3.org/2003/05/soap-envelope"; (::)
    declare namespace ns0="http://api.***.com/"; (::)
    declare namespace msdata="urn:schemas-microsoft-com:xml-msdata"; (::)
    declare namespace diffgr="urn:schemas-microsoft-com:xml-diffgram-v1"; (::)
    /soap:Envelope/soap:Body/ns0:DataSet/diffgr:diffgram/NewDataSet/OptOutAll'
    passing xml_data
    returning content
    )
    as varchar2(30)
    )
    into res
    from my_xml2;

    dbms_output.put_line(nvl(res,'-'));

    END;

    But nothing is returned , confused.
  • 6. Re: Extracting the data from web service response
    odie_63 Guru
    Currently Being Moderated
    When I query the clob it give me additional 'Soap' tags also.
    Your last XML sample is still not correct...

    What's with the tags GetOptOutAllResponse, GetOptOutAllResult ?

    I'm afraid I can't help if you're not at least posting something we can work on.
  • 7. Re: Extracting the data from web service response
    Known_Sword Explorer
    Currently Being Moderated
    Hi,

    Actually the xml is too large therefore I pasted only the one record, How should I give complete xml?

    I am missing these tags GetOptOutAllResponse, GetOptOutAllResult , no wondering how to figure them in query but still nothing i sreturned.

    Thanks,
  • 8. Re: Extracting the data from web service response
    odie_63 Guru
    Currently Being Moderated
    Actually the xml is too large therefore I pasted only the one record, How should I give complete xml?
    I don't want you to post the complete document, but just a valid_ one.

    In what you posted so far, you were either missing the soap elements, the GetOptOut elements or the DataSet element.
    If the issue lies in the way you're writing the XQuery expression, then you surely understand it's difficult to help without seeing the correct structure of the document.

    So please, again, can you post a valid sample XML?
  • 9. Re: Extracting the data from web service response
    Known_Sword Explorer
    Currently Being Moderated
    Hi,

    I will paste the correct xml but cant figure out to get out the complete xml from databse , i stored the xml in xmltype column and by running this querey got the length

    select dbms_lob.getlength(xmltype.getclobval(xml_data)) from my_xml2;
    output=953467

    How to see the complete xml from xmltype column?
  • 10. Re: Extracting the data from web service response
    odie_63 Guru
    Currently Being Moderated
    What tool are you using?

    Most GUI tools I know provide an embedded text editor for large character-based columns in a result set.

    Or, in SQL*Plus, you can do :
    SET LONG 50000
    SET LONGC 50000
    SET LINES 200
    SET PAGES 200
    select xmlserialize(document xml_data as clob indent) from my_xml2;
    It should output a large part of the XML content, sufficient enough to see the access path we must use.
  • 11. Re: Extracting the data from web service response
    Known_Sword Explorer
    Currently Being Moderated
    Hi,

    I use PL/SQL developer,

    This is the xml I got, nothing chnaged. But without ending tags as I cant see them due to large file.

    <?xml version="1.0" encoding="ISO-8859-15">
    <soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <soap:Body>
    <GetOptOutAllResponse xmlns="http://api.***.com/">
    <GetOptOutAllResult>
    <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-               microsoft-com:xml-msdata">
    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
    <xs:complexType>
    <xs:choice minOccurs="0" maxOccurs="unbounded">
    <xs:element name="OptOutAll">
    <xs:complexType>
    <xs:sequence>
    <xs:element name="OptOutTime" type="xs:dateTime" minOccurs="0"/>
    <xs:element name="Email" type="xs:string" minOccurs="0"/>
    <xs:element name="MailingList" type="xs:string" minOccurs="0"/>
    <xs:element name="SendQueueId" type="xs:int" minOccurs="0"/>
    <xs:element name="Reason" type="xs:string" minOccurs="0"/>
    </xs:sequence>
    </xs:complexType>
    </xs:element>
    </xs:choice>
    </xs:complexType>
    </xs:element>
    </xs:schema>
    <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
    <NewDataSet xmlns="">
    <OptOutAll diffgr:id="OptOutAll1" msdata:rowOrder="0" diffgr:hasChanges="inserted">
    <OptOutTime>2012-05-09T22:40:00+02:00</OptOutTime>
    <MailingList>information</MailingList>
    <SendQueueId>111111</SendQueueId>
    <Reason>Subscriber confirmed</Reason>
    </OptOutAll>
    <OptOutAll diffgr:id="OptOutAll2" msdata:rowOrder="1" diffgr:hasChanges="inserted">
    <OptOutTime>2012-06-07T22:30:00+02:00</OptOutTime>
    <MailingList>information</MailingList>
    <SendQueueId>111111</SendQueueId>
    <Reason>Subscriber confirmed</Reason>
    </OptOutAll>
    <OptOutAll diffgr:id="OptOutAll3" msdata:rowOrder="2" diffgr:hasChanges="inserted">
    <OptOutTime>2012-06-14T00:20:00+02:00</OptOutTime>
    <MailingList>information</MailingList>
    <SendQueueId>111111</SendQueueId>
    <Reason>Subscriber confirmed</Reason>
    </OptOutAll>
    <OptOutAll diffgr:id="OptOutAll4" msdata:rowOrder="3" diffgr:hasChanges="inserted">
    <OptOutTime>2012-06-19T20:50:00+02:00</OptOutTime>
    <MailingList>information</MailingList>
    <SendQueueId>111111</SendQueueId>
    <Reason>Subscriber confirmed</Reason>
    </OptOutAll>
    <OptOutAll diffgr:id="OptOutAll5" msdata:rowOrder="4" diffgr:hasChanges="inserted">
    <OptOutTime>2012-06-19T20:50:00+02:00</OptOutTime> </NewDataSet>
    </diffgr:diffgram>
    </DataSet>
    <MailingList>information</MailingList> <SendQueueId>111111</SendQueueId>
    <Reason>Subscriber confirmed</Reason>
    </OptOutAll>



    Ending tags would be

    </NewDataSet>
    </diffgr:diffgram>
    </DataSet>

    Edited by: Known_Sword on Jun 26, 2012 4:08 PM
  • 12. Re: Extracting the data from web service response
    odie_63 Guru
    Currently Being Moderated
    I use PL/SQL developer,
    Me too.
    You can use the field editor to see the content of a column (click on the small button which looks like [...]).
    This is the xml I got
    I don't think so.

    Where is the opening tag that should match the closing </DataSet> ??
  • 13. Re: Extracting the data from web service response
    Known_Sword Explorer
    Currently Being Moderated
    Hi,

    Yes I was wrong the ending look likes

    </NewDataSet>
    </diffgr:diffgram>
    </GetOptOutAllResult>
    </GetOptOutAllResponse>
    </soap:Body></soap:Envelope>

    thanks,
  • 14. Re: Extracting the data from web service response
    odie_63 Guru
    Currently Being Moderated
    If I use the following sample XML (stored in XMLType table TMP_XML) :
    <?xml version="1.0" encoding="UTF-8"?>
    <soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <soap:Body>
        <GetOptOutAllResponse xmlns="http://api.***.com/">
          <GetOptOutAllResult>
            <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas- microsoft-com:xml-msdata">
              <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
                <xs:complexType>
                  <xs:choice minOccurs="0" maxOccurs="unbounded">
                    <xs:element name="OptOutAll">
                      <xs:complexType>
                        <xs:sequence>
                          <xs:element name="OptOutTime" type="xs:dateTime" minOccurs="0"/>
                          <xs:element name="Email" type="xs:string" minOccurs="0"/>
                          <xs:element name="MailingList" type="xs:string" minOccurs="0"/>
                          <xs:element name="SendQueueId" type="xs:int" minOccurs="0"/>
                          <xs:element name="Reason" type="xs:string" minOccurs="0"/>
                        </xs:sequence>
                      </xs:complexType>
                    </xs:element>
                  </xs:choice>
                </xs:complexType>
              </xs:element>
            </xs:schema>
            <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
              <NewDataSet xmlns="">
                <OptOutAll diffgr:id="OptOutAll1" msdata:rowOrder="0" diffgr:hasChanges="inserted">
                  <OptOutTime>2012-05-09T22:40:00+02:00</OptOutTime>
                  <MailingList>information</MailingList>
                  <SendQueueId>111111</SendQueueId>
                  <Reason>Subscriber confirmed</Reason>
                </OptOutAll>
                <OptOutAll diffgr:id="OptOutAll2" msdata:rowOrder="1" diffgr:hasChanges="inserted">
                  <OptOutTime>2012-06-07T22:30:00+02:00</OptOutTime>
                  <MailingList>information</MailingList>
                  <SendQueueId>111111</SendQueueId>
                  <Reason>Subscriber confirmed</Reason>
                </OptOutAll>
                <OptOutAll diffgr:id="OptOutAll3" msdata:rowOrder="2" diffgr:hasChanges="inserted">
                  <OptOutTime>2012-06-14T00:20:00+02:00</OptOutTime>
                  <MailingList>information</MailingList>
                  <SendQueueId>111111</SendQueueId>
                  <Reason>Subscriber confirmed</Reason>
                </OptOutAll>
                <OptOutAll diffgr:id="OptOutAll4" msdata:rowOrder="3" diffgr:hasChanges="inserted">
                  <OptOutTime>2012-06-19T20:50:00+02:00</OptOutTime>
                  <MailingList>information</MailingList>
                  <SendQueueId>111111</SendQueueId>
                  <Reason>Subscriber confirmed</Reason>
                </OptOutAll>
              </NewDataSet>
            </diffgr:diffgram>
          </GetOptOutAllResult>
        </GetOptOutAllResponse>
      </soap:Body>
    </soap:Envelope>
    Then I can do :
    SQL> SELECT x.*
      2  FROM tmp_xml t
      3     , XMLTable(
      4         XMLNamespaces(
      5           'http://www.w3.org/2003/05/soap-envelope' as "soap"
      6         , 'urn:schemas-microsoft-com:xml-diffgram-v1' as "diffgr"
      7         , 'http://api.***.com/' as "ns0"
      8         )
      9       , '/soap:Envelope/soap:Body/ns0:GetOptOutAllResponse/ns0:GetOptOutAllResult/diffgr:diffgram/NewDataSet/OptOutAll'
     10         PASSING t.object_value
     11         COLUMNS OptOutTime  TIMESTAMP WITH TIME ZONE PATH 'OptOutTime'
     12               , MailingList VARCHAR2(100)            PATH 'MailingList'
     13               , Reason      VARCHAR2(150)            PATH 'Reason'
     14       ) x
     15  ;
     
    OPTOUTTIME                          MAILINGLIST                    REASON
    ----------------------------------- ------------------------------ --------------------------------------------------------------------------------
    09/05/12 22:40:00,000000 +02:00     information                    Subscriber confirmed
    07/06/12 22:30:00,000000 +02:00     information                    Subscriber confirmed
    14/06/12 00:20:00,000000 +02:00     information                    Subscriber confirmed
    19/06/12 20:50:00,000000 +02:00     information                    Subscriber confirmed
     
    Are you getting anything different?
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points