none
WCF-SQL Stored Procedure calling from Orchestration RRS feed

  • Question

  • Hello,

    I have stored procedure which return 4 Dataset/XML as output. When I use this procedure from receive port using polling interval and  with properties XMLStoredProcedureRootNodeName and XMLStoredProcedureRootNodeNamespace  it will return only single XML with assigned RootNode and Namespace. Now I want to use the same procedure as Request-Response port from my Orchestration. 

    When i use WCF-Custom or WCF-SQL both property are not binding XMLStoredProcedureRootNodeName and XMLStoredProcedureRootNodeNamespace.

    I am receiving my Response with result set and 4 data set. 

    Please guide me what Property i am missing.

    Regards,
    Saurin


    Friday, July 14, 2017 10:44 AM

All replies

  • Very important question, does the Stored Procedure use FOR XML?

    Returning 4 datasets is the default behavior of SQL Server + WCF sqlBinding.

    Friday, July 14, 2017 2:04 PM
    Moderator
  • Hi, Thanks for reply, Yes I used For XML with my requirement, 

    Something like this:

    ;WITH XMLNAMESPACES ('http://<NameSapcename>' as ns1)
    SELECT * FROM <TableName> FOR XML RAW('ns1:<RootNodeName>'), ELEMENTS

    I am using the above syntax for all 4 set of dataset/XML. My Receive port is receiving only 1 XML with below Property. XMLStoredProcedureRootNodeName and XMLStoredProcedureRootNodeNamespace 

    Regards,
    Saurin

    Monday, July 17, 2017 6:13 AM
  • Ok, most likely, the problem is that you can return one FOR XML results set when calling XML procedures.

    What you have are 4 FOR XML queries and the Adapter is return only the first as first class Xml and ignoring the rest.

    To return all 4 query results, you need to wrap all those in another outer FOR XML query.

    • Proposed as answer by bhvyshah Tuesday, July 25, 2017 12:31 PM
    Monday, July 17, 2017 3:05 PM
    Moderator