Use db name in script and set to xml variable? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Use db name in script and set to xml variable?

Author

Topic

dpnadam
Starting Member

29 Posts

Posted - 2011-02-07 : 06:59:38

Hi

I have the following script. I know it won't work because of the @databasename variable in the statement. How would I go about getting it to work and setting it to the @xmlData variable?

DECLARE @xmlData XML;
DECLARE @databaseName NVARCHAR(50);

SET @databaseName = DB_NAME();

SET @xmlData = (SELECT *
FROM Customer AS @databaseName
FOR XML AUTO, ROOT('root'))

Thanks

ZZartin
Starting Member

30 Posts

Posted - 2011-02-07 : 13:10:14

Maybe you could try something like

SET @xmlData = EXEC('SELECT *
FROM Customer AS ' + @databaseName + ' FOR XML AUTO, ROOT(' + char(39) + 'root' + char(39) + ')')

dpnadam
Starting Member

29 Posts

Posted - 2011-02-09 : 04:23:16

quote:
Originally posted by ZZartin

Maybe you could try something like

SET @xmlData = EXEC('SELECT *
FROM Customer AS ' + @databaseName + ' FOR XML AUTO, ROOT(' + char(39) + 'root' + char(39) + ')')

Thanks for your replay ZZartin.

Unfortunately thats one of the things I tried and it throws the error: "Incorrect syntax near the keyword 'EXEC'."

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-09 : 04:50:51

try this:


DECLARE @sql NVARCHAR(MAX)
DECLARE @xmlData XML
DECLARE @dbName SYSNAME

SET @dbName = DB_NAME()

SET @sql = N'
SET @xmlData = (
	SELECT *
	FROM Customer AS ' + QUOTENAME(@dbName) + '
	FOR XML AUTO, ROOT (''root'')
	)'

PRINT @sql

EXEC sp_executeSql @sql, N'@xmlData XML OUT', @xmlData OUT

SELECT @xmlData

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

dpnadam
Starting Member

29 Posts

Posted - 2011-02-09 : 05:06:31

Thanks Charlie, it compiles :)!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources