Indenting a sub-category in FOR XML PATH in SQL Server?

Question

I need to export data from SQL Server into an XML file. I know how to output to a text file, but I'm having difficulty with the XML formatting.

Sample Data:

CREATE TABLE dbo.testdata (accountid varchar(100),
                           activeaccount bit,
                           text1 varchar(100),
                           text2 varchar(100))

INSERT INTO dbo.testdata VALUES ('T00000001',0,'Fish' ,'Blue'),
                                ('T00000002',1,'Bread','Red' ),
                                ('T00000003',1,'Onion','Blue')

My XML export code:

SELECT accountid AS '@ID',
       activeaccount,
       text1,
       text2
  FROM dbo.testdata
FOR XML PATH ('acccountid'), root ('detail')

The output of that export:

<detail>
   <acccountid ID="T00000001">
      <activeaccount>0</activeaccount>
      <text1>Fish</text1>
      <text2>Blue</text2>
   </acccountid>
   <acccountid ID="T00000002">
      <activeaccount>1</activeaccount>
      <text1>Bread</text1>
      <text2>Red</text2>
   </acccountid>
   <acccountid ID="T00000003">
      <activeaccount>1</activeaccount>
      <text1>Onion</text1>
      <text2>Blue</text2>
   </acccountid>
</detail>

What I want is for the "text1" and "text2" fields to be indented into a sub-category of accountid, named "preferences", so the output would look like this:

<detail>
   <acccountid ID="T00000001">
      <activeaccount>0</activeaccount>
      <preferences>
         <text1>Fish</text1>
         <text2>Blue</text2>
      </preferences>
   </acccountid>
   <acccountid ID="T00000002">
      <activeaccount>1</activeaccount>
      <preferences>
         <text1>Bread</text1>
         <text2>Red</text2>
      </preferences>
   </acccountid>
   <acccountid ID="T00000003">
      <activeaccount>1</activeaccount>
      <preferences>
         <text1>Onion</text1>
         <text2>Blue</text2>
      </preferences>
   </acccountid>
</detail>

This should be obvious but everything I try in the FOR XML PATH command is putting the fields in the wrong order, not working at all or adding extra stuff where I don't want it. Driving me slightly crazy.

Answer 1

One method would be to define it in the alias of the column:

SELECT accountid AS '@ID',
       activeaccount,
       text1 AS [preferences/text1],
       text2 AS [preferences/text2]
  FROM dbo.testdata
FOR XML PATH ('acccountid'), root ('detail');