Post reply

xml modify

  • September 28, 2012 at 3:37 am

    #393275

    I need to update a specific value of a node in an xml column. Im trying to use the replace option of 'modify' but cant figure out the exact syntax.

    heres what i want to replace

    <Catalogue Name="UKArtists" Version="1" />

    I want to set the version to "2" for all the rows in the table for the xml column 'CatalogueXML'

    (this contains the xml)

    heres what ive got

    UPDATE

    dbo.Catalogues

    SET

    CatalogueXML.modify('replace value of (/Catalogue/Version) with "2"')

    anyone got any pointers ?

  • September 28, 2012 at 3:56 am

    This was removed by the editor as SPAM

  • September 28, 2012 at 4:14 am

    #1543301

    Im going to be specifying the version in a variable (Ill build a stored procedure because its to be run from within a .net app), so Ive now got this

    DECLARE @Ver VARCHAR(1)

    SET @Ver = '2'

    UPDATE dbo.Catalogues

    SET CatalogueXML.modify('replace value of (/Catalogue/@Version)[1] with ( sql:variable("@Ver") )')

    but it didnt update anything

    I got a message

    348 row(s) affected

    but the version is still the same

  • September 28, 2012 at 5:22 am

    #1543336

    ok, got it to work, the node is actually underneath a root node which id left out

Viewing 4 posts - 1 through 3 (of 3 total)

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