Scribd Logo
Upload

Welcome to Scribd!

  • 89 views

    SQLAdapter Biz Talk

    Uploaded by

    api-3747450
    The document describes setting up an OrderManager solution in BizTalk that calls a SQL stored procedure. It includes sample order XML, creating a stored procedure to insert XML into a table, adding a SQL adapter, mapping schemas, and constructing the message to call the stored procedure.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd

    SQLAdapter Biz Talk

    Uploaded by

    api-3747450
    89 views6 pages
    The document describes setting up an OrderManager solution in BizTalk that calls a SQL stored procedure. It includes sample order XML, creating a stored procedure to insert XML into a table, adding a SQL adapter, mapping schemas, and constructing the message to call the stored procedure.

    Original Title

    SQLAdapterBizTalk

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    DOC, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document describes setting up an OrderManager solution in BizTalk that calls a SQL stored procedure. It includes sample order XML, creating a stored procedure to insert XML into a table, adding a SQL adapter, mapping schemas, and constructing the message to call the stored procedure.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    89 views6 pages

    SQLAdapter Biz Talk

    Uploaded by

    api-3747450
    The document describes setting up an OrderManager solution in BizTalk that calls a SQL stored procedure. It includes sample order XML, creating a stored procedure to insert XML into a table, adding a SQL adapter, mapping schemas, and constructing the message to call the stored procedure.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    of 6

    Solution Name : OrderManager

    Schema Name : IncompleteOrder.xsd

    Sample Input Values


    <ns0:Order xmlns:ns0="http://OrderManager.IncompleteOrder">
    <OrderId>1</OrderId>
    <OrderDate>2005-03-01</OrderDate>
    <CustomerId>ALFKI</CustomerId>
    </ns0:Order>

    Schema Name : CompleteOrder.xsd

    Create Stored Procedure


    Create procedure TestSP
    (@XMLMsg ntext)
    AS
    Begin
    DECLARE @ErrCode Int
    INSERT INTO SAP_Import_Msg (iMessage_No, sMessage_XML) VALUES
    (1, @XMLMsg)
    IF @@ERROR<>0
    begin
    SET @ErrCode = @@ERROR
    end
    ELSE
    BEGIN
    SET @ErrCode = 0

    end

    Select @ErrCode as ErrorCode for XML RAW, XMLDATA


    RETURN @@Error
    END

    Adding the structure to call SQL Server


    Right-click on the project in the Solution Explorer and select the "Add Generated Items"
    option. In the list of items, select the Add Adapter option and click on the Open button.
    The following screen will show up:

    In this screen, select the adapter of type "SQL" and click Next

    In the first screen, click on the Set button and provifde the information to connect to
    your SQL Server instance. Select "Northwind" as the initial catalog. When the
    connection string is set, click Next.
    Th next screen shows information about the schemas that will be used to transport
    the information to and from SQL. In the Target Namespace option, type
    "http://nwtraders". In the Port-Type, select "Send Port", since we're going to send a
    request to SQL Server and receive a response. In the property "Request root element
    name", type "InCustomer", and in the property "Response root element name", type
    "OutCustomer". The screen should be as in the picture below:

    Click on the Next button. In the screen "Statement type information", select the
    option "Stored Procedure". Click Next. In the combo box for selecting the procedure,
    select sendXMLMsg.

    This information will be used by the SQL adapter to generate the initial schema, they
    are not used later in the project. Click on the Next button, and in the next screen,
    click the Finish button. A new schema and a new orchestration will be created in your
    project.

    The created schema (SQLService) contains the request and response information for
    the stored procedure. The orchestration contains some types (port type) used to call
    the SQL adapter.

    MAP

    Source :IncompleteOrder.xsd
    Target : IntermediateCompleteOrder.xsd
    Message Construction

    vTempXMLDocument =new System.Xml.XmlDocument();


    vTempXMLDocument.LoadXml("<ns0:InboundDeliveriesRequest
    xmlns:ns0='http://namespace/Inbbound><ns0:SendXMLMsg_Temp
    XMLMsg=''/></ns0: InboundDeliveriesRequest >");
    InboundDeliveriesToRequestMsg=vTempXMLDocument;

    vXMLSPDoc = new System.Xml.XmlDocument();


    vXMLSPDoc = InboundDeliveriesBFCSIntermediateMsg;
    InboundDeliveriesToBFCSSPRequestMsg.SAP_SendXMLMsg_Temp.XMLMsg=vXMLSPDo
    c.InnerXml;

    You might also like