replace value of empty element?

Hi,
In SQL 2005, I have a XML with an one element with an empty value like
so:
<root>
<test></test>
</root>
When I try and replace the value of (/root/test) it does not seem to
work:
declare @.xml xml
set @.xml = '<root><test></test></root>'
set @.xml.modify('replace value of (/root/test/text())[1] with "test
new value"')
select @.xml
returns this:
<root>
<test></test>
</root>
If however the element has a value already like so:
<root>
<test>some old value here already</test>
</root>
and I re-rerun the statement above:
declare @.xml xml
set @.xml = '<root><test></test></root>'
set @.xml.modify('replace value of (/root/test/text())[1] with "test
new value"')
select @.xml
returns this:
<root>
<test>test new value</test>
</root>
Is something wrong there?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 = '<root><test></test></root>'
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
<doctorphan@.gmail.com> 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:
> <root>
> <test></test>
> </root>
> When I try and replace the value of (/root/test) it does not seem to
> work:
> declare @.xml xml
> set @.xml = '<root><test></test></root>'
> set @.xml.modify('replace value of (/root/test/text())[1] with "test
> new value"')
> select @.xml
> returns this:
> <root>
> <test></test>
> </root>
>
> If however the element has a value already like so:
> <root>
> <test>some old value here already</test>
> </root>
> and I re-rerun the statement above:
> declare @.xml xml
> set @.xml = '<root><test></test></root>'
> set @.xml.modify('replace value of (/root/test/text())[1] with "test
> new value"')
> select @.xml
> returns this:
> <root>
> <test>test new value</test>
> </root>
> Is something wrong there?
>|||On Apr 19, 6:07 pm, "Denis Ruckebusch [MSFT]"
<denis...@.online.microsoft.com> wrote:
> In the first case, when you use expression (/root/test/text())[1] you refe
r to
> something that doesn't exist (there is no text node under element test) an
d
> therefore nothing gets updated.
> What you need to do is insert a new text node inder element "test" like th
is
> declare @.xml xml
> set @.xml = '<root><test></test></root>'
> 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
> <doctorp...@.gmail.com> wrote in message
> news:1177013246.440921.259810@.d57g2000hsg.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
> - 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 the
re
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
<doctorphan@.gmail.com> wrote in message
news:1177078051.463036.85330@.n76g2000hsh.googlegroups.com...
> On Apr 19, 6:07 pm, "Denis Ruckebusch [MSFT]"
> <denis...@.online.microsoft.com> wrote:
>
> 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 = '<root><test></test></root>'
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
<doctorphan@.gmail.com> wrote in message
news:1177078051.463036.85330@.n76g2000hsh.googlegroups.com...
> On Apr 19, 6:07 pm, "Denis Ruckebusch [MSFT]"
> <denis...@.online.microsoft.com> wrote:
>
> 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?
>