Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Assigning value of EXEC result to variable

Posted on 2008-10-16
2
Medium Priority
?
1,074 Views
Last Modified: 2010-04-21
Hi,

I want to turn the path to xml file into a variable.
So, this line
SET @xmlDoc = ( SELECT * FROM OPENROWSET ( BULK 'pathtoxml.xml', SINGLE_CLOB ) AS xmlData)
 must be
SET @xmlDoc = ( SELECT * FROM OPENROWSET ( BULK @xmlPath, SINGLE_CLOB ) AS xmlData)

I tried to create a dynamic query:
 
      Set @Sql = 'SELECT * FROM OPENROWSET ( BULK '''+@xmlPath+''', SINGLE_CLOB ) as T(xmlData)'
            exec sp_executesql @Sql, N'@res XML OUTPUT' , @xmlDoc OUTPUT

       -- this line should return the number of tags under DATA
       SELECT @xmlDoc.value('count(/XML/DATA/*)','INT')

But unfortunately @xmlDoc does not return the xml data that I am expecting.

Am I missing something?

0
Comment
Question by:JaimeJegonia
[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
2 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 total points
ID: 22728745
try
Set @Sql = 'SET @res = (SELECT * FROM OPENROWSET ( BULK '''+@xmlPath+''', SINGLE_CLOB ) as T(xmlData))'
0
 

Author Closing Comment

by:JaimeJegonia
ID: 31506629
Thanks!
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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 shrink a transaction log file down to a reasonable size.

598 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