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
