Apostrophe causing issue in parsing XML

• Aparna-1

  SSCrazy

  Points: 2339

  More actions

  July 31, 2009 at 12:30 am

  #207526

  Hi XML Gurus,

  Apostrophe is causing my XML to break.

  declare @xml xml

  set @xml =

  '

  test1 '

  '

  select c.value('issuerCode[1]', 'varchar(20)') AS test

  from

  @xml.nodes('/root/issuers') AS T(c)

  Now instead of ' if I have ' it breaks.

  As ' is not a mandatory encoding character as per w3 standards the vendor who is generating the XML does not want to encode it. Is it a SQL issue or issue with my understanding?

• Aparna-1

  SSCrazy

  Points: 2339

  More actions

  August 3, 2009 at 11:32 pm

  #1034076

  I have figured that this is an issue only when we query from management studio.

  It is not an issue when querying using the xquery for a table having an xml field.

  For instance:

  create table

  #temp

  (

  xml xml

  )

  insert into #temp

  (xml)

  values

  (

  '

  oran''ge

  '

  )

  select

  xml.value('(/root/fruits/name)[1]', 'varchar(10)')

  from

  #temp

  drop table #temp