Null values passed via XML into a Stored Proc

If the XML that gets into your proc looks something like this:

'<ArrayOfFeeSaveRequest xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance>
...
...
<RunNumber xsi:nil="true">

Then you will not get nulls in the output from SELECT on the prepared document.
You have to add '<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>’ onto the sp_xml_preparedocument command

For example:

EXEC sp_xml_preparedocument @XMLDoc OUTPUT,@XMLOtherFees,'<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>'

Then in your WITH you need something like this:

WITH  (
                        FeeID INT,
                        StartDate DATETIME,
                        EndDate DATETIME 'EndDate[not(@xsi:nil = "true")]',   --ALLOW NULLS!!!!!!!!!!!
                        RecurringOrSingleFlag BIT,
                        TransactionTypeID INT,
                        IsRepresentedFee BIT,
                        HostFeeID NVARCHAR(40),
                        Amount DECIMAL(18,4),
                        Comments NVARCHAR(128),
                        DirectDebitID INT
                  )

ExecuteScalar

A tip from Si:
I’m not sure if this has been mentioned before BUT did you know that the ExecuteScalar method on an SqlCommand will cheerfully truncate any string returned through it? That means that XML returned from a stored procedure say may or may not be cut in half depending on its size. The solution is to use the ExecuteXmlReader method instead. Details in this link ExecuteScalar truncates