OPENXML and NULLs
-
If I have an empty tag in my xml e.g. <StartDate/>, why does the value in my date field get set to 1900-01-01 rather than NULL?
-
To get a null datetime value, you need to omit the tag completely or specify the null value explicitly. For example, you could use a case statement:
...
select case StartDate
when '' then null
else StartDate
end
from OpenXML(...)<oops tags wrong>
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Edited by - mia on 11/26/2003 07:28:59 AM
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. -
I was hoping that wouldn't be the answer. Wonder why they did it like that - surely specifying a blank tag equates to an unknown value.
How would I specify NULL in the XML? Won't <StartDate>NULL</StartDate> attempt to put the text "NULL" in the field (didn't try yet)?
-
The empty StartDate tag equates to an empty string. Inserting an empty string value into a datetime column, even when that column accepts nulls, will insert the value '1900-01-01' into the column. Not really sure why that is although I'm sure there's an explanation somewhere!
Putting the text value NULL in the StartDate tag will result in a data type conversion error as it tries to convert that string to a date. You can't actually specify null in the XML in this case. I think the only options are to omit the tag from the XML when it has no value, which will equate to a null value, or use a case statement to specify null in the database when the StartDate tag is empty. If that isn't helpful, maybe post some code & see if that sheds any light?
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. -
As you say - "Life Moves Fast"
So I've already started adding case statements to all my procs. I had hoped to pass the buck to the business layer programmer but he says he is just calling the serializable attribute of a C# object and can't omit blank tags...
For what it's worth, here's my code..
UPDATE tmd..TaskActions
SET ActionDesc = xDescription,
StartDate = case xStartDate when '' then null else xStartDate end,
EndDate = case xEndDate when '' then null else xEndDate end,
Status = case xStatus when '' then null else xStatus end,
Officer = case xOfficer when '' then null else xOfficer end,
AssociatedOfficers = case xSecondOfficer when '' then null else xSecondOfficer end,
Location = case xPlace when '' then null else xPlace end,
Results = case xResults when '' then null else xResults end,
ByWhom = current_user,
WhenMod = getdate()
FROM OPENXML (@pidoc, '/Action')
WITH (xActionint'@ActionID',
xDescription varchar(1000) 'Description',
xStartDatedatetime'StartDate',
xEndDatedatetime'EndDate',
xStatusvarchar(40)'Status',
xOfficervarchar(40)'Officer',
xSecondOfficervarchar(40)'SecondOfficer',
xPlacevarchar(25)'Place',
xResultsvarchar(4000)'Results')
WHERE ActionID = xAction
-
Just realised this! Rather than the long-winded case statement, you might be able to use the nullif() function. Sorry I didn't mention this sooner!
i.e. instead of:
StartDate = case xStartDate when '' then null else xStartDate end
you would have:
StartDate = nullif(xStartDate, '')
In this case, life moved too fast
- sorry again!Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. -
Never heard of NULLIF but looks just right. Damn, I should slow down a bit. I've got 6 procs to change again.
Thanks!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply