7 Replies Latest reply on Jun 18, 2019 4:37 PM by 2797847

    SOA 12C BPEL

    2797847

      Hi,

      can somebody help me how to process an XML stored in Oracle database as CLOB using DB adapter in BPEL Process.

      1) in Payload XML message printing as a string which include &lt and &gt

      2) How to use ora:parseEscapedXML convert string back to XML and what type of variable should be created

      3) is XSD required to create a variable

      Please share me detailed steps how to process the XML message from DB clob and transform in to table as a inbound interface.

       

      Thanks.

        • 1. Re: SOA 12C BPEL
          Martien van den Akker

          Hi,

           

          It seems to me that you quite already know how to do it. Create a DB Adapter that selects the clob column based on the PK field of the table.

          To answer 3): yes, you'll need an xsd representing the XML that is in the Clob column. Preferably having an element defined.

          Create a variable on the particular element.

          After the invoke of the DB Adapter, create an assign, with an expression on the variable. Select the ora:parseExcapedXML() function from the functions list. Insert the element from the DB Adapter Output variable representing the Clob/XML column into the ora:parseExcapedXML() .


          Deploy and run...

          I said that it's prefered having an element defined in the xsd that represents the xml of the clob column. In most cases you can also have a (complex)type. However, for transformations you'll need to have an element-based variable.

           

          Regards,
          Martien

          • 2. Re: SOA 12C BPEL
            2797847

            Hi Martien,

            thank you for the reply and I am new to SOA trying to develop an inbound interface.

            can you please give me more information on below

            1) do I need to create an element based variable of type XSD?

            2) I have provided 3 versions of XSD so can I club all 3 versions in to single version or what is the best practice.

            appreciate if you can provide me any document or steps to perform the transformation.

            Thank you for you support and time.

             

            Regards,

            Srini

            • 3. Re: SOA 12C BPEL
              2797847

              Hi Martien,

              thank you for the reply and I am new to SOA trying to develop an inbound interface.

              can you please give me more information on below

              1) do I need to create an element based variable of type XSD?

              2) I have provided 3 versions of XSD so can I club all 3 versions in to single version or what is the best practice.

              appreciate if you can provide me any document or steps to perform the transformation.

              Thank you for you support and time.

               

              Regards,

              Srini

              • 4. Re: SOA 12C BPEL
                Martien van den Akker

                Hi Srini

                 

                I think this describes quite thoroughly how to configure the DB Adapter https://www.avioconsulting.com/blog/oracle-soa-database-adapter-101-weblogic-configuration-steps

                 

                Ad 1: you need to create a variable based on an element defined in the XSD. I don't understand exactly what you mean with your phrasing of the question.

                Ad 2: I don't understand what you mean with these 3 versions. You can combine several definitions in an XSD. However, an XSD can have only one target namespace. Do you mean that the clob in the DB can contain xml from different definitions? I like to keep things simple and have an xsd per purpose. I only combine messages that belong to eachother in one xsd.

                 

                I feel that your questions are pretty basic, and could be answered by a SOA Suite training. So I would recommend to attend a training or get the Lucas Jellema's SOA Suite handbook: https://www.bol.com/nl/f/oracle-soa-suite-12c-handbook/9200000041154620/

                 

                Kind regards,
                Martien

                • 5. Re: SOA 12C BPEL
                  2797847

                  Hi Martien,

                  Thanks for the reply.

                  one question, in DB adapter I have written SQL query using XML_TYPE column and trying to assign DB adapter out variable to insert table variable and getting error like(Exception is thrown because the from-spec at line 95 is evaluated to be empty) but in view payload of DB adapter invoke XML printing fine but not able to access the output variable.

                  is it valid to query XML_TYPE column in DB adapter.

                  Please help me in this regard.

                  • 6. Re: SOA 12C BPEL
                    Martien van den Akker

                    Hi Srini,

                     

                    You should open your BPEL file and look at line 95 in the source view. You'll find an assign-copy rule there where the expression in the from element does not result in a value. In the flow trace review the variable contents and against that xpath expression.

                    If the expression refers to a optional element that can be considered empty, then you can switch back to the design view, right-click on the copy rule and check the 'IgnoreMissingFromData' option.

                    Then redeploy.

                     

                    This error is actually one of the easier ones. BPEL is always right in this one and it neatly presents the line number where it fails.

                     

                    Regards,
                    Martien

                    • 7. Re: SOA 12C BPEL
                      2797847

                      Hi Martein,

                      Thanks for the help and support, I have successfully completed POC reading XML message from DB and load  in to table.

                      Thanks for your inputs.

                       

                      Regards,

                      Srini