August 15, 2017 at 8:08 am
Hello,
I am working on XML Files,Trying to load data from XML Files to DB.
I wrote a SP which can read the XML files and the issue is reading special & Unicode characters such as ®, ü, à, ‘, –, ö, é, “bucket list tour”,é, ’, í, ê, ú, ó, á, À ,™ , æ.
This files are directly from Web and load is scheduled everyday and there are many other characters coming.
Below is the SP and i don't want to change the code because i am using same code for many other files.
Can anyone help me out ??
Is there any Replace function for these type of characters in SQL 2008
SP
ALTER PROCEDURE [dbo].[usp_ReadActivityInfo]
(@vXMLSource VARCHAR(MAX)
)
AS BEGIN
Declare @vRefID INT
Set @vXMLSource=Replace(@vXMLSource,'&','&')
Set @vXMLSource=Replace(@vXMLSource,'''',''')
Set @vXMLSource=REPLACE(@vXMLSource,'®','®')
exec sp_xml_preparedocument @vRefID OUTPUT,@vXMLSource
insert into dbo.ActivityInfo
Select
*
From
OpenXML(@vRefID,'report/bookings/booking',2)
With
(
address_1 varchar(50) './travellers/traveller/address_info/address_1',
address_2 varchar(50) './travellers/traveller/address_info/address_2',
agent_id varchar(50) './agent_id'
)
exec sp_xml_removedocument @vRefID
END
August 15, 2017 at 8:33 am
No, there is no generic replace function that you can use for this, you have to write your own. Also, if you know you are dealing with UNICODE data you really should be using nvarchar(max) instead of varchar(max). You will lose Unicode data when it is implicitly converted to ASCII. I have experienced that issue where I work when developers haven't fully thought things out and try to use varchar and nvarchar interchangeably.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply