How to select output of XML Path query in SQL?

Question

Doc table contains a lot of columns (even not used ones):

Doc_DdfID Doc_RentDate Doc_ReturnDate etc.
--------- ------------ -------------- ----
1         2012-07-28   2012-07-28

But I want to query just the used ones within Doc's table.

DocDefinitionFields list columsn that are in use by document:

SELECT Dfl_ColName 
FROM DocDefinitionFields 
WHERE Dfl_DdfID = 1

DocDefinitionFields:

Dfl_ColName
-----------
Doc_RentDate
Doc_ReturnDate
...........

So I want to select all columns (listed by second query) from Doc table.

Example (if 2 columns are added to document definition form I want to select just them):

Doc:

Doc_RentDate Doc_ReturnDate
------------ --------------
2012-07-28   2012-07-28

Tried to do that by subquerying select with concatenation of fields using XML PATH:

SELECT 
   (SELECT 
       Dfl_ColName + ', ' 
       FROM DocDefinitionFields 
       FOR XML PATH('') 
   ) 
FROM Doc

It's not that simple tho. What do you suggest?

Can you post an example of the result you want?. Also, the version of SQL Server that you are using. — Lamak
– Lamak, Commented Apr 8, 2013 at 21:04

Nenad Zivkovic · Accepted Answer · 2013-04-08 21:18:20Z

2

What you need here is dynamic SQL, something like this:

DECLARE @sql VARCHAR(MAX)

SET @sql = 'SELECT ' + STUFF((SELECT ', ' + Dfl_ColName FROM DocDefinitionFields FOR XML PATH('') ),1,1,'') + ' FROM Doc'

EXEC (@sql)

Also, in order to eliminate additional comma(,) at the end of columns I have added STUFF function along with FOR XML PATH.

answered Apr 8, 2013 at 21:18

Nenad Zivkovic

18.6k6 gold badges45 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MoonKnight · Accepted Answer · 2013-04-08 21:21:15Z

To get the column names for a query dynamically and use these in a query you will need to use Dynamic SQL. Below is an example of how to create the string of available columns

DECLARE @Columns VARCHAR(MAX);
SELECT @Columns =  
    COALESCE(@Columns + ', 
     [' + CAST(COLUMN_NAME AS VARCHAR) + ']',
    '[' + CAST(COLUMN_NAME AS VARCHAR) + ']') 
FROM (SELECT DISTINCT COLUMN_NAME  
      FROM [SomeDatabase].INFORMATION_SCHEMA.COLUMNS 
      WHERE TABLE_NAME = N'SomeTableName') AS H
ORDER BY COLUMN_NAME;
GO

You can now use the string of available columns in a Dynamic SQL query. Below we have adopted the above in an INSERT query that build the required fields dynamically. The reason why we need to do it in the below is the inclusion of the set field SomeFieldA along with the others.

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 
N'INSERT INTO [' + @DbName + N']..[SomeOtherTable] ([SomeFieldA], ' + @Columns + N')  
  SELECT SomeFieldA, ' + @Columns + N' 
  FROM [SomeTableName];';
EXEC sp_executesql @SQL; 
GO

You should be able to amend the above to provide what you need.

I hope this helps.

Collectives™ on Stack Overflow

How to select output of XML Path query in SQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related