Selecting column containing XML into FOR XML query

I have a temporary table that has a column 'Header' that contains XML, that looks something like this:

<Header>
    <RecordID>Value</RecordID>
    <CompanyCode>Value</CompanyCode>
</Header>

I am then selecting that temp table column into a FOR XML query, that looks like this:

SELECT
    Header
FROM
    #TempTable
FOR XML PATH ('Asn'), ROOT ('Ans'), ELEMENTS

However, this doubles up the header node, and results in an XML that looks like this:

<Ans>
  <Asn>
    <Header>&lt;Header&gt;&lt;RecordID&gt;value&lt;/RecordID&gt;&lt;CompanyCode&gt;value&lt;/CompanyCode&gt;&lt;&lt;/Header&gt;</Header>
  </Asn>
</Asns>

How can I stop the header tag from doubling up when I select it into my XML query?

Solution

You can query the elements that are subelements of the Header element. Use the query method on the XML value to do that.

Depending on how you're set up, you can select without a column name to remove the Header element generated from the column name (see the second query).

DECLARE @x XML='<Header>
    <RecordID>Value</RecordID>
    <CompanyCode>Value</CompanyCode>
</Header>';

DECLARE @t TABLE(Header XML);
INSERT INTO @t(Header)VALUES(@x);

SELECT
    Header.query('Header')
FROM
    @t
FOR XML PATH ('Asn'), ROOT ('Ans'), ELEMENTS ;

-- Alternatively, without query, depending on how you're set up
SELECT
    (SELECT Header)
FROM
    @t
FOR XML PATH ('Asn'), ROOT ('Ans'), ELEMENTS ;