Update XML column in T-SQL

Question

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_>jo0@adventure-works.com</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 class="someClass">
    <div class="someOtherClass">
      <xsl:if test="SomeElement/ChildElement='NULL'">

and replace it with

  <div class="someClass">
    <div class="someOtherClass">
      <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.

Do not use string interpolation or concatenation to get values into SQL queries. That's error prone and might make your program vulnerable to SQL injection attacks. Use parameterized queries. See ["How can I add user-supplied input to an SQL statement?"](https://stackoverflow.com/questions/35163361/how-can-i-add-user-supplied-input-to-an-sql-statement). And do it properly, mind that ["AddWithValue is Evil"](https://www.dbdelta.com/addwithvalue-is-evil/). [`SqlDataType`](https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.sqldatatype?view=sql-smo-160) lists `Xml`. — sticky bit, Jan 07 '22 at 03:52

InquisitiveLad · Accepted Answer · 2022-01-07T05:28:40.723

0

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_>jo0@adventure-works.com</email_>
</user>'
WHERE id = 1

edited Jan 07 '22 at 05:28

answered Jan 07 '22 at 05:20

InquisitiveLad

309
3
16

Update XML column in T-SQL

1 Answers1