Manipulating XML Nodes in SQL (Merging nodes)

Question

In SQL Server, how can you merge a collection of nodes into one? Take the XML below for example. How can I go from the format below, to having the multiple 'Attribute' nodes under a single 'Attributes' node?

<Thing>
    <Id>160</Id>
    <Attributes>
        <Attribute>
            <Id>2</Id>
            <Values>
                <Value>94</Value>
            </Values>
        </Attribute>
    </Attributes> -- **how can i remove this**
    <Attributes> -- **and this**
        <Attribute>
            <Id>4</Id>
            <Values>
                <Value>103</Value>
            </Values>
        </Attribute>
    </Attributes>
</Thing>

Edit: I have a 2nd part to this question Given this input:

<Thing>
    <Id>160</Id>
    <Attributes>
        <Attribute>
            <Id>2</Id>
            <Values>
                <Value>94</Value>
            </Values>
        </Attribute>
    </Attributes>
    <Attributes>
        <Attribute>
            <Id>2</Id>
            <Values>
                <Value>103</Value>
            </Values>
        </Attribute>
    </Attributes>
    <Attributes>
        <Attribute>
            <Id>4</Id>
            <Values>
                <Value>106</Value>
            </Values>
        </Attribute>
    </Attributes>
</Thing>

How can I merge the attributes to achieve this output, where values are also merged based on a matching ID?

<Thing>
    <Id>160</Id>
    <Attributes>
        <Attribute>
            <Id>2</Id>
            <Values>
                <Value>94</Value>
                <Value>103</Value>
            </Values>
        </Attribute>
        <Attribute>
            <Id>4</Id>
            <Values>
                <Value>106</Value>
            </Values>
        </Attribute>
    </Attributes>
</Thing>

Answer 1

This is an approach with FLWOR-XQuery:

DECLARE @xml XML=
N'<Thing>
    <Id>160</Id>
    <Attributes>
        <Attribute>
            <Id>2</Id>
            <Values>
                <Value>94</Value>
            </Values>
        </Attribute>
    </Attributes> 
    <Attributes> 
        <Attribute>
            <Id>4</Id>
            <Values>
                <Value>103</Value>
            </Values>
        </Attribute>
    </Attributes>
</Thing>';

--The query will re-create the XML according to your needs:

SELECT @xml.query
(
N'
    <Thing>
    {Thing/Id}
    <Attributes>
    {
        for $a in //Attributes/Attribute
        return $a
    }
    </Attributes>
    </Thing>
'
)

The result

<Thing>
  <Id>160</Id>
  <Attributes>
    <Attribute>
      <Id>2</Id>
      <Values>
        <Value>94</Value>
      </Values>
    </Attribute>
    <Attribute>
      <Id>4</Id>
      <Values>
        <Value>103</Value>
      </Values>
    </Attribute>
  </Attributes>
</Thing>

UPDATE Your follow-up question

In this case I'd rather shred and re-create the full XML:

DECLARE @xml XML=
N'<Thing>
<Id>160</Id>
<Attributes>
    <Attribute>
        <Id>2</Id>
        <Values>
            <Value>94</Value>
        </Values>
    </Attribute>
</Attributes>
<Attributes>
    <Attribute>
        <Id>2</Id>
        <Values>
            <Value>103</Value>
        </Values>
    </Attribute>
</Attributes>
<Attributes>
    <Attribute>
        <Id>4</Id>
        <Values>
            <Value>106</Value>
        </Values>
    </Attribute>
</Attributes>
</Thing>';


WITH attribs AS
(
    SELECT a.value('Id[1]','int') AS Id
          ,v.value('.','int') AS Value
    FROM @xml.nodes('/Thing/Attributes/Attribute') AS A(a)
    OUTER APPLY a.nodes('Values/Value') AS B(v)
)
,distinctAttribIDs AS
(
    SELECT DISTINCT Id FROM attribs
)
SELECT @xml.value('(/Thing/Id)[1]','int') AS Id
      ,(
            SELECT da.Id
                  ,(
                    SELECT a.Value
                    FROM attribs AS a
                    WHERE a.Id=da.Id
                    FOR XML PATH(''), ROOT('Values'),TYPE
                   ) 
            FROM distinctAttribIDs AS da
            FOR XML PATH('Attribute'),ROOT('Attributes'),TYPE
       )
FOR XML PATH('Thing')