none
NativeSQLexecuteResponse in to XMLDocument RRS feed

  • Question

  • Hi,
     
    I'm using Oracle Adapter to receive the data from the oracle database. The oracle Adapter is configured to retrive the data using NativeSQL way. I manage to get the NativeSQLResponse which has data from the oracle table.
     
    1) I have a receiving port to receive the data. Receive Port is configured for Oracle Database
    2) In the orchestration , I have receive shape to get the  NativeSQLExcuteResponse
    3) I'm trying to convert NativeSQLExcuteResponse 
    to XMLDocumnet in the expression shape.
     
    Since NativeSQLExecute response has no schema. I wish to do mapping of this with my destination schema, "schema1". I could simply do not do mapping using the mapper. so I tried to convert the NAtiveSQLExecuteResponse in to XMLdocument and pass it to a custome dll for parsing. I could not convert the NAtiveSQLResponse in to XMLdocumnet message.
     
    Can somebody point me how to convert NAtiveSQLexecuyteResponse in to XMLDocument?

    .Nilesh
    Saturday, January 17, 2009 12:13 PM

All replies

  • I hope below info will help you.

    Steps to use the SQLEvent:

     

    In the BizTalk Manager – create a new application named OraEvent2

    Right-click Receive Ports – choose New – One-way Receive Port…

    Name it OraSQLEventPort

    Click on Receive Locations in the left pane

    Click New… in the right pane

    Name: OraSQLEventLoc

    Type: OracleDB Adapter

    Receive pipeline: XMLReceive

     

    Click the Configure… button

     

    Enter the Password, PATH, Service name and User name

    Click OK to save the configuration

    Go back into the configuration (click the Configure… button)

    Scroll down to Managing Events – click in the text box and click on the … button

    Open the top item (the name of your Oracle adapter)

    Open the second item (the name of your ODBC connection)

    Scroll to the bottom and highlight NativeSQL – click on the right arrow button

    Click OK

     

    In the configuration panel there is an option for Poll SQL Statement – here is where the SQL statement to be used for Polling should go – for example, my statement is:

     

    SELECT EMPNO, EMPNAME FROM VTEMP_TARGET WHERE UPDATE_TYPE='INSERT'

     

    Then go to the Post Poll SQL Statement – this is where you can issue the Post Poll statement which is only executed if the Poll statement was successful and no problems occurred in the Orchestration.  My statement is:

     

    UPDATE VTEMP_TARGET SET UPDATE_TYPE='PROCESSED' WHERE UPDATE_TYPE='INSERT'

     

    Notice – this statement changes the entry so that I will not keep retrieving back the same rows when I poll the table.  This way I can keep the rows to see what HAS been processed.

     

    When the Poll statements have been configured, click on OK – OK

     

    Now you have the Receive Port prepared and are ready to create the BizTalk Application in Visual Studio.

     

    Open an Empty BizTalk Application within Visual Studio – name it OraEvent2.

     

    Go to the Properties for OraEvent2 –

      Common Properties – Assembly – Assembly Key File --- set this to a valid .snk file

      Configuration Properties – Deployment – Application Name --- OraEvent2 (the same name you created in the BizTalk manager where you created the Receive Port)

    Click OK

     

    Now go into Add – Add Generated Items…

    Point to the Receive Port that was created – and go to the same NativeSQL you entered for the Managing Events entry – Click Finish

     

    There will be an Orchestration and three .xsd files created – you can delete the NativeSQLService_obj.xsd file.

     

    Open the Orchestration -

     

    On the right port surface create a New Configured Port – name: FromOracle

    Make it a One Way port that will be Receiving messages and specify Port Bindings later.

    Click on ‘Request’ inside of the FromOracle port – in the Properties, set the Message Type to Multi-part Message Types – OraEvent2.SQLEvent

     

    On the left port surface create a New Configured Port – name: ToDisk

    Make it a One Way port that will be Sending messages and specify Port Bindings later.

     

    Insert a Receive component into the orchestration – Set Activate: True

    Connect the FromOracle Port location to this Receive Component

      (Note – the message type will be Multi-part Message Types – OraEvent2.SQLEvent)

     

    Insert a Send component after the Receive

    Connect the Send component to the ToDisk Port

     

    The Orchestration should look like:

     

     

    Now compile and deploy the Orchestration.  In the BizTalk Manager you will need to refresh the application and then configure it to point to the Oracle receive port and create a new Send port that uses the FILE adapter and writes to a folder.

     

    Then start the BizTalk Application.

     

    If there is any data in the table – it will be pulled down and you will see an xml document show up in the folder.  Any changes made to the Oracle table where the data matches the Poll statement will be pulled down every 60 seconds when the poll occurs.


    Sanjay
    Friday, February 13, 2009 4:45 AM