Solved

Assigning value of EXEC result to variable

Posted on 2008-10-16
2
1,058 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
2 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

929 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now