0

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?

Dijkgraaf
  • 11,049
  • 17
  • 42
  • 54
strongmmc
  • 137
  • 12
  • Are you declaring the namespaces properly? – Shnugo Nov 29 '16 at 09:53
  • @Shnugo yes. i only sobstitute the attributes with elements leaving the namespace the way it's been declared with the wizard. Otherwise i would receive transmission fails for the schema with the attributes as well – strongmmc Nov 30 '16 at 11:37
  • 1
    Change your stored procedure so you set the default value of Field1 to null. P.S. Why are you still developing for biztalk-2006? That is out of support. – Dijkgraaf Dec 07 '16 at 01:53
  • i thougth an empty element would be treated as a null value, especially when nil property is set... i use my sp parameters as a guide to know what's required and what is not, and this sp needs that Field1 to perform. (ansering your p.s. : very old systems maintenance) – strongmmc Dec 07 '16 at 08:59

1 Answers1

0

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.

Dijkgraaf
  • 11,049
  • 17
  • 42
  • 54