Updating XML type column with condition

Question

Hopefully a quick question to be answered by the XML experts. I have a the following table with a XML type column with the below XML schema and data in one record:

INSERT INTO XMLTEST (testXML)
values ('<R>
  <P N="Status" V="&#xD;&#xA;Draft&#xD;&#xA;" />
  <P N="Approved For Publishing" V="&#xD;&#xA;T&#xD;&#xA;" />
  <P N="Concealed From Publishing" V="&#xD;&#xA;F&#xD;&#xA;" />
  <P N="Management System" V="&#xD;&#xA;OMS&#xD;&#xA;BMS&#xD;&#xA;" />
</R>')

I need to conditionally replace the 'Approved For Publishing' value to F, and I thought the best was was to check against the N value name of the xml row first before changing the V value. I have been trying to get the below SQL to work but no luck. Can someone help point out what is wrong?

DECLARE @myDoc xml
SET @myDoc = (SELECT TOP 1 CAST(testXML AS VARCHAR(MAX)) FROM XMLTEST) 
Select @myDoc
SET @myDoc.modify('  
  replace value of (/R/P/@V)[1]  
  with (  
       if (/R/P/N = ''Approved For Publishing'') then  
         "F"  
       else  
          "T"  
      )  
')  
SELECT @myDoc 

Answer 1

New answer

The xml special characters 
 and 
 are nothing more than a new line (a Line Feed (LF) and a carriage return (CR)). I do not think, that this should really live within the XML's value. Line feeds should rather be added in your presentation layer.

The following code shows the way how to update a tabel without the need of a variable or any cast.

DECLARE @tbl TABLE(ID INT IDENTITY, testXML XML);
INSERT INTO @tbl (testXML)
values ('<R>
  <P N="Status" V="&#xD;&#xA;Draft&#xD;&#xA;" />
  <P N="Approved For Publishing" V="&#xD;&#xA;T&#xD;&#xA;" />
  <P N="Concealed From Publishing" V="&#xD;&#xA;F&#xD;&#xA;" />
  <P N="Management System" V="&#xD;&#xA;OMS&#xD;&#xA;BMS&#xD;&#xA;" />
</R>')
,('<R>
  <P N="Status" V="&#xD;&#xA;Draft&#xD;&#xA;" />
  <P N="Approved For Publishing" V="&#xD;&#xA;T&#xD;&#xA;" />
  <P N="Concealed From Publishing" V="&#xD;&#xA;F&#xD;&#xA;" />
  <P N="Management System" V="&#xD;&#xA;OMS&#xD;&#xA;BMS&#xD;&#xA;" />
</R>');

UPDATE @tbl SET testXML=testXML.query(
                                    N'
                                        <R>
                                        {
                                        for $p in /R/P
                                        return
                                        if($p/@N="Approved For Publishing") then
                                         <P N="{$p/@N}" V="F"/>
                                        else
                                         <P N="{$p/@N}" V="{substring($p/@V,3,string-length($p/@V)-4)}"/>
                                        }
                                        </R>
                                    ');
SELECT * FROM @tbl;

One of the XMLs will look like this after the action:

<R>
  <P N="Status" V="Draft" />
  <P N="Approved For Publishing" V="F" />
  <P N="Concealed From Publishing" V="F" />
  <P N="Management System" V="OMS&#xD;&#xA;BMS" />
</R>

The new line between OMS and BMS remains in this approach.

Previous answer

Some remarks / questions first?

• Why do you need to cast this to VARCHAR(MAX) and then to XML (implicitly)?
• Do you need to update the values within the table or is this a temporary action?
• You cannot update more than one value at once with .modify()...
• You should always use NVARCHAR if you deal with XML!
• Is there a need for the variable @myDoc?

You can try it with a FLWOR XQuery:

DECLARE @tbl TABLE(testXML XML);
INSERT INTO @tbl (testXML)
values ('<R>
  <P N="Status" V="&#xD;&#xA;Draft&#xD;&#xA;" />
  <P N="Approved For Publishing" V="&#xD;&#xA;T&#xD;&#xA;" />
  <P N="Concealed From Publishing" V="&#xD;&#xA;F&#xD;&#xA;" />
  <P N="Management System" V="&#xD;&#xA;OMS&#xD;&#xA;BMS&#xD;&#xA;" />
</R>');

DECLARE @myDoc xml
SET @myDoc = (SELECT TOP 1 CAST(testXML AS VARCHAR(MAX)) FROM @tbl) 

SELECT @myDoc.query(
N'
    <R>
    {
    for $p in /R/P
    return
    if($p/@N="Approved For Publishing") then
     <P N="{$p/@N}" V="F"/>
    else
     <P N="{$p/@N}" V="T"/>
    }
    </R>
')

The result

<R>
  <P N="Status" V="T" />
  <P N="Approved For Publishing" V="F" />
  <P N="Concealed From Publishing" V="T" />
  <P N="Management System" V="T" />
</R>

Answer 2

In the end the below ended up working based on Shnugo's feedback, Thanks! It was a huge help for me to understand XQUERY a bit better!!

I needed to update the XML type column contents in SQL, and the below is the fundamental working piece required as part of my overall solution to bulk modify values in an old legacy system i'm working with. I'm sure there is a more elegant solution, but i had to set a variable with the xml special characters to retain the string literal in the column itself - for some reason I couldn't get it working in line after the 'then' portion of the if block.

I also change it so that it only modifies the value hitting the conditional check and retains the same data for the rest.

DECLARE @myDoc xml
SET @myDoc = (SELECT TOP 1 testXML FROM XMLTEST) 
UPDATE XMLTEST
SET testXML = (SELECT @myDoc.query(
N'
    <R>
    {
    let $str := "&#xD;&#xA;F&#xD;&#xA;"
    for $p in /R/P
    return
    if($p/@N="Approved For Publishing") then
     <P N="{$p/@N}" V="{$str}"/>
    else
     <P N="{$p/@N}" V="{$p/@V}"/>
    }
    </R>
'))
SELECT * FROM XMLTEST

XML output after where 'Approved For Publishing' value is set to 'F' from 'T' retaining special XML characters.

<R>
  <P N="Status" V="&#xD;&#xA;Draft&#xD;&#xA;" />
  <P N="Approved For Publishing" V="&#xD;&#xA;F&#xD;&#xA;" />
  <P N="Concealed From Publishing" V="&#xD;&#xA;F&#xD;&#xA;" />
  <P N="Management System" V="&#xD;&#xA;OMS&#xD;&#xA;BMS&#xD;&#xA;" />
</R>