JaimeJegonia
asked on
Assigning value of EXEC result to variable
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?
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/
But unfortunately @xmlDoc does not return the xml data that I am expecting.
Am I missing something?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER