在SQL中使用引号在变量中构造字符串

Question

我有一个字符串从XML拉。它从记录中提取单个值。调用项时唯一改变的部分是字段名。

例如，，下面的第一个提取项目的'resolution'，下面的第二个语句提取项目的name:

XMLData.value('(ImportFormXml/Resolution)[1]','VARCHAR(50)') AS Resolution
XMLData.value('(ImportFormXml/Name)[1]','VARCHAR(50)') AS Name

我想声明一个变量，并将其作为下面两种方式之一使用。

WAY 1(首选)

DECLARE  
@Var1 Varchar(50)

SET @Var1 = 'XMLData.value('(ImportFormXml/' [BE ABLE TO INSERT NAME HERE...THIS CAN'T BE ANOTHER VARIABLE]')[1]','VARCHAR(50)')
SELECT 
@Var1  INSERT 'Resolution' AS Resolution
,  @Var2  INSERT 'Name'       AS Name
From TableX

方式2

DECLARE 
@Var1 Varchar(50)
@Var2 Varchar(50)
SET @Var1 = 'XMLData.value('(ImportFormXml/'
SET @Var2 = ')[1]','VARCHAR(50)')

SELECT 
@Var1 + 'Resolution' + @Var2   AS Resolution
, @Var1 + 'Name' + @Var2         AS Name
From TableX

Answer 1

没有提供最小可重复的示例。

所以我在拍臀部

/p>

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, XMLColumn XML);
INSERT INTO @tbl (XMLColumn) VALUES
(N'<ImportFormXml>
<Resolution>Some kind of resolution</Resolution>
<Name>Just a name</Name>
</ImportFormXml>');
-- DDL and sample data population, end
SELECT ID
, c.value('local-name(*[1])','VARCHAR(50)') + ': ' +
c.value('(Resolution/text())[1]','VARCHAR(50)') AS Col1
, c.value('local-name(*[2])','VARCHAR(50)') + ': ' +
c.value('(Name/text())[1]','VARCHAR(50)') AS Col2
FROM @tbl
CROSS APPLY XMLColumn.nodes('/ImportFormXml') AS t(c);

+----+-------------------------------------+-------------------+
| ID |                Col1                 |       Col2        |
+----+-------------------------------------+-------------------+
|  1 | Resolution: Some kind of resolution | Name: Just a name |
+----+-------------------------------------+-------------------+