How can I query SQL Server and generate XML with column names and values as attributes

Question

I'm trying to figure out how to query a table that generates xml that looks like below: (this is a sample from the AdventureWorks database.

I can get the column names as the elements easy enough, but is it possible to make both the column name and value as attributes? I'm trying to figure out how to do this in a generic fashion, so I don't want to hardcode the column names using FOR EXPLICIT

    <TABLE name="StateProvince">
        <ROW>
            <COL name="StateProvinceID" value="1" />
            <COL name="StateProvinceCode" value="AB" />
            <COL name="CountryRegionCode" value="CA" />
            <COL name="IsOnlyStateProvinceFlag" value="0" />
            <COL name="Name" value="Alberta" />
            <COL name="TerritoryID" value="6" />
            <COL name="rowguid" value="298C2880-AB1C-4982-A5AD-A36EB4BA0D34" />
            <COL name="ModifiedDate" value="2004-03-11T10:17:21.587" />
        </ROW>
    </TABLE>

Answer 1

Doing this without specifying column names is a bit tricky but doable.
To use this you need to replace @T with your table name and change the table name constant 'StateProvince' to your tables name.

declare @T table
(
  StateProvinceID int,
  StateProvinceCode char(2),
  CountryRegionCode char(2),
  IsOnlyStateProvinceFlag int,
  Name varchar(50),
  TerritoryID int,
  rowguid uniqueidentifier,
  ModifiedDate datetime
)

insert into @T values
(1, 'AB', 'CA', 0, 'Alberta', 6, '298C2880-AB1C-4982-A5AD-A36EB4BA0D34', '2004-03-11T10:17:21.587'),
(2, 'AB', 'CA', 0, 'Alberta', 6, '298C2880-AB1C-4982-A5AD-A36EB4BA0D34', '2004-03-11T10:17:21.587')

select 'StateProvince' as [@name],
  (
    select 
      (
        select T3.N.value('local-name(.)', 'sysname') as [@name],
               T3.N.value('.', 'nvarchar(max)') as [@value]
        from (
               select T1.*
               for xml path(''), type
             ) T2(N)
          cross apply T2.N.nodes('*') as T3(N)       
        for xml path('COL'), root('ROW'), type
      )         
    from @T as T1
    for xml path(''), type
  )
for xml path('TABLE')  

Result:

<TABLE name="StateProvince">
  <ROW>
    <COL name="StateProvinceID" value="1" />
    <COL name="StateProvinceCode" value="AB" />
    <COL name="CountryRegionCode" value="CA" />
    <COL name="IsOnlyStateProvinceFlag" value="0" />
    <COL name="Name" value="Alberta" />
    <COL name="TerritoryID" value="6" />
    <COL name="rowguid" value="298C2880-AB1C-4982-A5AD-A36EB4BA0D34" />
    <COL name="ModifiedDate" value="2004-03-11T10:17:21.587" />
  </ROW>
  <ROW>
    <COL name="StateProvinceID" value="2" />
    <COL name="StateProvinceCode" value="AB" />
    <COL name="CountryRegionCode" value="CA" />
    <COL name="IsOnlyStateProvinceFlag" value="0" />
    <COL name="Name" value="Alberta" />
    <COL name="TerritoryID" value="6" />
    <COL name="rowguid" value="298C2880-AB1C-4982-A5AD-A36EB4BA0D34" />
    <COL name="ModifiedDate" value="2004-03-11T10:17:21.587" />
  </ROW>
</TABLE>

Note: The column names in your table has to be a valid XML element name. You can for instance not have any spaces in the column name.