locked
BizTalk: CDATA in response from request response call from Orchestration RRS feed

  • Question

  • <usp_Load_DetailResponse xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">
    <StoredProcedureResultSet0>
    <StoredProcedureResultSet0 xmlns="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/usp_Load_Detail">
    <UnNamedColumn0>
    <![CDATA[<A1><B1>0</B1></A1>]]>
    </UnNamedColumn0>
    </StoredProcedureResultSet0>
    </StoredProcedureResultSet0>
    <ReturnValue>0</ReturnValue>
    </usp_Load_DetailResponse>

    How do I eliminate the CDATA and get the desired nodes(<A1><B1>0</B1></A1>) as xml. 

    I modified the XSD and replaced the UnNamedColumn0 with <A1><B1>0</B1></A1> and the SP uses XML XPath to create the output.


    Raja

    Tuesday, October 9, 2018 4:36 PM

All replies

  • Can you change the SP to a prober design / xml output or is it as is?

    /Peter


    When asking a question please be as thoroughly as possible this will make it easier to assist you http://www.catb.org/esr/faqs/smart-questions.html

    Tuesday, October 9, 2018 6:09 PM
  • The thing is, CDATA is Xml so...you already have proper Xml.

    What problem are you having?

    Tuesday, October 9, 2018 6:46 PM
    Moderator
  • My map in Orchestration is expecting <A1><B1>0</B1></A1> as my response to the DB call.  But I get <UnNamedColumn0> node and inside that<![CDATA[

    I'm using a request response DB call in an Orchestartion. Sending in a parameter(Order Number) and expecting an ouput(details of an Order) using For XML PATH. 

    At one point, I had the below error. 

    Microsoft.ServiceModel.Channels.Common.MetadataException: Retrieval of Operation Metadata has failed while building WSDL at 'TypedProcedure/dbo/usp_Order_Detail' ---> System.Data.SqlClient.SqlException: Unnamed tables cannot be used as XML identifiers as well as unnamed columns cannot be used for attribute names. Name unnamed columns/tables using AS in the SELECT statement.


    Raja

    Tuesday, October 9, 2018 7:33 PM
  • I did follow these steps. 

    https://lajak.wordpress.com/2011/07/17/biztalk-configure-wcf-sql-adapter-for-outbound-operation-using-typed-stored-procedure/

    Is there any recommendation on the stored procedures with FOR XML PATH, so that can be consumed by the above steps. When I consume, I get <UnNamedColumn0> column.


    Raja

    Tuesday, October 9, 2018 7:37 PM
  • If you were asking to run the SP and see. I get the same output as desired. But when it comes through the WCF adapter with sqlbinding, the UnNamedColumn0 and  CDATA gets appended

    Raja

    Tuesday, October 9, 2018 7:52 PM
  • Can you modify or create your own Stored Procedure?
    Tuesday, October 9, 2018 7:54 PM
    Moderator
  • yes, I can. I tried few options, with FOR XML AUTO, XLSCHEMA and FOR XML PATH.

    Preference is  XML PATH to get hierarchical xml, as Order details can repeat.

    • Edited by Sax001 Tuesday, October 9, 2018 8:04 PM
    Tuesday, October 9, 2018 8:02 PM
  • Then you need to address the Unnamed Column.  That alone will likely solve the problem.
    Tuesday, October 9, 2018 8:33 PM
    Moderator
  • CREATE PROCEDURE [dbo].[usp_Order_Detail]
    @Ordernumber decimal(10,0)
    AS
    SET NOCOUNT ON
    BEGIN
    WITH XMLNAMESPACES(DEFAULT 'http://xyz.com/Order') 
    SELECT [Ordernumber]
          ,[OrderDate]
          ,[OrderSystem]
          ,[OrderType] 
      FROM [dbo].[Order_Master] 
      WHERE Ordernumber = '235'
    FOR XML PATH ('OrderHeader'), TYPE, ELEMENTS
    END

    this is the Stored Proc, i created to test and used 'Consume Adapter services' to create the Schema. The Response schema still come back with ns3:UnNamedColumn0


    Raja

    Tuesday, October 9, 2018 8:51 PM
  • If I go with XML Auto in the SP instead of XML PATH, In the Schema generated -  instead of  UnNamedColumn, I get another column by the name ns3:XML_F52E2B61-18A1-11d1-B105-00805F49916B

    Raja

    Tuesday, October 9, 2018 8:56 PM
  • Oh, I think you're choosing the wrong type when generating the Schema.  But....

    This is a very simple query and result set.  I would not use FOR XML.

    Take out the FOR XML and generate the schema as a Strongly Typed Procedure.

    Tuesday, October 9, 2018 9:32 PM
    Moderator