Copy a subset of xml from one column to another on sql server

Question

I have a table (on sqlserver05) with an xml column. The format of the column is similar to this:

<doc>
   <field name="a">foo</field>
   <field name="b">bar</field>
   <field name="c">fizz</field>
   <field name="d">buzz</field>
</doc>

I would like to copy a subset of that xml to a new xml column, for instance:

<doc>
   <field name="a">foo</field>
   <field name="c">fizz</field>
</doc>

How can I do so?

EDIT: this is what worked for me (based on the accepted answer)

UPDATE tbl
SET xml_SubColumn = 
    xml_Column.query('<doc>{//field[@name="a" or @name="c"]}</doc>')

Answer 1

Use XQuery:

declare @x xml;
set @x = '<doc>
   <field name="a">foo</field>
   <field name="b">bar</field>
   <field name="c">fizz</field>
   <field name="d">buzz</field>
</doc>';

select @x.query('<doc>{//field[@name="a" or @name="c"]}</doc>');

In your case apply the xquery to the XML column. Of course, adapt the XQuery to your specific case, my expression is just an example that assumes that your criteria is @name='a' or @name='b'.