Problem in using & using in CDATA RSS

• • Reply
  

  kavita_khand...

  Star

  9767 Points

  1930 Posts

  Problem in using & using in CDATA

  Mar 20, 2012 10:10 AM|LINK

  I am not able to retrive proper data using the for XML path from SQl.
  If I have inserted data as <![CDATA[London]]> in table and, when i write select using for xml, it gives me error as
  XML parsing: line 5, character 75, illegal name character

  Please mark this post as Answer if it is of help to you!
  I would love to change the world, but they wont give me the source code.
• • Reply
  

  kuber.manral

  Contributor

  3051 Points

  714 Posts

  Re: Problem in using & using in CDATA

  Mar 20, 2012 11:26 AM|LINK

  Hi,

      try this like :

  Source : http://stackoverflow.com/questions/1429351/sql-server-xml-output-with-cdata

  declare @agent table
  (
      AgentID int,
      Fname varchar(5),
      SSN varchar(11)
  )
   
  insert into @agent
  select 1, 'Vimal', '123-23-4521' union all
  select 2, 'Jacob', '321-52-4562' union all
  select 3, 'Tom', '252-52-4563'
   
  SELECT
      1 AS Tag,
      NULL AS Parent,
      NULL AS 'Agents!1!',
      NULL AS 'Agent!2!AgentID',
      NULL AS 'Agent!2!Fname!Element',
      NULL AS 'Agent!2!SSN!cdata'
  UNION ALL
  SELECT
      2 AS Tag,
      1 AS Parent,
      NULL,
      AgentID,
      Fname,
      SSN
  FROM @agent
  FOR XML EXPLICIT

  Please "Mark As Answer;", if this Post helps you.
  Visit My Blog
• • Reply
  

  kuber.manral

  Contributor

  3051 Points

  714 Posts

  Re: Problem in using & using in CDATA

  Mar 20, 2012 11:30 AM|LINK

  Hi,

     May be this would better describe your Concern. You can use "Nodes" method of XML Datatype in SQL like :

   

  DECLARE @xml XML
   
  SET @xml = '<testData><id><![CDATA[112233]]></id><id><![CDATA[445566]]></id></testData>'
  
  select x.y.query('.')
  from @xml.nodes('testData/id') x(y)
  
  select x.y.value('.', 'NVARCHAR(MAX)')
  from @xml.nodes('testData/id') x(y)

  Please "Mark As Answer;", if this Post helps you.
  Visit My Blog
  • Edited by kuber.manral on Mar 20, 2012 11:31 AM
  • Edited by kuber.manral on Mar 20, 2012 11:31 AM
  • Marked as answer by kavita_khandhadia on Mar 21, 2012 06:19 AM
• • Reply
  

  kavita_khand...

  Star

  9767 Points

  1930 Posts

  Re: Problem in using & using in CDATA

  Mar 21, 2012 06:19 AM|LINK

  Thanks Kuber, It worked.

  Please mark this post as Answer if it is of help to you!
  I would love to change the world, but they wont give me the source code.