none
WCF-SQL stored procedure result with escaped nodes RRS feed

  • Question

  • Hi Pros,

    I use the WCF-SQL adapter to call a stored procedure on sql-server. The procedure returns the result of a select statement via

    SELECT ...FOR XML RAW ('Document'), ROOT ('Documents');

    Works fine so far but when I try to parse the reponse message in my orchestration the xml result of the procedure seemes to be transmitted as string inside the response:

    <LookupResponse xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">
      <StoredProcedureResultSet0>
        <StoredProcedureResultSet0 xmlns="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/Lookup">
          <XML_F52E2B61-18A1-11d1-B105-00805F49916B>&lt;Documents&gt;&lt;Document doku_id="123123234"/&gt;&lt;Document doku_id="234234123"/&gt;&lt;/Documents&gt;</XML_F52E2B61-18A1-11d1-B105-00805F49916B>
        </StoredProcedureResultSet0>
      </StoredProcedureResultSet0>
      <ReturnValue>0</ReturnValue>
    </LookupResponse>

    Is there a way to avoid this behaviour?

    Replacing the escaped nodes like &lt; by < and converting the string to xml seems to be an ugly solution.

    Thanks in advance

    Wednesday, March 5, 2014 3:38 PM

Answers

  • In the binding properties, you have to set XmlStoredProcedureRootNodeName and XmlStoredProcedureRootNodeNamespace.  This causes the sqlBinding to treat the response as Xml.

    The values you specify here must match those added to the Schema generated for the return.

    • Marked as answer by Trombone Wednesday, March 5, 2014 4:12 PM
    Wednesday, March 5, 2014 3:55 PM

All replies

  • In the binding properties, you have to set XmlStoredProcedureRootNodeName and XmlStoredProcedureRootNodeNamespace.  This causes the sqlBinding to treat the response as Xml.

    The values you specify here must match those added to the Schema generated for the return.

    • Marked as answer by Trombone Wednesday, March 5, 2014 4:12 PM
    Wednesday, March 5, 2014 3:55 PM
  • it works, thank you

    Wednesday, March 5, 2014 4:13 PM