none
WCF-SQL stored procedure not giving desired output RRS feed

  • Question

  • Hi,I am working on WCF-SQL adapter,Taken Procedure objects as action in the add generated items wizard.My stored procedure will produce EmailAddress,Subject,body needs to be output.

    Iam getting the result from the stored procedure like this

    - <EmailServiceProcResponse xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">
    - <StoredProcedureResultSet0>
    - <StoredProcedureResultSet0 xmlns="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/EmailServiceProc">
      <XML_F52E2B61-18A1-11d1-B105-00805F49916B><EmailService EmailAddress="c3JpbGFrc2htaS5uYWdhbGxhQG9ha3Rvbi5jb20u" Subject="RXJyb3I=" Body="sadfdsasa"/></XML_F52E2B61-18A1-11d1-B105-00805F49916B>
      </StoredProcedureResultSet0>
      </StoredProcedureResultSet0>
      <ReturnValue>0</ReturnValue>
      </EmailServiceProcResponse>

    please any one suggest me how to get the actual values

     

     

    Monday, October 24, 2011 5:25 AM

Answers

  • Refer to following link about 'Executing Stored Procedures Having a FOR XML Clause'...

    http://msdn.microsoft.com/en-us/library/dd787898(v=bts.10).aspx

     

    [Note:
    Make sure you remove the xmlschema clause after you have executed the stored procedure to generate the schema. If you fail to do this, when you finally execute the stored procedure through BizTalk, you will again generate the schema in the response message. So, to get the response message as xml you must remove the xmlschema clause.]

     

    HTH.


    Don't forget to mark the post as answer or vote as helpful if it does, Regards - Sathish Krishnan
    Monday, October 24, 2011 7:43 AM

All replies

  • Can you check the values in Database are correct and also the values returned by the SP? Are these elements (EmailAddress, Subject & Body) are of string type in Respone Schema?


    Don't forget to mark the post as answer or vote as helpful if it does, Regards - Sathish Krishnan
    Monday, October 24, 2011 6:09 AM
  • Also, refer to the following link that explains different  ways to call SP from WCF-SQL Adapter.

    http://blogs.msdn.com/b/adapters/archive/2008/09/30/multiple-ways-to-generate-metadata-execute-stored-procedures-in-the-sql-adapter.aspx

     

    HTH.


    Don't forget to mark the post as answer or vote as helpful if it does, Regards - Sathish Krishnan
    Monday, October 24, 2011 6:19 AM
  • My Xml out is

    <xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
      <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
      <xsd:element name="EmailService">
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element name="EmailAddress">
              <xsd:simpleType>
                <xsd:restriction base="sqltypes:varbinary">
                  <xsd:maxLength value="100" />
                </xsd:restriction>
              </xsd:simpleType>
            </xsd:element>
            <xsd:element name="Subject">
              <xsd:simpleType>
                <xsd:restriction base="sqltypes:varbinary">
                  <xsd:maxLength value="50" />
                </xsd:restriction>
              </xsd:simpleType>
            </xsd:element>
            <xsd:element name="Body">
              <xsd:simpleType>
                <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" />
              </xsd:simpleType>
            </xsd:element>
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
    </xsd:schema>
    <EmailService xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
      <EmailAddress>c3JpbGFrc2htaS5uYWdhbGxhQG9ha3Rvbi5jb20u</EmailAddress>
      <Subject>RXJyb3I=</Subject>
      <Body>sadfdsasa</Body>
    </EmailService>

    i used for xml auto,XMLSChema,Elements,binary base64 in the stored procedure while executing.The stored procedure generate results as above and also the xsd structure.

    I want my output is like this

    - <ns0:EmailService xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo">
      <EmailAddress>GpM7</EmailAddress>
      <Subject>GpM7</Subject>
      <Body>Body_0</Body>
      </ns0:EmailService>

    This schema structure has copied from the above xsd and generated.

    I used this schema  for my stored proc response message.

    port is suspending ,the response structure not same as my desired structure.

     

     

    Monday, October 24, 2011 6:28 AM
  • If your SP is already returning XML, you should change the InboundOperationType on your receive adapter to "XmlPolling"

    Also remember to supply a root-name and namespace.

    Morten la Cour



    • Edited by la Cour Monday, October 24, 2011 7:00 AM
    Monday, October 24, 2011 6:52 AM
  • Refer to following link about 'Executing Stored Procedures Having a FOR XML Clause'...

    http://msdn.microsoft.com/en-us/library/dd787898(v=bts.10).aspx

     

    [Note:
    Make sure you remove the xmlschema clause after you have executed the stored procedure to generate the schema. If you fail to do this, when you finally execute the stored procedure through BizTalk, you will again generate the schema in the response message. So, to get the response message as xml you must remove the xmlschema clause.]

     

    HTH.


    Don't forget to mark the post as answer or vote as helpful if it does, Regards - Sathish Krishnan
    Monday, October 24, 2011 7:43 AM
  • I think what you are getting as output is the composite form ( XML Schema + XML Result Set).

    After you have generated the Request / Response Schema for your solution , you need to alter the SP by removing the  clause 'XMLSChema,Elements'

    from the SP and execute the same.

     - XMLSChema I is required for generating the schema only and if u keep it in the SP for run-time , every time your SP runs , the schema information would also come along with the result sets which is not desirable.


    In case my post helped you , please do indicate that :-) Amit Shukla http://blogs.msdn.com/b/bizintegration1/
    Monday, October 24, 2011 7:47 AM