CODEWITHSUNDEEP
sqlsql-serverxmlt-sql
XSLT-explorer
XSLT-explorer

Reputation: 97

Convert elements into attributes within one short expression when creating XML

I found a common method to create XML from tables where elements' names changed into attributes' names. It looks like

SELECT  ElementName_1 as "@AttName_1", ElementName_2 as "@AttName_2", ElementName_3 as "@AttName_3", etc..
from table
for XML PATH

Is there a method to shorten this kind of converting entry? For example i have dozens of fields\columns and need co convert all of them except one. So, formally it would looks like

 (SELECT table.* --> all @attributes --> except 'OneFieldName'

If it possible, how it would be noted correctly?

Upvotes: 0

Views: 29

Answers (1)

gotqn
gotqn

Reputation: 43636

It will be better to specify the columns. What if someone add new columns and you are using SELECT *?

If you do not want to write the definition itself, you can build it using the sys.columns system view. It will be something like this:

SELECT CONCAT(QUOTENAME([name]), ' AS "AttName_', [column_id], '"') 
FROM 
(
    SELECT ROW_NUMBER() OVER (ORDER BY [column_id]) AS [column_id]
          ,[name]
    FROM sys.columns
    WHERE [object_id] = OBJECT_ID('dbo.table')
        AND [name] NOT IN ('OneFieldName')
) DS

and you can even use STRING_AGG to build the whole SELECT:

SELECT STRING_AGG(CAST(CONCAT(QUOTENAME([name]), ' AS "AttName_', [column_id], '"') AS NVARCHAR(MAX)), ', ') WITHIN GROUP (ORDER BY [column_id])
FROM 
(
    SELECT ROW_NUMBER() OVER (ORDER BY [column_id]) AS [column_id]
          ,[name]
    FROM sys.columns
    WHERE [object_id] = OBJECT_ID('dbo.table')
        AND [name] NOT IN ('OneFieldName')
) DS

Upvotes: 1

Related Questions