Conversion failed when converting the varchar value to data type int in SQL Server

0
votes

I have a problem. i have 2 identical code about inserting data from XML to table in SQL Server.

My first code:

declare
   @nDoc int,
   @cXML xml

select @cXML = '<root><rekening NomorCIF=''123'' NomorRek=''123456789'' StatusRek=''1'' MataUang=''IDR'' Saldo=''1000''></rekening></root>'

exec sp_xml_preparedocument @nDoc output, @cXML

insert into [50732Rekening_TM] select * from OPENXML (@nDoc, 'root/rekening', 1) with  (NomorCIF varchar(30), NomorRek varchar(30), StatusRek varchar(30), MataUang varchar(30), Saldo varchar(30))

exec sp_xml_removedocument @nDoc

This code runs smoothly

But this code returns an error

declare
    @nDoc int,
    @cXML xml,
    @query nvarchar(max)

select @cXML ='<root><rekening NomorCIF=''123'' NomorRek=''123456789'' StatusRek=''1'' MataUang=''IDR'' Saldo=''1000''></rekening></root>'

exec sp_xml_preparedocument @nDoc output, @cXML

set @query = 'insert into [50732Rekening_TM] select * from OPENXML ('+@nDoc+', ''root/rekening'', 1) with (NomorCIF varchar(30), NomorRek varchar(30), StatusRek varchar(30), MataUang varchar(30), Saldo varchar(30))'

exec sp_executesql @query
exec sp_xml_removedocument @nDoc

The error is

Msg 245, Level 16, State 1, Line 15
Conversion failed when converting the varchar value 'insert into [50732Rekening_TM] select * from OPENXML (' to data type int.

Why does the second code contain an error? I need it but I don't know how to fix it.

Thanks

2
sqlsql-serverxml
Well, @nDoc is an INT, so you can't just concatenate it with a string without converting it first. - Lamak
or add @nDoc as param into dynamic code - Darka
Just change it to select * from OPENXML ('+cast(@nDoc as varchar)+' - Rahul

2 Answers

1
votes

You must pass handle of the XML document as parameter

declare
    @nDoc int,
    @cXML xml,
    @query nvarchar(max)

select @cXML ='<root><rekening NomorCIF=''123'' NomorRek=''123456789'' StatusRek=''1'' MataUang=''IDR'' Saldo=''1000''></rekening></root>'

exec sp_xml_preparedocument @nDoc output, @cXML

set @query = N'insert into [50732Rekening_TM] select * from OPENXML (@nDoc, ''root/rekening'', 1) with (NomorCIF varchar(30), NomorRek varchar(30), StatusRek varchar(30), MataUang varchar(30), Saldo varchar(30))'
exec sp_executesql @query, N'@nDoc int', @nDoc = @nDoc
exec sp_xml_removedocument @nDoc
0
votes

You have to convert @nDoc int parameter to string because you can not concatenate int value with string @query variable.

declare
    @nDoc int,
    @cXML xml,
    @query nvarchar(max)

select @cXML ='<root><rekening NomorCIF=''123'' NomorRek=''123456789'' StatusRek=''1'' MataUang=''IDR'' Saldo=''1000''></rekening></root>'

exec sp_xml_preparedocument @nDoc output, @cXML

set @query = 'insert into [50732Rekening_TM] select * from OPENXML ('+convert(varchar,@nDoc)+', ''root/rekening'', 1) with (NomorCIF varchar(30), NomorRek varchar(30), StatusRek varchar(30), MataUang varchar(30), Saldo varchar(30))'
exec sp_executesql @query
exec sp_xml_removedocument @nDoc