OpenXML & SQL INSERTstatement - 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

SQL Server 2000 Forums

Transact-SQL (2000)

OpenXML & SQL INSERTstatement

Author

Topic

samj12
Starting Member

6 Posts

Posted - 2004-07-03 : 01:40:46

<customer id="10" name="mike" Age=""></customer>
<customer id="20" name="james" Age="34"></customer>

I have the above XML document that I am passing into my stored procedure. I am then inserting the XML elements into a database table using the Trans-SQL OPENXML.

Now my XML document can contain element attributes that are empty, such as the Age attribute. The Age field in the database is of type int and is nullable.

Now when my stored procedure below executes, 2 rows are inserted into the database table. One row with the Age field 0 and the second
34. Why is it defaulting to 0 for my Age(integer type field)?? - I also noticed it defaults to 0 for fields of type BIT. I just want it to behave like a normal TRANSQL INSERT statement where the field would become NULL if nothing is passed to it.

-------------------------------------------------------
DECLARE @idoc int
DECLARE @doc varchar(1000)
set @doc = '<ROOT><customer CustID="10" CustName ="mike" Age=""></customer><customer CustID="20" CustName ="james" Age="34"></customer></ROOT>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
INSERT INTO tblTest (CustID,CustName,Age)
SELECT *
FROM OPENXML(@idoc, '/ROOT/customer')
WITH (CustIDINT,
CustName VARCHAR(200),
Age INT)
EXEC sp_xml_removedocument
-------------------------------------------------------

Thanks in advance for all your help.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-07-03 : 07:44:21

This is the normal bahavior of the datatype:

create table t ( v int)
insert into t select ''
select * from t
drop table t

An empty string ("") is not nothing (NULL) and will be converted to the primitive datatype (if possible).


DECLARE @idoc int
DECLARE @doc varchar(1000)
set @doc = '<ROOT><customer CustID="10" CustName ="mike" Age=""></customer><customer CustID="20" CustName ="james" Age="34"></customer></ROOT>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc 
INSERT INTO tblTest (CustID,CustName,Age)
SELECT custid ,custname,case when age = '' then null else age end
FROM OPENXML(@idoc, '/ROOT/customer') 
WITH (CustID INT,CustName VARCHAR(200),Age INT)
EXEC sp_xml_removedocument @idoc

samj12
Starting Member

6 Posts

Posted - 2004-07-03 : 13:26:40

Ok now im slightly confused. I know that an empty string is not the same as NULL. But what is the difference between these two scenarios then:
table t (Address varchar(100) nullable)

1. A form text field(txtAddress) that is not filled by the user and is then inserted into the Address field of table t through a Transactsql INSERT statement in a stored procedure. Note that my ASP does not manipulate the text field. Simply uses Request.Form("txtAddress") and then uses the parameter object to pass the field to the stored procedure for insertion as is.
(RESULT: Database Address field contains NULL)

2. Using OpenXML to insert the address value from the XML document into the Address field.
<customer id="10" name="mike" Address=""></customer>
(RESULT: Database Address field contains an empty string)

Why the difference in these two scenarions???

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-03 : 14:34:44

Because this: "" is an empty string.

The txtAddress field you are using on the other hand is NULL until the user fills it out, so by just passing the existing parameter through, it passes a NULL.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

samj12
Starting Member

6 Posts

Posted - 2004-07-03 : 19:05:48

I used ASP IsNull on the txtAddress field after the user has left the field empty and it returns False. Which means that txtAddress field is not null but rather an empty string. But the result in the database field is always NULL when the field is left empty.

Shouldn't the insertion of the Address attribute in the XML document <root><customer Address=""></customer></root> using openxml and the Address field from the form using transact INSERT yiled the same outcome??? Either both should insert empty string or both should insert null. Thats not the case.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-03 : 21:00:27

Well, I don't know about your txtAddress field. I do know the XML syntax you're using will always put in an empty string though because that's what it is.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources