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 '<TOP (id column)="' + id column + '"/>'
+ '<FIELD fieldname="(fieldname)" value="' + fieldname + '"/>'
+ '<FIELD fieldname="(fieldname1)" value="' + fieldname1 + '"/>'
+ '<FIELD fieldname="(fieldname2)" value="' + fieldname2 + '"/>'
+ '<FIELD fieldname="(fieldname3)" value="' + fieldname3 + '"/>'
+ '<FIELD fieldname="(fieldname4)" value="' + fieldname4 + '"/>'
+ '<FIELD fieldname="(fieldname5)" value="' + fieldname5 + '"/>'
+ </TOP>
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 <"FIELD"> brackets and primary key in <"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.

  • Is there only one column for each primary key? – McNets Jan 21 at 13:51
  • Can you provide an example of the final schema ? – armitage Jan 21 at 15:31

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'

;WITH CTE_Initial AS
    SELECT CONCAT('<', CASE WHEN C.column_id = 1 THEN 'TOP (' + C.name + ')' ELSE 'FIELD fieldname="(' + C.name + ')"' END, ' value="', '''', ' + ', '[T].', QUOTENAME(C.name), ' + ', '''', '"/>') 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
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.