Geek asks and answers

SQL Server Transfer Variable Instead of String Name of Root Element

In SQL Server 2008, I have a query

DECLARE @root AS varchar(20);
SET @root = 'Root';

DECLARE @element AS varchar(20);
SET @element = 'Element';

SELECT
    *
FROM 
    SomeTable
FOR XML PATH (@element), ROOT(@root);

I want to pass variables instead of hard coding the repeating element name and root.

When I execute the request, it throws this error:

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '@element'.

Is it possible?

thank

+5
source share
2 answers

Try dynamic SQL -

DECLARE 
  @Root VARCHAR(20)
, @Element VARCHAR(20)
, @SQL NVARCHAR(500)

SELECT 
  @Root = 'Root'
, @Element = 'Element'
, @SQL = N'
 SELECT *
 FROM Labour.Absence a
 FOR XML PATH (''' + @Element + '''), ROOT(''' + @Root + ''')' 

PRINT @SQL

EXEC sp_executesql @SQL
+3
source

Learn to read the documentation for SQL Server. If you look at the FORsentence , you will see:

| PATH [ ( 'ElementName' ) ] 
...
[ , ROOT [ ( 'RootName' ) ] ]

If you compare this with other documentation, here I will use as an example BACKUP:

 { 'physical_device_name' | @physical_device_name_var }
...
FILE = { logical_file_name | @logical_file_name_var } 
| FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }

, , , '' . , ( @). , , ''

, @Devart, - SQL.

+3

More articles:


All Articles