Sql-server – SQL stored procedure to produce custom XML text

azure-sql-databasedynamic-sqlsql servert-sql

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.

Best Answer

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('<', 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

Related Solutions

Sql-server – way to force Deferred Name Resolution even if the table exists when creating a stored procedure

No.

I feel really guilty just typing that, but no, sadly. That's the first time I've heard of this use case, and it makes perfect sense. Best to submit a request for it on https://feedback.azure.com/forums/908035-sql-server and your grandchildren will be able to do it. ;-)

Sql-server – Stored Procedure to return dynamically created table data

Based on comments from people in chat, I decided to change my script slightly to INSERT INTO a temp table instead of creating one long SQL statement to execute at the end. So in the end my stored procedure contains the following:

create table #SurveyData
(
    tableName varchar(50),
    columnName varchar(50),
    columnId int,
    rownum int
)

create table #results
(
    SurveyId int,
    InstanceId int,
    QuestionNumber int,
    Response varchar(1000)
)

-- insert the survey table structures for use
insert into #SurveyData (tableName, columnName, columnId, rownum)
select tables1.name, cols1.name, column_id, ROW_NUMBER() over(order by tables1.name, column_id)
from sys.all_columns cols1
inner join 
(
    SELECT *
    FROM sys.all_objects
    WHERE type = 'U' 
    AND upper(name) like 'LIBRARY%' 
) Tables1
    ON cols1.object_id = tables1.object_id
WHERE cols1.name Like 'Q_%'
ORDER BY tables1.name, column_id;


declare @sql varchar(max) = '';
declare @RowCount int = 1;
declare @TotalRecords int = (SELECT COUNT(*) FROM #SurveyData);

Declare @TableName varchar(50) = '';
Declare @ColumnName varchar(50) = '';

WHILE @RowCount <= @TotalRecords
    BEGIN

        SELECT @TableName = tableName, @ColumnName = columnName
        FROM #SurveyData
        WHERE @RowCount = rownum

        SET @sql = 'INSERT INTO #results ' +
                    ' SELECT s.SurveyId
                        , s.InstanceId
                        , CASE WHEN columnName = ''' +  @ColumnName + ''' THEN REPLACE(columnName, ''Q_'', '''') ELSE '''' END as QuestionNumber
                        , Cast(s.' + @ColumnName + ' as varchar(1000)) as ''Response''
                    FROM #SurveyData t 
                    INNER JOIN ' + @TableName + ' s' +
                    ' ON REPLACE(t.tableName, ''Library_'', '''') = s.SurveyID ' +
                    ' WHERE t.columnName = ''' + @ColumnName + ''''

        exec(@sql)

        SET @RowCount = @RowCount + 1       
    END

    SELECT SurveyId, InstanceId, QuestionNumber, Response
    FROM #results

drop table #SurveyData
drop table #results

See SQL Fiddle with the final script

Related Question