Empty values in XML

Hi,
Trying to pass XML as parameter to stored procedure and get the result set.
I want to allow null values. Here is the problem: when I leave a value blank
(between the <Score> and </Score> tags) it will return 0 instead of <NULL>
... See code below.
What am I doing wrong here?
Thanks in advance!
Joost
CREATE PROCEDURE dbo.Test
AS
DECLARE @.i int
DECLARE @.ScoresXML nvarchar (4000)
SET @.ScoresXML = '<Scores><Score><ID>1</ID><Result></Result></Score></Scores
>'
EXEC sp_xml_preparedocument @.i OUTPUT, @.ScoresXML
SELECT
ID,
Result
FROM
OPENXML (@.i, '/Scores/Score', 2)
WITH
(ID int,
Result float)
EXEC sp_xml_removedocument @.iHello JoostK,
SQL Server 2000 or SQL Server 2005?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||SQL 2000
Thanks,
Joost
"Kent Tegels" <ktegels@.develop.com> schreef in bericht
news:b87ad741c6fb8c81b916f5cc940@.news.microsoft.com...
> Hello JoostK,
> SQL Server 2000 or SQL Server 2005?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||Hi Kent,
Using SQL2000
Thanks, Joost
"Kent Tegels" wrote:

> Hello JoostK,
> SQL Server 2000 or SQL Server 2005?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||Hello JoostK,

> Hi,
> Trying to pass XML as parameter to stored procedure and get the result
> set. I want to allow null values. Here is the problem: when I leave a
> value blank (between the <Score> and </Score> tags) it will return 0
> instead of <NULL> ... See code below.
> What am I doing wrong here?
In your table, you're asking "what's the value of the Score element", which
isn't null because it does exist. What you want is "what's the value of
the text node of the element Score". To fix this, all you need do is write
your table as:
WITH
(ID int,
Result int 'text()')
And it should give you what you want.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/