如何在SQL中选择XML Path查询的输出？

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

Doc表包含很多列(甚至没有使用过的列):

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.

但我想查询Doc表中使用过的那些。

DocDefinitionFields list columsn that are in use by document:

DocDefinitionFields列出文档正在使用的columsn:

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.

所以我想从Doc表中选择所有列(由第二个查询列出)。

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

示例(如果将2列添加到文档定义表单中,我只想选择它们):

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:

试图通过使用XML PATH使用字段串联来进行subquerying来做到这一点:

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

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

这并不是那么简单。你有什么建议?

2 个解决方案

#1

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

你需要的是动态SQL,如下所示:

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.

另外,为了消除列末尾的额外逗号(,),我添加了STUFF函数和FOR XML PATH。

#2

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

要动态获取查询的列名并在查询中使用这些名称,您需要使用动态SQL。下面是如何创建可用列字符串的示例

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.

您现在可以在动态SQL查询中使用可用列的字符串。下面我们在INSERT查询中采用了上述方法,动态地构建了必需的字段。我们之所以需要在下面这样做的原因是包含了设置字段SomeFieldA以及其他字段。

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.

我希望这有帮助。

#1