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>

score 4 · Accepted Answer · answered Mar 03 '12 at 17:57

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.

Thanks Mikael, I had seen something like that before and was hoping I was missing something easier. :-) — SumOfDavid, Mar 03 '12 at 22:28

score 1 · Answer 2 · answered Mar 03 '12 at 09:05

I don't think you can achieve what you're looking for 100% using just SQL Server's functionality - you can get close, but not 100%.

My query here uses the FOR XML PATH construct available in SQL Server 2005 and newer:

SELECT 
    [StateProvinceID] AS 'COL/@StateProvinceID',
    '',
    [StateProvinceCode] AS 'COL/@StateProvinceCode',
    '',
    [CountryRegionCode] AS 'COL/@CountryRegionCode',
    '',
    [IsOnlyStateProvinceFlag] AS 'COL/@IsOnlyStateProvinceFlag',
    '',
    [Name] AS 'COL/@Name',
    '',
    [TerritoryID] AS 'COL/@TerritoryID',
    '',
    [rowguid] AS 'COL/@rowguid',
    '',
    [ModifiedDate] AS 'COL/@ModifiedDate'
  FROM [Person].[StateProvince]
  FOR XML PATH('ROW'), ROOT('TABLE')

which results in an XML something like this:

<TABLE>
  <ROW>
    <COL StateProvinceID="1" />
    <COL StateProvinceCode="AB " />
    <COL CountryRegionCode="CA" />
    <COL IsOnlyStateProvinceFlag="0" />
    <COL Name="Alberta" />
    <COL TerritoryID="6" />
    <COL rowguid="298C2880-AB1C-4982-A5AD-A36EB4BA0D34" />
    <COL ModifiedDate="2008-03-11T10:17:21.587" />
  </ROW>
  <ROW>
    <COL StateProvinceID="2" />
    <COL StateProvinceCode="AK " />
    <COL CountryRegionCode="US" />
    <COL IsOnlyStateProvinceFlag="0" />
    <COL Name="Alaska" />
    <COL TerritoryID="1" />
    <COL rowguid="5B7B8462-A888-4E0B-A3E1-7278F8AF107E" />
    <COL ModifiedDate="2008-03-11T10:17:21.587" />
  </ROW>
  ..........
</TABLE>

You need the "empty" column selects between each attribute to avoid that all the column values are stuck into a single <COL .... /> element like this:

    <COL StateProvinceID="2"  
         StateProvinceCode="AK "  
         CountryRegionCode="US"    
         IsOnlyStateProvinceFlag="0"  
         Name="Alaska"  
         TerritoryID="1" 
         rowguid="5B7B8462-A888-4E0B-A3E1-7278F8AF107E"  
         ModifiedDate="2008-03-11T10:17:21.587" />

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

2 Answers2