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="
Draft
" />
<P N="Approved For Publishing" V="
T
" />
<P N="Concealed From Publishing" V="
F
" />
<P N="Management System" V="
OMS
BMS
" />
</R>')
,('<R>
<P N="Status" V="
Draft
" />
<P N="Approved For Publishing" V="
T
" />
<P N="Concealed From Publishing" V="
F
" />
<P N="Management System" V="
OMS
BMS
" />
</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
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="
Draft
" />
<P N="Approved For Publishing" V="
T
" />
<P N="Concealed From Publishing" V="
F
" />
<P N="Management System" V="
OMS
BMS
" />
</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>
