Load XML document content in table

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)

Load XML document content in table

Author

Topic

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-04-08 : 06:07:04

I have a table having XML column. I want to read a XML document and insert content of the XML file into this xml column.
CREATE TABLE [XMLTest](
[DataAsXML] [xml] NULL,

[CreatedDate] [datetime] NULL
)

I have tried the following query
declare @filepath varchar(100)

declare @filename varchar(100)

set @filename = 'Referred'

set @filepath = 'D:\ReportOutput\'+ @FileName +'.xml'

print @filepath

insert into XMLTest

SELECT xCol,getdate()

FROM (SELECT * FROM OPENROWSET

(BULK @filepath,SINGLE_BLOB) AS xCol) AS R(xCol)

Problem is that When I give complete file path for BULK instead of variable name i.e. BULK 'C:\Test.xml', query runs fine. But when I try to use @filepath, I get error "Incorrect syntax near '@filepath'." What am I doing incorrect? Also,If some one can suggest a better approach to load content of XML document to table?

Regards

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-04-08 : 06:21:21

Got reply for my query-:

http://www.sqlservercentral.com/Forums/Topic469199-5-1.aspx

I am still looking for better approach to load XML document.

Regards

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-08 : 07:41:44

http://weblogs.sqlteam.com/mladenp/archive/2007/06/18/60235.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources