Re: replace value of empty element?

Tech-Archive recommends: Fix windows errors by optimizing your registry

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@xxxxxxxxx> wrote in message
news:1177078051.463036.85330@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Apr 19, 6:07 pm, "Denis Ruckebusch [MSFT]"
<denis...@xxxxxxxxxxxxxxxxxxxx> 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...@xxxxxxxxx> wrote in message

news:1177013246.440921.259810@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



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?- 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?



.

Relevant Pages

  • can openxml write multiple fields - 1 row?
    ... quantity attributes from the XML document. ... declare @doc varchar ... FROM OPENXML ... This routine only generates one int ...
    (microsoft.public.sqlserver.xml)
  • Re: XML performance extremely slow for no obvious reason
    ... But XML is also an issue, because there, too, ... the optimizer can't deal, but you can do less about it, I think. ... Though there's a nested loop join shown, ... declare @ids varbinary ...
    (microsoft.public.sqlserver.xml)
  • can openxml write multiple fields - 1 row?
    ... I got an xml routine from the Sql Server NG where I can ... delimiters in the string. ... Declare @iDoc Int ...
    (microsoft.public.sqlserver.xml)
  • Re: Shredding XML
    ... to determine which field values should be extracted from an XML doc ... The stored procedure works, but the process of dynamically gathering ... DECLARE keycolumns_cursor CURSOR LOCAL ... DECLARE @xPath varchar ...
    (microsoft.public.sqlserver.xml)
  • Re: For XML problem
    ... DECLARE @sql NVARCHAR ... DECLARE @data XML; ... ROOT )'; ... where CustomerID Like 'A%' ...
    (microsoft.public.sqlserver.programming)