Replace value of empty XML node in SQL Server's XQuery

Question

I tried the following ways and could not find a way to replace empty node value without deleting it and without using XML attributes.

Replace node is not available in 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
*/

Tweaked the subject line to make the implementation more visible, since it's rather critical to the question. (I could answer this for an implementation supporting XQUF, but MS... shrug). — Charles Duffy
– Charles Duffy, Commented Mar 14, 2014 at 22:31

Michael Shiels · Accepted Answer · 2016-11-03 17:30:09Z

1

Well I think something like this would do it

DECLARE @doc xml
SET @doc = '
    <f1>1</f1> 
    <f2/>
'
print '1) ' + convert(varchar(4000),@doc)


SET @doc.modify('
    insert text{"v9"} into (/f2)[1]
')
print '2) ' + convert(varchar(4000),@doc)

answered Nov 3, 2016 at 17:30

Michael Shiels

111 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Replace value of empty XML node in SQL Server's XQuery

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related