Modify XML in a field

• sharky

  Default port

  Points: 1494

  More actions

  October 24, 2013 at 8:34 am

  #278506

  Hi Hi Guys,

  The following is a Sample OF an XML field I want TO Modify:

  DECLARE @TestTAb AS TABLE(ID INT IDENTITY PRIMARY KEY, XMLText XML)

  INSERT INTO @TestTAb(XMLText) VALUES(

  '

  <main>

  <heading>"Quotes Should be removed" </heading>

  <heading2>"Quotes Should not be removed" </heading2>

  </main>

  ')

  INSERT INTO @TestTAb(XMLText) VALUES(

  '<main>

  <heading>Quotes "Should" be removed </heading>

  <heading2>Quotes "Should" not be removed </heading2>

  </main>')

  What is the easiest way TO do an update on the table and REMOVE ALL the quotes, but ONLY FOR heading 1?

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  October 24, 2013 at 9:01 am

  #1661333

  sharky (10/24/2013)

  ---

  Hi Hi Guys,

  The following is a Sample OF an XML field I want TO Modify:

  DECLARE @TestTAb AS TABLE(ID INT IDENTITY PRIMARY KEY, XMLText XML)

  INSERT INTO @TestTAb(XMLText) VALUES(

  '

  <main>

  <heading>"Quotes Should be removed" </heading>

  <heading2>"Quotes Should not be removed" </heading2>

  </main>

  ')

  INSERT INTO @TestTAb(XMLText) VALUES(

  '<main>

  <heading>Quotes "Should" be removed </heading>

  <heading2>Quotes "Should" not be removed </heading2>

  </main>')

  What is the easiest way TO do an update on the table and REMOVE ALL the quotes, but ONLY FOR heading 1?

  Something like: -

  UPDATE a

  SET XMLText.modify('replace value of (/main/heading/text())[1] with sql:column("N")')

  FROM (SELECT ID, XMLText, REPLACE(XMLText.value('(/main/heading/text())[1]','NVARCHAR(MAX)'),'"','') AS N

  FROM @TestTAb) a;

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• sharky

  Default port

  Points: 1494

  More actions

  October 24, 2013 at 9:08 am

  #1661336

  Thanks! Perfect..