SQL Adapter - NULL values - Peter Bradley

Hi,

I have a SQL adapter that obtains a row of data from SQL Server and passes
it in to a BizTalk orchestration.  All the fields except the primary key
(and one other) may be NULL in the database.  The fields that may be NULL
are marked as minOccurs="0" maxOccurs="1".  Everything works fine if every
column has a value, but if I set one of the columns to NULL, BizTalk
suspends the message in the Message Box and never passes it into the
orchestration.

How can I tell BizTalk to accept messages that have columns with NULL
values?

Thanks


Peter

SQL Adapter - NULL values - Markus Eßmayr

Hi Peter,

basically, NULL values shoudn't be a problem.
Are you using the generated schema or did you apply some manual changes
after generating it?

Could you also please post the complete error message from the EventLog,
that says that the message was suspended?

Max

SQL Adapter - NULL values - Peter Bradley

Ysgrifennodd Markus Eßmayr:

Hi Max,

Thanks very much for the reply.  Actually, I found a workaround today.
If I used attributes instead of elements, the error was no longer
generated, but on the other hand the null field was still excluded from
the output (from the SQL adapter).  If I then set the attribute to
'required', it passed a null string, so everything then worked.  I'd
rather have NULL than the null string, but sometimes I guess you have to
take what you get.

When I was using elements, I couldn't find a way of having the element
included when the field in the database was null.  For example, if I was
getting 3 fields from the db:


This would be fine, but say that field_2 was null in the db, I was getting:


I couldn't find a way to get:


As I say, I've moved on a bit, so the data you asked for is no longer
easily with me, but I will get it if you're interested in the problem.
I'm sure I'll meet this again in the future.

Thanks again,


Peter

SQL Adapter - NULL values - jonesb32

Another option is to use ISNULL() inside of your SQL.

SELECT Field_1, ISNULL(Field_2, ''), Field_3 FROM MyTable.

I try to reserve the ISNULL function for only those fields where it is
necessary, but it will ensure you get at least an empty element or attribute.

--
Brian
http://bajwork.spaces.live.com/

SQL Adapter - NULL values - Jan Eliasen

On Fri, 3 Aug 2007 22:16:05 -0700, jonesb321

Remember to only use this for string types, unless you really know
what you are doing. If the schema for Field_2 is of type int, an empty
string is actually not valid, and the XML can't be validated against
the schema.

--
eliasen, representing himself and not the company he works for.

Private blog: http://blog.eliasen.dk

Private email: jan@eliasen.dk

SQL Adapter - NULL values - Markus Eßmayr

Hello again,

another solution could be the following:

Executing
SELECT * FROM table FOR XML AUTO, ELEMENTS
does not create the elements for fields containing NULL, but
SELECT * FROM table FOR XML AUTO, ELEMENTS XSINIL
does. It creates the elements like this

This should also work!
But i think, this keyword needs SQLServer 2005 to be used.

Greetings,
Max