Using Xquery to replace a node value that is xsi:nil = "true"

I am trying to use XQuery in SQL Server 2005 to update xml saved in a column. Here is a sample of the data I need to update.

<Length>3</Length>
<Width>5</Width>
<Depth>6</Depth>
<Area xsi:nil="true" />
<Volume xsi:nil="true" />

I need to set the area and volume to values from a different table. I am creating a CTE for the update. There is other logic that I have omitted, but I have verified that the CTE contains the correct data for the update:

;with Volume (DocumentID, Volume) As
(
  Select DocumentID, Volume from tbl
)

and I am using the following XQuery SQL statement to try to update the table.

UPDATE tbl_Archive
   SET XML.modify(' declare namespace x="http://www.redacted.com";
   replace va开发者_如何转开发lue of  (/x:Document/x:Volume/text())[1]
   with sql:column("Volume.Volume")')
    From Volume where volume.documentID = tbl_Archive.DocumentID

I get 1 row affected, but when I look at the XML it hasn't changed, and I can't figure out what needs to be fixed to make it work. The node is untyped, if that makes any difference.

Update wont work if there's no text to replace.. the XPath /x:Document/x:Volume/text())[1] will return an empty set.

Try insert...

UPDATE tbl_Archive
   SET XML.modify(' declare namespace x="http://www.redacted.com";
   insert text {sql:column("Volume.Volume")}
   as first into  (/x:Document/x:Volume)[1]')
    From Volume where volume.documentID = tbl_Archive.DocumentID

..you'll then need to remove the nil="true" attribute..

Something like this maybe..

 update tbl_Archive set XML.modify('delete /*:Document/*:Volume[text()]/@xsi:nil')