dastaub
asked on
SQL 2005 XML
The below T-SQL works, but when I try to change file XML file path and name to a variable, it does not work. It gives the error of: Must declare the scalar variable "@x".
DECLARE @x xml
DECLARE @FileName varchar(100)
SELECT @x = SQLs FROM OPENROWSET (BULK 'C:\Temp\ForTables.xml', SINGLE_BLOB) AS Solution(SQLs)
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----
DECLARE @x xml
DECLARE @FileName varchar(100)
SET @FileName = 'C:\Temp\ForTables.xml'
DECLARE @CMD varchar(max)
SET @cmd = 'SELECT @x = SQLs FROM OPENROWSET (BULK ''' + @FileName + ''', SINGLE_BLOB) AS Solution(SQLs)'
EXEC (@cmd)
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
DECLARE @x xml
DECLARE @FileName varchar(100)
SELECT @x = SQLs FROM OPENROWSET (BULK 'C:\Temp\ForTables.xml', SINGLE_BLOB) AS Solution(SQLs)
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
--------------------------
DECLARE @x xml
DECLARE @FileName varchar(100)
SET @FileName = 'C:\Temp\ForTables.xml'
DECLARE @CMD varchar(max)
SET @cmd = 'SELECT @x = SQLs FROM OPENROWSET (BULK ''' + @FileName + ''', SINGLE_BLOB) AS Solution(SQLs)'
EXEC (@cmd)
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
ASKER
the below builds the entire string and executes it as a dynamic SQL, but does not work and gives the above described error message
DECLARE @CMD varchar(max)
SET @cmd = 'SELECT @x = SQLs FROM OPENROWSET (BULK ''' + @FileName + ''', SINGLE_BLOB) AS Solution(SQLs)'
EXEC (@cmd)
DECLARE @CMD varchar(max)
SET @cmd = 'SELECT @x = SQLs FROM OPENROWSET (BULK ''' + @FileName + ''', SINGLE_BLOB) AS Solution(SQLs)'
EXEC (@cmd)
Please post the value for @cmd.
ASKER
the value of @cmd is after the equals sign.
SET @cmd = 'SELECT @x = SQLs FROM OPENROWSET (BULK ''' + @FileName + ''', SINGLE_BLOB) AS Solution(SQLs)'
SET @cmd = 'SELECT @x = SQLs FROM OPENROWSET (BULK ''' + @FileName + ''', SINGLE_BLOB) AS Solution(SQLs)'
Can you post the result please, as in:
SET @cmd = 'SELECT @x = SQLs FROM OPENROWSET (BULK ''' + @FileName + ''', SINGLE_BLOB) AS Solution(SQLs)'
Print @cmd
SET @cmd = 'SELECT @x = SQLs FROM OPENROWSET (BULK ''' + @FileName + ''', SINGLE_BLOB) AS Solution(SQLs)'
Print @cmd
ASKER
Print @cmd would result in:
SELECT @x = SQLs FROM OPENROWSET (BULK 'C:\Temp\ForTables.xml', SINGLE_BLOB) AS Solution(SQLs)
SELECT @x = SQLs FROM OPENROWSET (BULK 'C:\Temp\ForTables.xml', SINGLE_BLOB) AS Solution(SQLs)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm glad you hung in there with me.
ASKER
A minor change had to occur. the DECLARE and the SET had to be in two lines.
EXAMPLE:
DECLARE @FileName nvarchar(1000) = 'C:\Temp\ForTables.xml'
had to be changed to:
DECLARE @FileName nvarchar(1000)
SET @FileName = 'C:\Temp\ForTables.xml'
The below T-SQL works.
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------
Declare @FileName nvarchar(1000)
SET @FileName = 'C:\Temp\ForTables.xml'
DECLARE @CMD nvarchar(max),
@x xml,
@Parms nvarchar(100)
SET @Parms = '@FileName nvarchar(1000), @X xml OUTPUT'
SET @cmd = 'SELECT @x = SQLs FROM OPENROWSET (BULK ''' + @FileName + ''', SINGLE_BLOB) AS Solution(SQLs)'
EXECUTE sp_executesql @CMD, @Parms, @FileName = @FileName, @x = @x OUTPUT
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
SELECT * FROM OPENXML (@hdoc, '/CMPDocument/CMPData/Even tLog/Syste mLog/SYSs' , 2)
WITH (
[InstanceID] varchar(500)
,ErrorTime varchar(500)
,ErrorSource varchar(500)
,ErrorMessage varchar(max)
)
SELECT * FROM OPENXML (@hdoc, '/CMPDocument/CMPData/Even tLog/Appli cationLog/ LOGs', 2)
WITH (
[InstanceID] varchar(500)
,ErrorTime varchar(500)
,ErrorSource varchar(500)
,ErrorMessage varchar(max)
)
EXEC sp_xml_removedocument @hdoc
EXAMPLE:
DECLARE @FileName nvarchar(1000) = 'C:\Temp\ForTables.xml'
had to be changed to:
DECLARE @FileName nvarchar(1000)
SET @FileName = 'C:\Temp\ForTables.xml'
The below T-SQL works.
--------------------------
Declare @FileName nvarchar(1000)
SET @FileName = 'C:\Temp\ForTables.xml'
DECLARE @CMD nvarchar(max),
@x xml,
@Parms nvarchar(100)
SET @Parms = '@FileName nvarchar(1000), @X xml OUTPUT'
SET @cmd = 'SELECT @x = SQLs FROM OPENROWSET (BULK ''' + @FileName + ''', SINGLE_BLOB) AS Solution(SQLs)'
EXECUTE sp_executesql @CMD, @Parms, @FileName = @FileName, @x = @x OUTPUT
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
SELECT * FROM OPENXML (@hdoc, '/CMPDocument/CMPData/Even
WITH (
[InstanceID] varchar(500)
,ErrorTime varchar(500)
,ErrorSource varchar(500)
,ErrorMessage varchar(max)
)
SELECT * FROM OPENXML (@hdoc, '/CMPDocument/CMPData/Even
WITH (
[InstanceID] varchar(500)
,ErrorTime varchar(500)
,ErrorSource varchar(500)
,ErrorMessage varchar(max)
)
EXEC sp_xml_removedocument @hdoc
A minor change had to occur. the DECLARE and the SET had to be in two lines.
You are right. My code is not supported in SQL Server 2005.
You are right. My code is not supported in SQL Server 2005.
ASKER
Yes, I assumed it was an SQL version issue.
To quote from SQL Server's BOL: . So you will have to build the entire string and then execute it as dynamic SQL