+ Reply to Thread
Results 1 to 5 of 5

replace value of empty element?

  1. replace value of empty element?

    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?


  2. Re: 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?
    >




  3. Re: replace value of empty element?

    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?


  4. Re: replace value of empty element?

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




  5. Re: replace value of empty element?

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




+ Reply to Thread