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
dastaubAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Now I see your point.  I am sorry I was so dense.

You need to use sp_executesql as in:
Declare @FileName nvarchar(1000) = 'C:\Temp\ForTables.xml'


DECLARE @CMD nvarchar(max),
		@x xml
DECLARE @Parms nvarchar(100) = N'@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

-- Do your stuff

EXEC sp_xml_removedocument  @hdoc 

Open in new window

0
 
Anthony PerkinsCommented:
but when I try to change file XML file path and name to a variable, it does not work.
To quote from SQL Server's BOL:
OPENROWSET does not accept variables for its arguments.
.  So you will have to build the entire string and then execute it as dynamic SQL
0
 
dastaubAuthor Commented:
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)
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Anthony PerkinsCommented:
Please post the value for @cmd.
0
 
dastaubAuthor Commented:
the value of @cmd is after the equals sign.

SET @cmd = 'SELECT @x = SQLs FROM OPENROWSET (BULK ''' + @FileName + ''', SINGLE_BLOB) AS Solution(SQLs)'
0
 
Anthony PerkinsCommented:
Can you post the result please, as in:
SET @cmd = 'SELECT @x = SQLs FROM OPENROWSET (BULK ''' + @FileName + ''', SINGLE_BLOB) AS Solution(SQLs)'
Print @cmd
0
 
dastaubAuthor Commented:
Print @cmd  would result in:

SELECT @x = SQLs FROM OPENROWSET (BULK 'C:\Temp\ForTables.xml', SINGLE_BLOB) AS Solution(SQLs)
0
 
dastaubAuthor Commented:
I'm glad you hung in there with me.
0
 
dastaubAuthor Commented:
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/EventLog/SystemLog/SYSs', 2)
WITH (
      [InstanceID] varchar(500)
      ,ErrorTime varchar(500)
      ,ErrorSource varchar(500)
      ,ErrorMessage varchar(max)
      )

SELECT * FROM OPENXML (@hdoc, '/CMPDocument/CMPData/EventLog/ApplicationLog/LOGs', 2)
WITH (
      [InstanceID] varchar(500)
      ,ErrorTime varchar(500)
      ,ErrorSource varchar(500)
      ,ErrorMessage varchar(max)
      )

EXEC sp_xml_removedocument  @hdoc
0
 
Anthony PerkinsCommented:
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.
0
 
dastaubAuthor Commented:
Yes, I assumed it was an SQL version issue.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.