XML field update error

25 Aug 2006 5:38 PM

Alexander Korol

Hello I am using SQL Server 2005

When trying to update xml field that contains xml without schema I got the
following error:

XQuery: Only nillable elements or text nodes can be updated with empty
sequence

The code I was using:
Update interface set messages.modify(
          'replace value of (/Messages/Message[@Name =
sql:variable("@ProfileName")]/Properties/Property[@Key =
"Interval"]/@Value)[1]
          with sql:variable("@Interval")')
          Where InterfaceID = @InterfaceID

@ProfileName variable is int and its value is NULL and what I want to get
is: Value=""

Here is XML i was updating:

<Messages>
<Message Name="111" Type="0">
    <Properties>
      <Property Key="Interval" Value="10" />
      <Property Key="Frequency" Value="" />
    </Properties
</Message>
</Messages>

what i want to get is

<Messages>
<Message Name="111" Type="0">
    <Properties>
      <Property Key="Interval" Value="" />
      <Property Key="Frequency" Value="" />
    </Properties
</Message>
</Messages>

Thanks in advance.

25 Aug 2006 8:46 PM

Kent Tegels

Hello Alexander,

Yep, it means what it says: it can't nil out attribute, only a text node
or an nillable elements value can be set to a null value for an integer.

You can, however, do this:

declare @IntervalP 3
set @IntervalP = 3
declare @Interval varchar(10)
set @Interval = cast(@intervalP as varchar(10))
declare @ProfileName nvarchar(50)
set @ProfileName ='111'
declare @don xml
set @don = '<Messages><Message Name="111" Type="0"><Properties><Property
Key="Interval" Value="10" /><Property Key="Frequency" Value="" /></Properties></Message></Messages>'

set @don.modify('replace value of (/Messages/Message[@Name=sql:variable("@ProfileName")]/Properties/Property[@Key
="Interval"]/@Value)[1] with sql:variable("@Interval")')

select @don

Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/

11 Sep 2006 6:44 AM

Alexander Korol

Thanks a lot, Kent. It did help.