weird problem loading XML data

Question

weird problem loading XML data

sonia-834492

Ten Centuries

Points: 1087
More actions
August 4, 2008 at 5:50 pm

#94324

Hi,
I have encountered a weird problem while loading XML data. if the text in data contains this symbol ( ' ) then I get an error with my code. but if I remove this symbol from my data then there is no problem in loading.
for e.g. if the sample data is
[places]
[state id = "1"] you're in colorado[/state]
[state id = "2"]I'm in idaho[/state]
[/places]
the error it shows is -- incorrect syntax near 're'
whereas if i have the data as shown below without using the (') symbol then i dont get any errors
[places]
[state id = "1"] you are in colorado[/state]
[state id = "2"]I am in idaho[/state]
[/places]
the code i am using is
declare @idoc int
declare @doc varchar(100)
set @doc='
[places]
[state id = "1"] you're in colorado[/state]
[state id = "2"]I'm in idaho[/state]
[/places]
'
--- Create an internal representation of the XML document.
exec sp_xml_preparedocument @idoc output,@doc
--- Execute a SELECT statement that uses the OPENXML rowset provider
select *
from openxml(@idoc,'/places',1)
with (id int,
message ntext 'text()')
exec sp_xml_removedocument @idoc
I dont want to modify my data because it is a huge file . so is there any way i can do it. I am new to this and can't understand why it is happening like this
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Shah-858675 SSC Rookie Points: 41 More actions · Answer 1

hi,

i faced the same problem in past, when the ' (single quote) appear in a string sql server consider this as an end of a string so the preceding statements will not execute properly, but i found a solution.

i replaced my string with ' (single quote) to ` (i.e. replace([message],'''','`')) , and

at the time of selection once again i replaced my string with the original one

(i.e replace([message],'`',''''))

hope you will get your result too

🙂

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 2

Shah (9/23/2008)
hi,
i faced the same problem in past, when the ' (single quote) appear in a string sql server consider this as an end of a string so the preceding statements will not execute properly, but i found a solution.
i replaced my string with ' (single quote) to ` (i.e. replace([message],'''','`')) , and
at the time of selection once again i replaced my string with the original one
(i.e replace([message],'`',''''))
hope you will get your result too
🙂

Seems like it might be easier to just stuff another ' in the string on the way in, so when you select out of the db, you'll just get the one in return. (In other words, '' = ' within a text string in SQL, which you're doing above in your second replace)

SELECT 'This is a single quote: '' '

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."