Thursday, 24 November 2011

Null values passed via XML into a Stored Proc

If the XML that gets into your proc looks something like this:
'<ArrayOfFeeSaveRequest xmlns:xsd="" xmlns:xsi=">
<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=""/>’ onto the sp_xml_preparedocument command

For example:
EXEC sp_xml_preparedocument @XMLDoc OUTPUT,@XMLOtherFees,'<root xmlns:xsi=""/>'

Then in your WITH you need something like this:

                        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

No comments: