Include Column_Id as atttribute in FOR XML PATH

Question

Let see this sql query

SELECT * 
FROM Customer 
WHERE Id = 60 
FOR XML PATH ('Customer'), ELEMENTS XSINIL, TYPE

It returns XML like this

<Customer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <Id>60</Id>
      <BranchId>5</BranchId>
      <DocKind>3</DocKind>
      <Document>SomeDoc</Document>
....

How can I add Column_id as Xml attributes to each node?

This is the query returning Column_Id's of table Customer:

SELECT COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
    COLUMN_NAME, 'ColumnID') AS COLUMN_ID, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Customer' 

I want result like this:

<Customer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Id ColumnId=1>60</Id>
  <BranchId ColumnId=2>5</BranchId>
  <DocKind ColumnId=3>3</DocKind>
  <Document ColumnId=4>SomeDoc</Document>

Answer 1

Here is solution:

DECLARE @s VARCHAR(MAX) = 
'select ' + STUFF(( SELECT  ',' + CAST(COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + 
                            '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') AS VARCHAR(10)) + 
                            ' as ''' + COLUMN_NAME + '/@ColumnID'', [' + COLUMN_NAME + ']'
                    FROM    INFORMATION_SCHEMA.COLUMNS
                    WHERE   TABLE_NAME = 'Customer'
                    FOR XML PATH('')), 1, 1, '') + 
' from Customer FOR XML PATH(''Customer''),ELEMENTS XSINIL, TYPE'   
EXEC (@s)

Answer 2

If your in SQL Server, I think you can you a correlated subquery for each field such as:

(select top(1) COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
COLUMN_NAME, 'ColumnID') AS COLUMN_ID
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Customer' AND COLUMN_NAME = 'BranchId') as 'BranchID/@Column_ID'

This is a quick application of the information given in TSQL FOR XML PATH Attribute On , Type.

With some sample data it'd be easier to see if it works...

CREATE TABLE Customer (ID INT IDENTITY(1,1), BranchID INT) 
GO
INSERT INTO Customer SELECT ID FROM (VALUES (1),(2)) x(id)
GO

SELECT (SELECT TOP(1) COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),COLUMN_NAME, 'ColumnID') 
          FROM INFORMATION_SCHEMA.COLUMNS
         WHERE TABLE_NAME = 'Customer' 
           AND COLUMN_NAME = 'ID') AS 'ID/@Column_ID'
     ,ID
     ,(SELECT TOP(1) COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') 
         FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'Customer' AND COLUMN_NAME = 'BranchId') AS 'BranchID/@Column_ID'
     ,BranchID
 FROM Customer 
WHERE Id = 1 FOR XML PATH ('Customer'), ELEMENTS XSINIL, TYPE

And that gets me:

<Customer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ID Column_ID="1">1</ID>
  <BranchID Column_ID="2">1</BranchID>
</Customer>