?
Solved

SQL 2005 XML

Posted on 2014-03-14
11
Medium Priority
?
710 Views
Last Modified: 2014-03-17
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
0
Comment
Question by:dastaub
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39930689
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
 

Author Comment

by:dastaub
ID: 39930813
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39931756
Please post the value for @cmd.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:dastaub
ID: 39932129
the value of @cmd is after the equals sign.

SET @cmd = 'SELECT @x = SQLs FROM OPENROWSET (BULK ''' + @FileName + ''', SINGLE_BLOB) AS Solution(SQLs)'
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39932730
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
 

Author Comment

by:dastaub
ID: 39932997
Print @cmd  would result in:

SELECT @x = SQLs FROM OPENROWSET (BULK 'C:\Temp\ForTables.xml', SINGLE_BLOB) AS Solution(SQLs)
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 39933070
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
 

Author Comment

by:dastaub
ID: 39933471
I'm glad you hung in there with me.
0
 

Author Comment

by:dastaub
ID: 39934690
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39935733
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
 

Author Comment

by:dastaub
ID: 39935888
Yes, I assumed it was an SQL version issue.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

766 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question