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 xmlns="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/usp_Load_Detail">

    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.


    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?


    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
  • 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.


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


    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.


    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


    Tuesday, October 9, 2018 7:52 PM
  • Can you modify or create your own Stored Procedure?
    Tuesday, October 9, 2018 7:54 PM
  • 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
  • CREATE PROCEDURE [dbo].[usp_Order_Detail]
    @Ordernumber decimal(10,0)
    WITH XMLNAMESPACES(DEFAULT 'http://xyz.com/Order') 
    SELECT [Ordernumber]
      FROM [dbo].[Order_Master] 
      WHERE Ordernumber = '235'
    FOR XML PATH ('OrderHeader'), TYPE, ELEMENTS

    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


    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


    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