Is this a bug with decimal(19,2)?

Seems like I've run across a place where INT works and MONEY works and DECIMAL(19,2) fails.

So, I have two questions:

* Should it work?

* Is there a better practice to handle optionally missing values, I'd rather have NULL than 0 anyway.

(there is also an xsd that shows the element as optional, but that seems to make no difference).

Apologies if this is kind of a newbie question (outside of the bug), me being an XML newbie.

Thanks.

Josh

Code Snippet

declare @.myxml XML

set @.myxml =

'<root>

<myrec>

<foo>123</foo>

<bar>123</bar>

</myrec>

<myrec>

<foo>234</foo>

</myrec>

</root>'

this works

select

t.rows.query('foo').value('.','int') as foo

from @.myxml.nodes('/root/myrec') as t(rows)

this works

select

t.rows.query('foo').value('.','int') as foo,

t.rows.query('bar').value('.','int') as bar

from @.myxml.nodes('/root/myrec') as t(rows)

this works

select

t.rows.query('foo').value('.','money') as foo,

t.rows.query('bar').value('.','money') as bar

from @.myxml.nodes('/root/myrec') as t(rows)

this fails

select

t.rows.query('foo').value('.','decimal(19,2)') as foo,

t.rows.query('bar').value('.','decimal(19,2)') as bar

from @.myxml.nodes('/root/myrec') as t(rows)

/*

Msg 8114, Level 16, State 5, Line 27

Error converting data type nvarchar to numeric.

*/

Here is how you get a NULL with the value method and all three types (int, money, decimal) if the XPath expression does not find an element:

Code Snippet

declare @.myxml XML

set @.myxml =

'<root>

<myrec>

<foo>123</foo>

<bar>123</bar>

</myrec>

<myrec>

<foo>234</foo>

</myrec>

</root>'

this works

select

t.rows.value('foo[1]','int') as foo,

t.rows.value('bar[1]','int') as bar

from @.myxml.nodes('/root/myrec') as t(rows)

this works

select

t.rows.value('foo[1]','money') as foo,

t.rows.value('bar[1]','money') as bar

from @.myxml.nodes('/root/myrec') as t(rows)

this works

select

t.rows.value('foo[1]','decimal(19,2)') as foo,

t.rows.value('bar[1]','decimal(19,2)') as bar

from @.myxml.nodes('/root/myrec') as t(rows)

|||

Thanks.

Yes, I've seen that [1] syntax, and it seems to satisfy the value() function's demand for a singleton value, and then I don't need the separate query() method, ... but it seemed odd to me, didn't know how mainstream it was. Well, looks like it just got a lot more mainstream with me, thanks again!

Josh