• Post Reply Bookmark Topic Watch Topic
  • New Topic

Oracle function returning XML escaped

 
Robin Ken
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,



I am an Oracle DBA and not a developer but I have been trying to expose an Oracle function as a webservice using Jdeveloper and it works..sort of. Jdeveloper builds all the components and upon testing I get the correct answer back but the xml returned isnt seen as xml and so characters are changed I.E < becomes &t etc.

The webservice treats it as a string. I have read elsewhere that I need to define the response XML schema which I've done but still don't get the response I'm looking for (XML unescaped)



I am hoping someone can see an error in my wsdl or xsd or point me in the direction of an article that would help. Thank you



I've tried different Oracle packaged functions.

1 returning an XMLTYPE

1 returning a Clob



The response in SoapUI









SoapUI warning/error



    line 1: Expected element 'LoadCamt052Response@http://project1/Project1_TopLinkPlSqlProviderServiceService' instead of 'result@http://project1/Project1_TopLinkPlSqlProviderServiceService' here
    line 4: Expected element 'LoadCamt052Response@http://project1/Project1_TopLinkPlSqlProviderServiceService' before the end of the content





My WSDL











My XSD



 
g tsuji
Ranch Hand
Posts: 697
3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
From the error messages and that of the payload (rightly or wrongly) leaked as CDATA (that's why the angle brackets are escaped -  I assumed the forum shortcomings of rendering it properly), these are some conclusions I would draw and actions to be taken to rectify the wsdl and embedded and imported schemas.

[1] Your imported schema should have to make locally defined elements Id and DateProcessed be in the targetNamespace. Hence, you should specify it at the root of it.

Watch the elementFormDefault I've added there.

[2] Then your embedded schema is wrongly defined the loadCamt052ClobReturnResponseType which makes it rather an element in the namespace http://project1/Project1_TopLinkPlSqlProviderServiceService rather than in http://project1/Project1_TopLinkPlSqlProviderService. To make that happen, you should use ref attribute to refer to the imported schema.

[3] If the element for the request p_stmt_xml is defined correctly, you should see it should appear in the request soap with no namespace. If eventually, those not able to be deduced purely from the error messages and the other info, you actually should put it in ...ServiceService namespace, do something like what proposed in [1] adding elementFormDefault="qualified" in the root of the inline schema. This is just a lead in case you might need to correct that part too.
 
Robin Ken
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you for your response. I'll try it out now and reply.
 
Robin Ken
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks again for the pointers, they made a little more sense on how it all hangs together for me but my output is still enclosed in CDATA.

I made the changes you mentioned Points 1 and 2 and the result is the same. Everything between <srvc:result> is showing the < etc (Yes, this web page is formatting everything nicely)

Response

<?xml version = '1.0' encoding = 'UTF-8'?>
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <env:Header/>
   <env:Body>
      <srvc:loadCamt052ClobReturnResponse xmlns:srvc="http://project1/Project1_TopLinkPlSqlProviderServiceService">
         <srvc:result><LoadCamt052Response xmlns="http://project1/Project1_TopLinkPlSqlProviderService">
<Id>213</Id>
<DateProcessed>2016-07-12T16:25</DateProcessed>
</LoadCamt052Response></srvc:result>
      </srvc:loadCamt052ClobReturnResponse>
   </env:Body>
</env:Envelope>


An abreviated Soap request is as follows.

<?xml version = '1.0' encoding = 'UTF-8'?>
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="http://project1/Project1_TopLinkPlSqlProviderServiceService">
   <env:Header/>
   <env:Body>
      <ns1:loadCamt052ClobReturn>
         <ns1:p_stmt_xml><?xml version="1.0" encoding="utf-8"?>
<Document xmlns="urn:iso:std:iso:20022:tech:xsd:camt.052.001.06">
  <BkToCstmrAcctRpt>
....
</Document>
</ns1:p_stmt_xml>
      </ns1:loadCamt052ClobReturn>
   </env:Body>
</env:Envelope>

 
g tsuji
Ranch Hand
Posts: 697
3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
[4] From what I see from the request, p_stmt_xml is in the ...ServiceService namespace, so you need to put the elementFormDefault="qualified" in the root of the inline schema (xsd:schema). Have you done that? (The newer version of wsdl is not shown, if any newer at all.)

[4.1] Now this is important to clarify. The content of p_stmt_xml looks like an xml document. Question: is it in appearance only where xml entities are escaped actually or it really looks like this??? Clarify this before I go on. I don't get whole day, though.

There are other things too, but start from this.
 
Robin Ken
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Good morning,

I'm replying as fast as I can and appreciate the help. I have attached the wsdl and the xsd for you. The p_stmt_xml is xml and is the imput to the oracle function. In web service it is treated as a string, which is fine for what Im trying to do. The important thing for me is to get the outut appearing like xml and not with its escape chars instead.

Thanks again for your help.

eclipselink-dbws.wsdl

<wsdl:definitions name="Project1_TopLinkPlSqlProviderService"
                  targetNamespace="http://project1/Project1_TopLinkPlSqlProviderServiceService"
                  xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/"
                  xmlns:tns="http://project1/Project1_TopLinkPlSqlProviderServiceService"
                  xmlns:ns1="http://project1/Project1_TopLinkPlSqlProviderService"
                  xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/">
    <wsdl:types>
        <xsd:schema xmlns:tns="http://project1/Project1_TopLinkPlSqlProviderServiceService"
                    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                    targetNamespace="http://project1/Project1_TopLinkPlSqlProviderServiceService"
                    elementFormDefault="qualified">
            <xsd:import schemaLocation="eclipselink-dbws-schema.xsd"
                        namespace="http://project1/Project1_TopLinkPlSqlProviderService"/>
            <xsd:complexType name="loadCamt052ClobReturnResponseType">
                <xsd:sequence>       
                     <xsd:element ref="ns1:LoadCamt052Response" />                   
                </xsd:sequence>
            </xsd:complexType>
            <xsd:complexType name="loadCamt052ClobReturnRequestType">
                <xsd:sequence>
                    <xsd:element name="p_stmt_xml" type="xsd:string"/>
                </xsd:sequence>
            </xsd:complexType>
            <xsd:element name="loadCamt052ClobReturnResponse" type="tns:loadCamt052ClobReturnResponseType"/>
            <xsd:element name="loadCamt052ClobReturn" type="tns:loadCamt052ClobReturnRequestType"/>
        </xsd:schema>
    </wsdl:types>
    <wsdl:message name="loadCamt052ClobReturnRequest">
        <wsdl:part name="loadCamt052ClobReturnRequest" element="tns:loadCamt052ClobReturn"/>
    </wsdl:message>
    <wsdl:message name="loadCamt052ClobReturnResponse">
        <wsdl:part name="loadCamt052ClobReturnResponse" element="tns:loadCamt052ClobReturnResponse"/>
    </wsdl:message>
    <wsdl:portType name="Project1_TopLinkPlSqlProviderService_Interface">
        <wsdlperation name="loadCamt052ClobReturn">
            <wsdl:input message="tns:loadCamt052ClobReturnRequest"/>
            <wsdlutput message="tns:loadCamt052ClobReturnResponse"/>
        </wsdlperation>
    </wsdl:portType>
    <wsdl:binding name="Project1_TopLinkPlSqlProviderService_SOAP_HTTP"
                  type="tns:Project1_TopLinkPlSqlProviderService_Interface">
        <soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/>
        <wsdlperation name="loadCamt052ClobReturn">
            <soapperation soapAction="http://project1/Project1_TopLinkPlSqlProviderServiceService:loadCamt052ClobReturn"/>
            <wsdl:input>
                <soap:body use="literal"/>
            </wsdl:input>
            <wsdlutput>
                <soap:body use="literal"/>
            </wsdlutput>
        </wsdlperation>
    </wsdl:binding>
    <wsdl:service name="Project1_TopLinkPlSqlProviderService">
        <wsdl:port name="Project1_TopLinkPlSqlProviderServicePort"
                   binding="tns:Project1_TopLinkPlSqlProviderService_SOAP_HTTP">
            <soap:address location="http://localhost:7101/Application23-Project1-context-root/Project1_TopLinkPlSqlProviderService"/>
        </wsdl:port>
    </wsdl:service>
</wsdl:definitions>

eclipselink-dbws-schema.xsd

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="http://project1/Project1_TopLinkPlSqlProviderService" elementFormDefault="qualified">
   <xsd:element name="LoadCamt052Response">
      <xsd:complexType>
         <xsd:sequence>
            <xsd:element name="Id" type="xsd:integer"/>
            <xsd:element name="DateProcessed" type="xsd:string"/>
         </xsd:sequence>
      </xsd:complexType>
   </xsd:element>
</xsd:schema>

Test results (Text in red isnt shown as xml and characters like < etc appear)

<?xml version = '1.0' encoding = 'UTF-8'?>
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <env:Header/>
   <env:Body>
      <srvc:loadCamt052ClobReturnResponse xmlns:srvc="http://project1/Project1_TopLinkPlSqlProviderServiceService">
         <srvc:result><LoadCamt052Response xmlns="http://project1/Project1_TopLinkPlSqlProviderService">
<Id>222</Id>
<DateProcessed>2016-07-13T10:10</DateProcessed>
</LoadCamt052Response>
</srvc:result>
      </srvc:loadCamt052ClobReturnResponse>
   </env:Body>
</env:Envelope>


 
g tsuji
Ranch Hand
Posts: 697
3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
[4.1.1] Even though not explicitly clarified, I take it the content of p_stmt_xml are literal string with xml entities escaped _when_ the angle brackets of p_stmt_xml, for instance, are not. It must be rigoriously that if you put type="xsd:string" as its type.

[4.2] With [4.1.1] being said, the appearance of the xml declaration in the content <?xml ... ?> is no problem. If it is not, that xml declaration is inadmissible. Keep it in mind. It is no good if <document> etc in the content are meant to be xml elements... Even if the type is rigorously xsd:string, I would say, when you serialize an xmltype column with XMLSERIALIZE() or whatever, or load that from a physical file as binary, do not leave the trace of xml declaration into the text content of p_stmt_xml. That declaration would better be taken care, if asked for, by the xml parser eventually load up the text content converting back to an xml document. With that note, I leave the matter on the p_stmt_xml.

[5] Further down the response loadCamt052ClobReturnResponseType, it certainly is not what I would anticipate if you want the response to be shown (quite apart from the undesired escaped ns1 element). It should rather look like this.

That is what I would propose as far as I can see.
 
Robin Ken
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Apologies on the delay getting back. I appreciate the help.

Points 4.1 Yes
4.2 I agree, but its currently how the client would send it.

5. I've made the changes mentioned and have a problem using the ref to LoadCamt052Response

            <xsd:complexType name="loadCamt052ClobReturnResponseType">
                <xsd:sequence>
                    <xsd:element ref="ns1:LoadCamt052Response"/>
                </xsd:sequence>
            </xsd:complexType>
           
* Reference ns1:LoadCamt052Response not found

I've checked the namespace and can't see a problem.

My xsd is as follows

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            targetNamespace="http://project1/Project1_TopLinkPlSqlProviderService" elementFormDefault="qualified">
   
    <xsd:complexType name="LoadCamt052Response">
        <xsd:sequence>
            <xsd:element name="Id" type="xsd:integer"/>
            <xsd:element name="DateProcessed" type="xsd:string"/>
        </xsd:sequence>
    </xsd:complexType>
</xsd:schema>


And here is the complete WSDL, changes made in red.


<wsdl:definitions name="Project1_TopLinkPlSqlProviderService"
                  targetNamespace="http://project1/Project1_TopLinkPlSqlProviderServiceService"
                  xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/"
                  xmlns:tns="http://project1/Project1_TopLinkPlSqlProviderServiceService"
                  xmlns:ns1="http://project1/Project1_TopLinkPlSqlProviderService"
                  xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/">
    <wsdl:types>
        <xsd:schema xmlns:tns="http://project1/Project1_TopLinkPlSqlProviderServiceService"
                    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                    targetNamespace="http://project1/Project1_TopLinkPlSqlProviderServiceService"
                    elementFormDefault="qualified">
            <xsd:import schemaLocation="eclipselink-dbws-schema.xsd"
                        namespace="http://project1/Project1_TopLinkPlSqlProviderService"/>
           
           
            <xsd:complexType name="loadCamt052ClobReturnResponseType">
                <xsd:sequence>
                    <xsd:element ref="ns1:LoadCamt052Response"/>
                </xsd:sequence>
            </xsd:complexType>
           

           
            <xsd:complexType name="loadCamt052ClobReturnRequestType">
                <xsd:sequence>
                    <xsd:element name="p_stmt_xml" type="xsd:string"/>
                </xsd:sequence>
            </xsd:complexType>
            <xsd:element name="loadCamt052ClobReturnResponse" type="tns:loadCamt052ClobReturnResponseType"/>
            <xsd:element name="loadCamt052ClobReturn" type="tns:loadCamt052ClobReturnRequestType"/>
        </xsd:schema>
    </wsdl:types>
    <wsdl:message name="loadCamt052ClobReturnRequest">
        <wsdl:part name="loadCamt052ClobReturnRequest" element="tns:loadCamt052ClobReturn"/>
    </wsdl:message>
    <wsdl:message name="loadCamt052ClobReturnResponse">
        <wsdl:part name="loadCamt052ClobReturnResponse" element="tns:loadCamt052ClobReturnResponse"/>
    </wsdl:message>
    <wsdl:portType name="Project1_TopLinkPlSqlProviderService_Interface">
        <wsdlperation name="loadCamt052ClobReturn">
            <wsdl:input message="tns:loadCamt052ClobReturnRequest"/>
            <wsdlutput message="tns:loadCamt052ClobReturnResponse"/>
        </wsdlperation>
    </wsdl:portType>
    <wsdl:binding name="Project1_TopLinkPlSqlProviderService_SOAP_HTTP"
                  type="tns:Project1_TopLinkPlSqlProviderService_Interface">
        <soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/>
        <wsdlperation name="loadCamt052ClobReturn">
            <soapperation soapAction="http://project1/Project1_TopLinkPlSqlProviderServiceService:loadCamt052ClobReturn"/>
            <wsdl:input>
                <soap:body use="literal"/>
            </wsdl:input>
            <wsdlutput>
                <soap:body use="literal"/>
            </wsdlutput>
        </wsdlperation>
    </wsdl:binding>
    <wsdl:service name="Project1_TopLinkPlSqlProviderService">
        <wsdl:port name="Project1_TopLinkPlSqlProviderServicePort"
                   binding="tns:Project1_TopLinkPlSqlProviderService_SOAP_HTTP">
            <soap:address location="http://localhost:7101/Application8-Project1-context-root/Project1_TopLinkPlSqlProviderService"/>
        </wsdl:port>
    </wsdl:service>
</wsdl:definitions>



 
Robin Ken
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

posted the wrong WSDL changes earlier.

<xsd:complexType name="loadCamt052ClobReturnResponseType">
                <xsd:sequence>
                    <xsd:element name="result">
                        <xsd:complexType>
                            <xsd:sequence>
                                <xsd:element ref="ns1:LoadCamt052Response"/>
                            </xsd:sequence>
                        </xsd:complexType>
                    </xsd:element>
                </xsd:sequence>
            </xsd:complexType>
            <xsd:complexType name="loadCamt052ClobReturnRequestType">

The ref to ns1:LoadCamt052Response still cannot be found.

 
Robin Ken
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

I modified my XSD to the following which resolved the ref link, but the result are still the same unfortunately




SoapUI response


As you can see, the response is still wrapped in CDATA.
 
g tsuji
Ranch Hand
Posts: 697
3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But then how do you build the content of result element? Show the relevant lines (only) so that I may guess what technology you're using and why and how it could go wrong.
 
Robin Ken
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Morning,

I am following the method of exposing a plsql function by following the steps in the following link.

https://community.oracle.com/docs/DOC-912718

Jdeveloper 12.1.3

I have tried several different function return types XMLTYPE, Varchar etc. The results (data) is correct but always wrapped in CDATA.


Regards


Robin


 
g tsuji
Ranch Hand
Posts: 697
3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I see a bit clearer. I can create a more complicated package and confirm the behaviour you more or less mentioned. (wsdl seems another matter, it is generally sensible but it does not command the actual output due to eclipselink toplink provider has its own thing to say providing the service!) That kind of exposing a stored procedure/function through automation via a toplink wizard... and then testable with a client is a laudable effort on their part. Unfortunately, I think it would fall short for marginally more complicated requirements. If you have at design time a clear picture of what you want to get back, I think a less fancy top-down approach with the calling to the stored procedure/function in its business layer seems more sensible and offers more tight grip of various needs to the author of the ws.

Having said, I think the issue is the use of simple-xml-format schema (eclipselink-dbws-schema.xsd). A simplistic manual modification of it won't do much. I think the detail control of the response may reside also on the automatically generated eclipselink-dbws.xml where you see the result tag with <type>xsd:string</type><simple-xml-format /> etc... On exactly how to tweek the output formatting, I cannot help simply I don't know enough of the eclipselink dbws. I think it won't be a matter of a few button clicks away. Whether it is worth the effort for a highly lock-in tool, I cannot say. (In a step of the wizard, you have a reformat button which makes a few, three, alternatives. But it seems it does not serve the purpose in question.)

Maybe you can follow-through consulting their documentations such as
Oracle® Fusion Middleware Developing Persistence Architectures Using Oracle TopLink Database Web Services
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!