none
Please ensure that your input XML conforms to the schema for the operation RRS feed

  • Question

  • This is the error message I get...

    Microsoft.ServiceModel.Channels.Common.XmlReaderParsingException: The start element with name "Position" and namespace "http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo" was unexpected. Please ensure that your input XML conforms to the schema for the operation.

    Server stack trace:
       at System.ServiceModel.AsyncResult.End[TAsyncResult](IAsyncResult result)
       at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)
       at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)
       at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

    Exception rethrown at [0]:
       at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
       at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)
       at Microsoft.BizTalk.Adapter.Wcf.Runtime

    ...but first things first.

    0. I'm trying to send a collection of information to a stored procedure. Obviously I need some kind of structure for that. XML comes to mind.
    1. We are running BizTalk Server 2006 R2, the send port I am talking about is using the WCF-SQL adapter.
    2. I have a stored procedure which expect 4 parameters. One of them is either varchar(max) or xml. I tried it both ways. Whenever I generate a schema file for the stored procedure I end up with something like that:

    <xs:element minOccurs="0" maxOccurs="1" name="MyCollection" nillable="true" type="xs:string" />

    Looking at that piece of schema information I totally agree with the error message above. Any XML inside this node is parsed by the XML parser and it just doesn't recognize it.

    --> option 1: Is it possible to put XML inside a node which is not being parsed by the xml parser and will just be passed along? CDATA?
    --> option 2: Extend the xml schema definition.

    - <xs:complexType>
    - <xs:sequence>
      <xs:element minOccurs="0" maxOccurs="1" name="SomeSimpleInteger" nillable="true" type="xs:int" />
    - <xs:element minOccurs="0" maxOccurs="1" name="SomeSimpleString" nillable="true">
    - <xs:simpleType>
    - <xs:restriction base="xs:string">
      <xs:maxLength value="10" />
      </xs:restriction>
      </xs:simpleType>
      </xs:element>
    + <xs:element minOccurs="0" maxOccurs="1" name="AnotherSimpleString" nillable="true">
    - <xs:simpleType>
    - <xs:restriction base="xs:string">
      <xs:maxLength value="10" />
      </xs:restriction>
      </xs:simpleType>
      </xs:element>
    - <xs:element minOccurs="0" maxOccurs="1" name="MyCollection" nillable="true">
    - <xs:complexType>
    - <xs:sequence>
    - <xs:element minOccurs="0" maxOccurs="unbounded" name="Position">
    - <xs:complexType>
    - <xs:sequence>
      <xs:element minOccurs="0" maxOccurs="1" name="Id" type="xs:anyType" />
      <xs:element minOccurs="0" maxOccurs="1" name="Description" type="xs:anyType" />
      </xs:sequence>
      </xs:complexType>
      </xs:element>
      </xs:sequence>
      </xs:complexType>
      </xs:element>
      </xs:sequence>
      </xs:complexType>
     
    This looks good right. Well... it simply doesn't work!
     
    I also tried to use a typed stored procedure (with a typed xml parameter)... the schema generator didn't generate any other xsd than before and after editing the .xsd manually it didn't work either.
     
    Also... (fun fact...) the xml message attached to the biztalk transmission error validates successfully against my .xsd file in visual studio.
     
    Any hints are higly appreciated!

    Wednesday, June 29, 2011 7:16 AM

Answers

All replies

  • The xs:anyType nodes seem like the most concerning in the generated schema because there is no way for it to really parse those. Are those for the varchar(max) and xml parameters? Could you just show the parameter list for the stored proc (names can be changed)?

    Thanks,

     


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Wednesday, June 29, 2011 1:17 PM
    Moderator
  • Hello. Sadly xs:string makes no difference.

    stored procedure:

    CREATE PROCEDURE [dbo].[sp_BizTalk_DoSometing]
        @SomeSimpleInteger int,
        @SomeSompleString nvarchar(10),
        @AnotherSimpleString nchar(10),
        @MyCollection xml (or varchar(max))
    AS
    BEGIN [...]

    Wednesday, June 29, 2011 2:31 PM
  • Your parameter list does not show the "Position" parameter. Do you have the latest schema generated and deployed?

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Wednesday, June 29, 2011 2:53 PM
    Moderator
  • <?xml version="1.0" encoding="utf-8"?>
    <ns0:sp_BizTalk_DoSomething xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo" xmlns:ns3="http://schemas.datacontract.org/2004/07/System.Data">
     <ns0:SomeSimpleInteger>6977</ns0:SomeSimpleInteger>
     <ns0:SomeSimpleString>SUCCESS</ns0:SomeSimpleString>
     <ns0:AnotherSimpleString>0082935362</ns0:AnotherSimpleString>
     <ns0:MyCollection>
      <ns0:Position>
       <ns0:Id>1</ns0:Id>
       <ns0:Description>001000</ns0:Description>
      </ns0:Position>
      <ns0:Position>
       <ns0:Id>2</ns0:Id>
       <ns0:Description>002000</ns0:Description>
      </ns0:Position>
      <ns0:Position>
       <ns0:Id>3</ns0:Id>
       <ns0:Description>003000</ns0:Description>
      </ns0:Position>
      <ns0:Position>
       <ns0:Id>4</ns0:Id>
       <ns0:Description>004000</ns0:Description>
      </ns0:Position>
      <ns0:Position>
       <ns0:Id>5</ns0:Id>
       <ns0:Description>005000</ns0:Description>
      </ns0:Position>
     </ns0:MyCollection>
    </ns0:sp_BizTalk_DoSomething>
    


    This is what my output xml looks like (matching the schema described in my earlier postings).

    Do I need a "position" parameter in my sql sp parameter list? Position is a below "MyCollection". While the first 3 parameters have 1:1 relations in the biztalk mapping I use a scripting functoid (inline xslt) to generate my "MyCollection" structure. So my functoid simply points to "MyCollection". Isn't that sufficient?

    Thursday, June 30, 2011 7:41 AM
  • See this thread for the problem faced while trying to insert the XML data into SQL via WCF SQL Adapter.

     


    Mark As Answer or Vote As Helpful if My Reply Does, Regards, -Rohit
    Monday, July 11, 2011 4:26 AM
    Moderator