extract xml from variable xml path

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

extract xml from variable xml path

Author

Topic

Sheetal
Starting Member

10 Posts

Posted - 2007-10-15 : 10:02:06

Hi All,

I am trying to extract xml data from xml file into MS SQL DB.

For this I use following code in SP.

SET @XDoc = ( SELECT * FROM OPENROWSET
(BULK @VCnfVal, SINGLE_CLOB ) AS xmlData)

Please note that I want @VCnfVal as one variable and not hard-coded.

Below code works fine but code 1 fails. Please let me know how to use the xml variable path instead of one fixed value of the xml path.

SET @XDoc = ( SELECT * FROM OPENROWSET
(BULK 'C:\Documents and Settings\sheetal\Desktop', SINGLE_CLOB ) AS xmlData)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-15 : 10:11:23

I believe you will have to use D-sql in this case.

declare @doc XML
declare @sql nvarchar(max)

set @sql = N'select @doc = convert(xml,mycol) from openrowset(bulk ''' + @@VCnfVal + N''', SINGLE_BLOB) as Temp(mycol)'

exec sp_executesql @sql , N'@doc XML OUTPUT', @doc OUTPUT

Select @doc

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Sheetal
Starting Member

10 Posts

Posted - 2007-10-15 : 10:12:28

Thanks Harsh,

I shall try this :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources