SQL Server Performance Forum – Threads Archive

XML PrepareDocument

Hi All THis is my query: declare @xmlstring varchar(7500)
set @xmlstring ='<ROOT>
<GRAPH StDt="2006-02-02" EndDt="2006-02-02" />
<GRAPH StDt=" " EndDt="" />
<GRAPH StDt=" " EndDt="" />
<GRAPH StDt="2006-02-02" EndDt="2006-02-02" />
</ROOT> ‘
Declare @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmlString
INSERT INTO GDetails (
,StDt
,EndDt
) SELECT
,StDt
,EndDt FROM OPENXML(@hDoc,’/ROOT/GRAPH’)
WITH ( StDt datetime ‘@StDt’,
EndDt datetime ‘@EndDt’
) Problem is wherever there is emptyString in xml string like stdt="" i am getting
"1/1/1900" being inserted in the table column…. it should be inserted as null actually… How to solve this…. Regards
Rajesh. How should i do it

Can you specify the default value as NULL for that column? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

yes
already tha column default is null even though i t is inserting the default date

If you want it to be NULL, you shouldn’t pass an empty string to a DATETIME column. SQL Server will convert the empty string to the INT value 0, which in turn is also the INTEGER representation of SQL Server’s base date of 19000101. I’ve described this here:http://www.sql-server-performance.com/fk_datetime.asp —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

Sorry, it’s on top of the article’s second page here:http://www.sql-server-performance.com/fk_datetime2.asp —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

]]>