| Author |
 Topic  |
|
|
lynnbt
Starting Member
9 Posts |
 Posted - 07/15/2002 : 11:21:15
|
I am using OPENXML to perform bulk updates within a stored procedure.
My question is:
How can update a database column to a NULL value using this method??
I've tried things like:
<dbrecord id="123" myvalue="NULL"/>
and
<dbrecord id="123" myvalue=""/>
... but of course sql server treats the myvalue field as a string literal. So if I want "myvalue" to be updated to NULL in the database, how do I do that using this OPENXML method???
Thanks in advance for the help ....
|
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 07/15/2002 : 12:34:41
|
Well, it's not perfect, but why not just UPDATE the table after you do the OPENXML:
UPDATE myTable SET myValue=NULL
WHERE myValue='NULL'
...or...
UPDATE myTable SET myValue=NULL
WHERE myValue=''
I don't imagine that you need to differentiate between empty strings, 'NULL', and Null values.
I think the only way for it to work without doing something like this is to remove the myvalue attribute from the XML, and that causes its own problems.
|
 |
|
|
lynnbt
Starting Member
9 Posts |
Posted - 07/15/2002 : 12:50:20
|
Thanks for the advice, but this won't work for me.
In this example, the "myvalue" field is a foreign key to another table, so "myvalue" must contain a valid value or a NULL. So if try to stick a string literal in the field (like "NULL"), and then go back and do a "real" update to set those records to really be null, then first OPENXML update fails b/c the string literal "NULL" violates the FK constraint.
Any other ideas ... there has to be a way to do this.
|
|
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 07/15/2002 : 13:09:54
|
Do you have to use XML to perform these updates? If so, I would suggest importing the XML into a staging table that can allow for the UPDATE, and then updating your SQL table using the staging table and not the direct XML. If I think of anything else I'll post it here, but you should at least give that a shot.
It's important to realize that XML and relational databases are not designed to accomplish the same thing. There is going to be a mismatch in their abilities, and therefore there doesn't "have to be" a way to do what you're looking to do.
|
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
United Kingdom
846 Posts |
Posted - 07/15/2002 : 13:54:16
|
Use our old friend NULLIF()
create table #temp([id] int NOT NULL,myvalue char(1) NULL)
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<dbrecord id="123" myvalue="1"/>
<dbrecord id="124" myvalue=""/>
<dbrecord id="125" myvalue="3"/>
</ROOT>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
INSERT #temp([id],myvalue)
SELECT [id],NULLIF(myvalue,'')
FROM
OPENXML (@idoc, '/ROOT/dbrecord',1)
WITH ([id] int,
myvalue char(1))
EXEC sp_xml_removedocument @idoc
select * from #temp where myvalue IS NULL
drop table #temp
Results :
id myvalue
----------- -------
124 NULL
HTH
Jasper Smith |
|
|
|
lynnbt
Starting Member
9 Posts |
Posted - 07/15/2002 : 14:04:52
|
Jasper,
That's exactly what I'm looking for - thanks! Never used nullif() but it looks like it should do exactly what I need. Thanks again ...
|
|
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 07/15/2002 : 14:24:23
|
quote:
...therefore there doesn't "have to be" a way to do what you're looking to do
Jasper, thank you for proving me wrong! You da man!
|
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
United Kingdom
846 Posts |
Posted - 07/15/2002 : 15:38:37
|
Even a broken clock is right twice a day  
|
|
|
| |
Topic |
|
OPENXML and NULL values
Forum Locked
