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.googlegr oups.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 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 athttp://www.microsoft.com/info/cpyright.htm
> <doctorp...@.gmail.com> wrote in message
> news:1177013246.440921.259810@.d57g2000hsg.googlegr oups.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 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
<doctorphan@.gmail.com> wrote in message
news:1177078051.463036.85330@.n76g2000hsh.googlegro ups.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.googlegro ups.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?
>
sql