Using SQLXMLBulkLoad, getting exception because column does not accept NULL values

Question

I am trying to bulk insert XML Data into SQL Server 2005 Express with the SQLXMLBulkLoad Object Model in VB.NET using Visual Studio 2010.

When the loader gets to an element in my .xml file which does not hold a value, it throws an error because the corresponding table column in SQL Server is set to not contain null values. This was by design and instead of inserting NULL I need for the Loader to insert a blank space. I do not know how to go about doing this as this is my first time working with BulkLoad. Any suggestions?

The error.xml file text:

  <?xml version="1.0" ?> 
    <Result State="FAILED">
     <Error>
       <HResult>0x80004005</HResult> 
       <Description> <![CDATA[ No data was provided for column 'blah' on table 'blah', and this column cannot contain NULL values.]]></Description>
       <Source>General operational error</Source> 
       <Type>FATAL</Type> 
     </Error>
   </Result>

EDIT: http://social.msdn.microsoft.com/Forums/en-US/sqlxml/thread/bfa31c49-6ae5-4a5d-bcde-cd520e0cdf70/

This guy had the exact same problem as I am having, and was able to solve it by using objBl.Transaction = True. However, when I try that, I get an error that "Cannot bulk load because the file "This is a Local Temp File" could not be opened."

Answer 1

I am answering this for future users with this same circumstance. Albeit illogical, if you set your columns in the SQL Table to accept NULL values AND set the Default Value = (''), then the blank values in your XML file will become blanks instead of NULL values and you will no longer receive this error. I couldn't use objBl.Transaction = True because my server is a different computer than the computer that would run the app to perform the BulkLoad(). This CAN be solved by setting up a shared folder, but it was not an option in my circumstance. Therefore, the next best option was to do the above.