SQL stored procedure to produce custom XML text I have around 30 tables from which I need to generate a specific XML format, which is consistent through the tables. 

With my current solution I have a stored procedure for each 30 tables where I do: 

select '&lt;TOP (id column)="' + id column + '"/&gt;'
+ '&lt;FIELD fieldname="(fieldname)" value="' + fieldname + '"/&gt;'
+ '&lt;FIELD fieldname="(fieldname1)" value="' + fieldname1 + '"/&gt;'
+ '&lt;FIELD fieldname="(fieldname2)" value="' + fieldname2 + '"/&gt;'
+ '&lt;FIELD fieldname="(fieldname3)" value="' + fieldname3 + '"/&gt;'
+ '&lt;FIELD fieldname="(fieldname4)" value="' + fieldname4 + '"/&gt;'
+ '&lt;FIELD fieldname="(fieldname5)" value="' + fieldname5 + '"/&gt;'
+ &lt;/TOP&gt;
from dbo.table


where the parantheses values are hardcoded for each table. So for example in one table (id column) is CUSTOMER_ID. While in a different table (id column) is STORE_ID. and (fieldname) is for example CUSTOMER_NAME. So they are all hardcoded column names in the &lt;"FIELD"> brackets and primary key in &lt;"TOP">.

So my stored procedure with all 30 tables is around 1000 lines long, as every column and primary key is hardcoded for each table. 

I'm certain there is a dynamic way to achieve this functionality where columns/rows are somehow iterated over, but I can't figure it out.
This seems to at least approach a solution for you.  You might need to add in some data type detection and conversions to make it work for you though.  It also makes an assumption that the first column for the table is the ID column...

DECLARE @TableName NVARCHAR(100) = 'dbo.MyTableName'
DECLARE @SQLCommand NVARCHAR(4000)

;WITH CTE_Initial AS
    (
    SELECT CONCAT('&lt;', CASE WHEN C.column_id = 1 THEN 'TOP (' + C.name + ')' ELSE 'FIELD fieldname="(' + C.name + ')"' END, ' value="', '''', ' + ', '[T].', QUOTENAME(C.name), ' + ', '''', '"/&gt;') AS XMLColumnName
            , * 
    FROM sys.columns AS C 
    WHERE C.object_id = OBJECT_ID(@TableName) 
    )
SELECT @SQLCommand = 'SELECT ' + STRING_AGG(CONCAT('''', XMLColumnName, ''''), '+') + ' FROM ' + @TableName + ' AS T' FROM CTE_Initial

PRINT @SQLCommand 

--EXEC sp_executesql @SQLCommand