Query help

Transact-SQL
1

I have error when the input element in null in the xml

"Msg 8114, Level 16, State 5, Line 123 Error converting data type varchar to numeric."

XML:

Table Column: [ACCOUNTAMOUNT] [decimal](18, 2) NULL,

My Code:

Cast(ISNULL(Z.c.value('accountAmount[1]', 'varchar(50)'),0) as decimal(18,5)),

Please suggest..

2

That likely means that the xml node accountAmount has some values which cannot be converted to numeric. I am assuming that you are applying the XML nodes method to get a column named c and then taking the value function on that. So it is not clear to me what the [ACCOUNTAMOUNT] column is. One thing to note is that XML is case sensitive, so your node name has to use the exact case.

If none of what I said above helps you, post sample XML, the output you are looking for, and the query where you are constructing the column c

3

Thanks a lot the provided code resolved the issue..

I have another issue.

XML input file has no value for the element..

Mycode:
ISNULL(t.c.value('CreationDate[1]', 'date'), ''),

the output is showing as 1900-01-01 00:00:00.000

expect value should be null

XML input has no value..

Mycode:

ISNULL(t.c.value('accountName[1]', 'varchar(50)'),''),

Showing output as emptystring.

Expected output:NULL

4

Not sure what the solution is without seeing the source XML and doing some testing. Perhaps this?

Instead of

ISNULL(t.c.value('CreationDate[1]', 'date'), ''),

try

t.c.value('CreationDate[1]', 'date')

That is, remove the ISNULL function.