Replace an entire parent element in an XML data type

I have the following XML data within a SQL table (simplified for example):

DECLARE @x xml = '<Table>
  <Row>
    <Column>CustomerNo</Column>
    <Column>Name</Column>
  </Row>
  <Row>
    <Column>1</Column>
    <Column>Bob</Column>
  </Row>
   <Row>
    <Column>2</Column>
    <Column>Jill</Column>
  </Row>
</Table>'

I would like to replace the first row with the following:

DECLARE @replace xml = '<Table>
  <Row>
    <Column>Customer No</Column>
    <Column>Name</Column>
  </Row>
</Table>'

Such that the final result is this:

<Table>
  <Row>
    <Column>Customer No</Column>
    <Column>Name</Column>
  </Row>
  <Row>
    <Column>1</Column>
    <Column>Bob</Column>
  </Row>
   <Row>
    <Column>2</Column>
    <Column>Jill</Column>
  </Row>
</Table>

What is the best way to do this using an XQuery? I'm thinking .modify(). I've tried a couple of things with "replace value of" (see one attempt below) but I keep getting errors (e.g., "The target of 'replace value of' must be a non-metadata attribute or an element with simple typed content").

SET @x.modify('replace value of (/Table)[1] with "<Row>
                                                    <Column>Customer No</Column>
                                                    <Column>Name</Column>
                                                  </Row>"')

SELECT @x

Is it possible to do this with a single XQuery? Do I need to do use two .modify queries ("delete" then "insert as first")?

Solution

Assuming that SQL Server variable of XML data type is holding XML.

Please try the following two methods. I personally prefer Method #1 as more reliable.

SQL

DECLARE @x xml = '<Table>
  <Row>
    <Column>CustomerNo</Column>
    <Column>Name</Column>
  </Row>
  <Row>
    <Column>1</Column>
    <Column>Bob</Column>
  </Row>
   <Row>
    <Column>2</Column>
    <Column>Jill</Column>
  </Row>
</Table>';

DECLARE @replaceWith VARCHAR(30) = 'Customer No';

-- Method #1
-- based on find-and-replace value
SET @x.modify('replace value of (/Table/Row/Column[text()="CustomerNo"]/text())[1]
   with sql:variable("@replaceWith")');

-- Method #2
-- based on location of XML elements
SET @x.modify('replace value of (/Table/Row[1]/Column[1]/text())[1]
   with sql:variable("@replaceWith")');

--test
SELECT @x;

If you really need to replace the entire XML fragment, please try the following approach via FLWOR expression.

SQL

SELECT @x.query('<Table>
{
    for $x in /Table/*
    let $pos := count(/Table/*[. << $x[1]]) + 1
    return if ($pos eq 1) then 
        <WhateverNeeded>...</WhateverNeeded>
        else $x
}
</Table>');