use xmldb
GO

-- change the value of an XML variable
DECLARE @x XML
-- initialize it
SET @x = '<some>initial XML</some>'
-- now, mutate it 
-- in this case delete all of the some nodes at the root
SET @x.modify('delete /some')

SELECT @x
GO



-- declare XML variable and set its initial value
DECLARE @x xml
SET @x =
'<Invoice>
<InvoiceID>1000</InvoiceID>
<CustomerName>Jane Smith</CustomerName>
<LineItems>
<LineItem>
<Sku>134</Sku>
<Quantity>10</Quantity>
<Description>Chicken Patties</Description>
<UnitPrice>9.95</UnitPrice>
</LineItem>
<LineItem>
<Sku>153</Sku>
<Quantity>5</Quantity>
<Description>Vanilla Ice Cream</Description>
<UnitPrice>1.50</UnitPrice>
</LineItem>
</LineItems>
</Invoice>'

-- You could insert a new InvoiceDate element as a child of the Invoice
-- element using the following statement.
SET @x.modify('insert <InvoiceDate>2002-06-15</InvoiceDate>
into /Invoice[1]')

SELECT @x

-- To insert it as the first child of Invoice, simply
-- change the statement to the following.

SET @x.modify('insert <InvoiceDate>2002-06-15</InvoiceDate>
as first
into /Invoice[1]')

-- Here's an example of inserting an attribute, status="backorder", on
-- the Invoice element.
SET @x.modify('insert attribute status{"backorder"}
into /Invoice[1]')

SET @x.modify('insert
(
<LineItem>
<Sku>154</Sku>
<Quantity>20</Quantity>
<Description>Chocolate Ice Cream</Description>
<UnitPrice>1.50</UnitPrice>
</LineItem>
)
into /Invoice[1]/LineItems[1]')

SET @x.modify('insert <InvoiceDate>2002-06-15</InvoiceDate>
before /Invoice[1]/CustomerName[1]')

SELECT @x

-- this works too, and equates to the same position
SET @x.modify('insert <InvoiceDate>2002-06-15</InvoiceDate>
after /Invoice[1]/InvoiceID[1]')

SELECT @x

SET @x.modify('delete /Invoice/LineItems/LineItem')

SELECT @x

SET @x.modify('update value of
/Invoice[1]/CustomerName[1]/text()[1]
to "John Smith"
')

SELECT @x