Update XML column in T-SQL-CodePudding

I want to replace part of an xslt file stored in a xml column in a SQL Server database table.

I tried updating the entire column value using a syntax like below

UPDATE myTable
SET myColumn = '<user>
  <user_id>1</user_id>
  <fname>Josef</fname>
  <lname>Brown</lname>
  <email_>[email protected]</email_>
</user>'
WHERE id = 1

but since it has single and double quotes strewn all over the place, it doesn't work (SQL query parsing itself fails).

Is there a C# verbatim string equivalent in T-SQL?

If yes, then too easy, otherwise,

I probably have to search for my particular element which looks like below

  <div >
    <div >
      <xsl:if test="SomeElement/ChildElement='NULL'">

and replace it with

  <div >
    <div >
      <xsl:if test="SomeElement/ChildElement=''">

Basically, just replace NULL in the xsl:if element with an empty string

How do I do that? Any help appreciated.

CodePudding user response：

I found and replaced all occurrences of one single quote with two single quotes in the xml value - that way got my sql to parse fine and then used this syntax to update the row.

UPDATE myTable
SET myColumn = '<user>
  <user_id>1</user_id>
  <fname>Caroline</fname>
  <lname>O''Connor</lname>
  <email_>[email protected]</email_>
</user>'
WHERE id = 1