replace value of empty element?

In the first case, when you use expression (/root/test/text())[1] you refer to
something that doesn't exist (there is no text node under element test) and
therefore nothing gets updated.

What you need to do is insert a new text node inder element "test" like this

declare @xml xml

set @xml = ''

set @xml.modify('insert text{"test new value"} as first into (/root/test)[1] ')

select @xml


I hope this helps.

Denis Ruckebusch
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm


wrote in message
news:1177013246.440921.259810@d57g2000hsg.googlegroups.com...
> Hi,
>
> In SQL 2005, I have a XML with an one element with an empty value like
> so:
>
>
>
>
>
> When I try and replace the value of (/root/test) it does not seem to
> work:
>
> declare @xml xml
> set @xml = ''
> set @xml.modify('replace value of (/root/test/text())[1] with "test
> new value"')
> select @xml
>
> returns this:
>
>
>
>
>
> If however the element has a value already like so:
>
>
> some old value here already
>
>
> and I re-rerun the statement above:
>
> declare @xml xml
> set @xml = ''
> set @xml.modify('replace value of (/root/test/text())[1] with "test
> new value"')
> select @xml
>
> returns this:
>
> test new value
>
>
> Is something wrong there?
>

On Apr 19, 6:07 pm, "Denis Ruckebusch [MSFT]"
wrote:
> In the first case, when you use expression (/root/test/text())[1] you refer to
> something that doesn't exist (there is no text node under element test) and
> therefore nothing gets updated.
>
> What you need to do is insert a new text node inder element "test" like this
>
> declare @xml xml
>
> set @xml = ''
>
> set @xml.modify('insert text{"test new value"} as first into (/root/test)[1] ')
>
> select @xml
>
> I hope this helps.
>
> Denis Ruckebusch
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified athttp://www.microsoft.com/info/cpyright.htm
>
> wrote in message
>
> news:1177013246.440921.259810@d57g2000hsg.googlegroups.com...
>
>
>
> > Hi,
>
> > In SQL 2005, I have a XML with an one element with an empty value like
> > so:
>
> >
> >
> >
>
> > When I try and replace the value of (/root/test) it does not seem to
> > work:
>
> > declare @xml xml
> > set @xml = ''
> > set @xml.modify('replace value of (/root/test/text())[1] with "test
> > new value"')
> > select @xml
>
> > returns this:
> >
> >
> >
>
> > If however the element has a value already like so:
>
> >
> > some old value here already
> >
>
> > and I re-rerun the statement above:
>
> > declare @xml xml
> > set @xml = ''
> > set @xml.modify('replace value of (/root/test/text())[1] with "test
> > new value"')
> > select @xml
>
> > returns this:
> >
> > test new value
> >
>
> > Is something wrong there?- Hide quoted text -
>
> - Show quoted text -


So I guess there is no one built-in statement that will work in both
scenarios? ie. One that will insert the text if one doesn't exist, and
one that will replace the text if it already exists. I guess I will
have to write a function?

You should probably use the XML datatype's exist() method to determin if there
is already a text node or not and then perform the proper action depending on
what case you're in.

Denis Ruckebusch
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

wrote in message
news:1177078051.463036.85330@n76g2000hsh.googlegroups.com...
> On Apr 19, 6:07 pm, "Denis Ruckebusch [MSFT]"
> wrote:
>> In the first case, when you use expression (/root/test/text())[1] you refer
>> to
>> something that doesn't exist (there is no text node under element test) and
>> therefore nothing gets updated.
>>
>> What you need to do is insert a new text node inder element "test" like this
>>
>> declare @xml xml
>>
>> set @xml = ''
>>
>> set @xml.modify('insert text{"test new value"} as first into (/root/test)[1]
>> ')
>>
>> select @xml
>>
>> I hope this helps.
>>
>> Denis Ruckebusch
>> --
>> This posting is provided "AS IS" with no warranties, and confers no rights.
>> Use of included script samples are subject to the terms specified
>> athttp://www.microsoft.com/info/cpyright.htm
>>
>> wrote in message
>>
>> news:1177013246.440921.259810@d57g2000hsg.googlegroups.com...
>>
>>
>>
>> > Hi,
>>
>> > In SQL 2005, I have a XML with an one element with an empty value like
>> > so:
>>
>> >
>> >
>> >
>>
>> > When I try and replace the value of (/root/test) it does not seem to
>> > work:
>>
>> > declare @xml xml
>> > set @xml = ''
>> > set @xml.modify('replace value of (/root/test/text())[1] with "test
>> > new value"')
>> > select @xml
>>
>> > returns this:
>> >
>> >
>> >
>>
>> > If however the element has a value already like so:
>>
>> >
>> > some old value here already
>> >
>>
>> > and I re-rerun the statement above:
>>
>> > declare @xml xml
>> > set @xml = ''
>> > set @xml.modify('replace value of (/root/test/text())[1] with "test
>> > new value"')
>> > select @xml
>>
>> > returns this:
>> >
>> > test new value
>> >
>>
>> > Is something wrong there?- Hide quoted text -
>>
>> - Show quoted text -
>
>
> So I guess there is no one built-in statement that will work in both
> scenarios? ie. One that will insert the text if one doesn't exist, and
> one that will replace the text if it already exists. I guess I will
> have to write a function?
>

Try the following:

declare @xml xml

set @xml = ''

set @xml.modify('replace value of (/root/test/text())[1] with "test

new value"')

set @xml.modify('insert text{ "test

new value"} into (/root/test[not(text())])[1]')

select @xml



Best regards

Michael

wrote in message
news:1177078051.463036.85330@n76g2000hsh.googlegroups.com...
> On Apr 19, 6:07 pm, "Denis Ruckebusch [MSFT]"
> wrote:
>> In the first case, when you use expression (/root/test/text())[1] you
>> refer to
>> something that doesn't exist (there is no text node under element test)
>> and
>> therefore nothing gets updated.
>>
>> What you need to do is insert a new text node inder element "test" like
>> this
>>
>> declare @xml xml
>>
>> set @xml = ''
>>
>> set @xml.modify('insert text{"test new value"} as first into
>> (/root/test)[1] ')
>>
>> select @xml
>>
>> I hope this helps.
>>
>> Denis Ruckebusch
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified
>> athttp://www.microsoft.com/info/cpyright.htm
>>
>> wrote in message
>>
>> news:1177013246.440921.259810@d57g2000hsg.googlegroups.com...
>>
>>
>>
>> > Hi,
>>
>> > In SQL 2005, I have a XML with an one element with an empty value like
>> > so:
>>
>> >
>> >
>> >
>>
>> > When I try and replace the value of (/root/test) it does not seem to
>> > work:
>>
>> > declare @xml xml
>> > set @xml = ''
>> > set @xml.modify('replace value of (/root/test/text())[1] with "test
>> > new value"')
>> > select @xml
>>
>> > returns this:
>> >
>> >
>> >
>>
>> > If however the element has a value already like so:
>>
>> >
>> > some old value here already
>> >
>>
>> > and I re-rerun the statement above:
>>
>> > declare @xml xml
>> > set @xml = ''
>> > set @xml.modify('replace value of (/root/test/text())[1] with "test
>> > new value"')
>> > select @xml
>>
>> > returns this:
>> >
>> > test new value
>> >
>>
>> > Is something wrong there?- Hide quoted text -
>>
>> - Show quoted text -
>
>
> So I guess there is no one built-in statement that will work in both
> scenarios? ie. One that will insert the text if one doesn't exist, and
> one that will replace the text if it already exists. I guess I will
> have to write a function?
>