BizTalk stored procedure request through xml elements instead of attributes

Question

I have to call a SP with a XML request. This one is autogenerated by the wizard as an XML document with an element like the following:

<xs:element name="sp_storedProcedureName">
  <xs:complexType>
        <xs:attribute name="Field1" type="xs:string"/>
        <xs:attribute name="Field2" type="xs:string"/>
         . . .  

Some of the SP parameters might be null. The attributes won't fit this scenario, since a null attribute means "no attribute", therefore a schema validation failure.

I manually recreated the request schema in this way:

<xs:element name="sp_storedProcedureName">
  <xs:complexType>
    <xs:sequence>
      <xs:element name="Field1" type="xs:string"/>
      <xs:element name="Field2" type="xs:string"/>
      <xs:element name="FieldN" type="xs:string" nillable="true"/>
       . . .

and the message created seems to be valid (LINT says so, map test fails), meaning that the values are:

<ns0:sp_storedProcedureName>
  <ns0:Field1>AB012345</ns0:Field1>
  <ns0:Field2>ZZ</ns0:Field2>
  <ns0:FieldN xsi:nil="true" />
   . . .

which is exactly what I need. When the message reaches the send port pipeline the transmission fails with the error:

HRESULT="0x80040e10"
Description="Procedure or function 'sp_storedProcedureName' expects parameter '@Field1', which was not supplied."

yet it's clear that the value is supplied by the xml, but I guess a SQL adapter can't read from an element, while is able to read from an attribute of the main element.

Is it the case?

My main question is: how can I pass null values to a stored procedure using an xml?

Answer 1

Change the stored procedure so that it defaults to NULL if that fields is not passed from BizTalk, which tends to be the behaviour of BizTalk if what you are mapping does not exist in the payload you are mapping from.