Great Articles, and we have incorporated from your articles into our projects!!!

I have a question on when our .net apps creates the xml to pass into our stored procedure, sometimes an element may not have a value which is fine, and we want to shred this xml into a relational table, but the empty element seems to be handled as an empty string versus a null. We want it to be null.

How we do it today:

DECLARE @x XML

SELECT @x = '

Employee

ContactInfo

FirstName Bob /FirstName

LastName /

/ContactInfo

/Employee'

SELECT

x.value('(FirstName)[1]','varchar(30)'),

x.value('(LastName)[1]','varchar(30)')

FROM @x.nodes('/Employee/ContactInfo') n(x)

Then we would use something like this from a our temp table to clean up data if it is empty string and replace with Null value.

IF @lastName = '' SET @lastName = NULL

So if you our xml has many parameters, our stored proc gets peppered with all these statements, so I am just looking for a better or clean way to do this?

Thanks,

Antonio