Problem when saving data using xml

Question

Hi,
I am saving the bulk data to the database using xml format from vb.net windows application

this is the procedure

                                                                       
 create Procedure sp_Employee                                                             
(                                                                                                                               
@XMLInventory XML                                                                                                                    
)                                                                
as                                                                
BEGIN             
set nocount on  
                                                                         
insert into Emp(EmpId,EmpName,RefId,Sal)                                                                            
select                                                                                                                    
MasterDet.Item.value('@EmpId','nvarchar(20)'),                                                              
MasterDet.Item.value('@EmpName','nvarchar(20)'),                                                              
MasterDet.Item.value('@RefId','nvarchar(20)'),                                                              
MasterDet.Item.value('@Sal','money')                                                                   
from @XMLInventory.nodes('/Root/MasterRow') as MasterDet(Item)                                                               
                                                      
end  

from front end using string builder i am passing the parameter value to the procedure

that parameter value is

'<Root><MasterRow  EmpId=''E0001'' EmpName=''abc''  RefNo=''F&I'' Sal=''10000'' /></Root>'

 

but i am getting the error

XML parsing: line 1, character 58, semicolon expected

 

i found that the error is comming because of passing "F&I" value in the parameter value

if i pass only "FI"  i am not getting error

means the error comming because of passing "&" in the parameter value

so if i want to save the values like "F&I" how can i save into the database using xml

Please help me...

waiting for reply....

Answer 1

There's an & escape code in HTML, is it the same in XML? I think it's the entity &amp; i.e 5 chars starting with & ending with ;

---

Regards David R
---------------------------------------------------------------
The great thing about Object Oriented code is that it can make small, simple problems look like large, complex ones.
Object-oriented programming offers a sustainable way to write spaghetti code. - Paul Graham.
Every program eventually becomes rococo, and then rubble. - Alan Perlis
The only valid measurement of code quality: WTFs/minute.

Answer 2

I use &amp; in LINQ to XML, same as Riced indicated. Resources Use ampersand in CAST in SQL and XML Literals: Escaping Characters

---

KSG

Answer 3

Thank You Riced, Kevininstructor for reply

i am replacing &amp; in the place of &

so it is saving without error