Post reply

SQL upgrading values in XML

  • March 30, 2016 at 12:56 pm

    #306083

    I have created code similar to below. This is slow since this is a very large table. Is there a way to make this into one call?

    UPDATE dbo.foo

    SETFooAttributes.modify('replace value of (//*[local-name()="FooDim"]//text())[1] with (//*[local-name()="FooDim"])[1] * 0.0069444444')

    WHERE FooType IN (0, 1)

    UPDATE dbo.foo

    SETFooAttributes.modify('replace value of (//*[local-name()="FooBooDim"]//text())[1] with (//*[local-name()="FooBooDim"])[1] * 0.0069444444 ')

    WHERE FooType IN (0, 1)

  • April 3, 2016 at 3:01 pm

    #1869383

    JKSQL (3/30/2016)

    I have created code similar to below. This is slow since this is a very large table. Is there a way to make this into one call?

    UPDATE dbo.foo

    SETFooAttributes.modify('replace value of (//*[local-name()="FooDim"]//text())[1] with (//*[local-name()="FooDim"])[1] * 0.0069444444')

    WHERE FooType IN (0, 1)

    UPDATE dbo.foo

    SETFooAttributes.modify('replace value of (//*[local-name()="FooBooDim"]//text())[1] with (//*[local-name()="FooBooDim"])[1] * 0.0069444444 ')

    WHERE FooType IN (0, 1)

    The short answer is no.

    😎

    You have two options, one is to run an update for each element/attribute that must be updated and the second one is to reconstruct the full XML and replace the existing XML value. The modify function can only be used for a single update on an XML column at a time.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply