SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 OPENXML and NULL values
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

lynnbt
Starting Member

9 Posts

Posted - 07/15/2002 :  11:21:15  Show Profile
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
15663 Posts

Posted - 07/15/2002 :  12:34:41  Show Profile  Visit robvolk's Homepage
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.

Go to Top of Page

lynnbt
Starting Member

9 Posts

Posted - 07/15/2002 :  12:50:20  Show Profile
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.

Go to Top of Page

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 07/15/2002 :  13:09:54  Show Profile  Visit robvolk's Homepage
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.

Go to Top of Page

jasper_smith
SQL Server MVP & SQLTeam MVY

United Kingdom
846 Posts

Posted - 07/15/2002 :  13:54:16  Show Profile  Visit jasper_smith's Homepage
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
Go to Top of Page

lynnbt
Starting Member

9 Posts

Posted - 07/15/2002 :  14:04:52  Show Profile
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 ...

Go to Top of Page

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 07/15/2002 :  14:24:23  Show Profile  Visit robvolk's Homepage
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!

Go to Top of Page

jasper_smith
SQL Server MVP & SQLTeam MVY

United Kingdom
846 Posts

Posted - 07/15/2002 :  15:38:37  Show Profile  Visit jasper_smith's Homepage
Even a broken clock is right twice a day



Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000