OPENXML and NULL values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

OPENXML and NULL values

Author

Topic

lynnbt
Starting Member

9 Posts

Posted - 2002-07-15 : 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

15732 Posts

Posted - 2002-07-15 : 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 - 2002-07-15 : 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

15732 Posts

Posted - 2002-07-15 : 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

846 Posts

Posted - 2002-07-15 : 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