Я попробовал следующие способы и не смог найти способ заменить значение пустого узла, не удаляя его и не используя атрибуты XML.
Заменить узел недоступен в MS-SQL.
DECLARE @doc xml
SET @doc = '
<f1>1</f1>
<f2/>
'
print '1) ' + convert(varchar(4000),@doc)
SET @doc.modify('
replace value of (/f1/text())[1]
with (''v2'')
')
print '2) ' + convert(varchar(4000),@doc)
SET @doc.modify('
replace value of (/f1/text())[1]
with (''v3'')
')
print '3) ' + convert(varchar(4000),@doc)
SET @doc.modify('
replace value of (/f2/text())[1]
with (''v4'')
')
print '4) ' + convert(varchar(4000),@doc) + ' did not replace f2 value'
begin try
exec sp_executesql N'
SET @doc.modify(''
replace value of (/f2)[1] with (''''v5'''')
'')
print ''5) '' + convert(varchar(4000),@doc)
',N'@doc xml',@doc
end try begin catch
print '5) ' + error_message()
end catch
begin try
exec sp_executesql N'
SET @doc.modify(''
replace value of (/f2/node())[1]
with (''''v6'''')
'')
print ''6) '' + convert(varchar(4000),@doc)
',N'@doc xml',@doc
end try begin catch
print '6) ' + error_message()
end catch
begin try
exec sp_executesql N'
SET @doc.modify(''
replace value of (/f2)[1]
with (''''<f2>v7/f2>'''')
'')
print ''7) '' + convert(varchar(4000),@doc)
',N'@doc xml',@doc
end try begin catch
print '7) ' + error_message()
end catch
SET @doc.modify('
delete (/f2)
')
SET @doc.modify('
insert <f2>v8</f2> as last
into (.)[1]
')
print '8) ' + convert(varchar(4000),@doc) + ' only delete and add works'
/*
output:
1) <f1>1</f1><f2/>
2) <f1>v2</f1><f2/>
3) <f1>v3</f1><f2/>
4) <f1>v3</f1><f2/> did not replace f2 value
5) XQuery [modify()]: The target of 'replace value of' must be a non-metadata attribute or an element with simple typed content, found 'element(f2,xdt:untyped) ?'
6) XQuery [modify()]: The target of 'replace value of' cannot be a union type, found '(element(*,xdt:untyped) | comment | processing-instruction | text) ?'.
7) XQuery [modify()]: The target of 'replace value of' must be a non-metadata attribute or an element with simple typed content, found 'element(f2,xdt:untyped) ?'
8) <f1>v3</f1><f2>v8</f2> only delete and add works
*/
