Using quotes in a variable to construct a string in SQL

Question

I have a string pulling from XML. It is pulling a single value out of a record. the only part that changes when calling the item is the field name.

for example, the first below pulls the 'resolution' for the item, the second below pulls the 'name' of the item:

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

I would like to declare a variable and use it as one of the two ways below.

WAY 1 (Preferred)

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

WAY 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

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Oct 29 '21 at 16:59
When you say this can't be another variable, actually it can: you can use `sql:variable("@someVariable")` directly in XQuery, and you can place that inside a node predicate. If you explain better what you are trying to achieve, there is probably a better solution than the linked duplicate. You could do eg `XMLData.value('(ImportFormXml/*[sql:variable("@nodeName")])[1]','VARCHAR(50)')`, note that this does *not* require dynamic SQL — Charlieface, Oct 31 '21 at 00:53

score 0 · Answer 1 · answered Oct 29 '21 at 17:35

A minimal reproducible example is not provided.

So I am shooting of the hip.

SQL

-- 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);

Output

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

Using quotes in a variable to construct a string in SQL

1 Answers1