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
                  )