Using XQUERY/Modify to replace a piece of my XML with value from varchar field

Posted on 2008-11-09
Last Modified: 2012-05-05
I have a table:

  config as XML
  title as varchar(255)

In MyTable.config I have XML in the following structure:

<configuration pagetitle="myConfig">
      <component id="1" type="MyPiece" title="My Title" text="junk" />

I need a script to inject the value of MyTable.text into the text attribute of the component node in my config XML.

I know this is wrong, but I want to do something similar:
UPDATE MyTable SET config.configuration.column.row.component.title = title

I believe I want to use XQUERY and the modify function.

Question by:rcoast
    LVL 59

    Accepted Solution

    See if this helps:
    DECLARE @title NVARCHAR(255)
    SELECT @title = title FROM MyTable;
    UPDATE MyTable
    SET config.modify('replace value of (//@title)[1] with sql:variable("@title")')

    Open in new window

    LVL 59

    Expert Comment

    by:Kevin Cross
    You could make it more explicit by replacing //@title with:


    From my understanding these kinds of operations only can affect one node at a time, so you may need to build function if replacement need is more complex then you can call the user defined function passing XML so that each time it is only operating on one but you can be overall executing on an entire dataset.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    This video discusses moving either the default database or any database to a new volume.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now