T-SQL: update a specific XML node by searching for a sibling (peer)

Question

I have XML like the following, representing SQL table data:

<Project>
    <DataSource version="4" type="LiveDatabaseSource">
        <ServerName>(local)</ServerName>
    </DataSource>
    <Tables>
        <value>
            <TableType>Generated</TableType>
            <PopulationDetails version="2" type="PopulationDetails">
                <PopulationType>RowCount</PopulationType>
                <RowCount>10000</RowCount>
                <ProportionTableExists>False</ProportionTableExists>
                <Proportion>0</Proportion>
                <TimeToPopulate>0</TimeToPopulate>
            </PopulationDetails>
            <Name>Table1</Name>
            <Schema>dbo</Schema>
        </value>
        <value>
            <TableType>Generated</TableType>
            <PopulationDetails version="2" type="PopulationDetails">
                <PopulationType>RowCount</PopulationType>
                <RowCount>10000</RowCount>
                <ProportionTableExists>False</ProportionTableExists>
                <Proportion>0</Proportion>
                <TimeToPopulate>0</TimeToPopulate>
            </PopulationDetails>
            <InvalidRowBehaviour>SkipRow</InvalidRowBehaviour>
            <Included>False</Included>
            <Append>False</Append>
            <Name>Table2</Name>
            <Schema>dbo</Schema>
        </value>
    </Tables>
</Project>

This is in a single XML column in SQL Server, and there are many more <value> entries representing tables in the database. I need to update PopulationDetails.RowCount by searching for the value in Name: i.e. I want to update Table2 to a row count of 60,000 using T-SQL.

I can find the correct node via Name, but I'm having trouble updating the value in its peer PopulationDetails.Name node. All I have so far is the ability to update the nth record:

UPDATE dbo.SQLGenXML
SET GenXML.modify('replace value of 
(/Project/Tables/value/PopulationDetails/RowCount/text())    [1] with ("60000")')
WHERE id = 1;

There are a lot of examples out there for finding nodes based on attributes and updating the value, but not this type of peer level search (or my google-foo sucks).

Answer 1

Here is what you are looking for.

SQL

-- DDL and sample data population, start
DECLARE @SQLGenXML TABLE (ID INT IDENTITY PRIMARY KEY, GenXML XML);
INSERT INTO @SQLGenXML (GenXML)
VALUES
(N'<Project>
    <DataSource version="4" type="LiveDatabaseSource">
        <ServerName>(local)</ServerName>
    </DataSource>
    <Tables>
        <value>
            <TableType>Generated</TableType>
            <PopulationDetails version="2" type="PopulationDetails">
                <PopulationType>RowCount</PopulationType>
                <RowCount>10000</RowCount>
                <ProportionTableExists>False</ProportionTableExists>
                <Proportion>0</Proportion>
                <TimeToPopulate>0</TimeToPopulate>
            </PopulationDetails>
            <Name>Table1</Name>
            <Schema>dbo</Schema>
        </value>
        <value>
            <TableType>Generated</TableType>
            <PopulationDetails version="2" type="PopulationDetails">
                <PopulationType>RowCount</PopulationType>
                <RowCount>10000</RowCount>
                <ProportionTableExists>False</ProportionTableExists>
                <Proportion>0</Proportion>
                <TimeToPopulate>0</TimeToPopulate>
            </PopulationDetails>
            <InvalidRowBehaviour>SkipRow</InvalidRowBehaviour>
            <Included>False</Included>
            <Append>False</Append>
            <Name>Table2</Name>
            <Schema>dbo</Schema>
        </value>
    </Tables>
</Project>');
-- DDL and sample data population, end

UPDATE @SQLGenXML
SET GenXML.modify('replace value of 
(/Project/Tables/value[Name="Table2"]/PopulationDetails/RowCount/text())[1] with ("60000")');

-- test
SELECT * FROM @SQLGenXML;