how to xml file on network - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

how to xml file on network

Author

Topic

DURGESH
Posting Yak Master

105 Posts

Posted - 2008-09-17 : 05:32:24

hi all,
i am trying to read a xml file from my server but it is not displaying records in my temptable
code snippet

DECLARE @FileName varchar(255)
DECLARE @ExecCmd VARCHAR(255)
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR(8000)
CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))
SET @FileName = '\\myserver\Projects\eclient.xml'
SET @ExecCmd = 'type ' + @FileName
SET @FileContents = ''
INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd
select * from #tempXML

xml file sample(eclient.xml)

<?xml version="1.0" standalone="yes"?>
<DocumentElement>
<Final>
<HIPL_ID>HOFDA00002</HIPL_ID>
<HNMSID>HNMS06284</HNMSID>
<TECHSPECNO>12897</TECHSPECNO>
<ATTRIB_VALUE>ramesh</ATTRIB_VALUE>
<ATTRIB_UOM>testing tlf same2TIME</ATTRIB_UOM>
<EVSTATUSFLAG>1</EVSTATUSFLAG>
</Final>
<Final>
<HIPL_ID>HOFDA00002</HIPL_ID>
<HNMSID>HNMS06285</HNMSID>
<TECHSPECNO>12898</TECHSPECNO>
<ATTRIB_VALUE>ramesh</ATTRIB_VALUE>
<ATTRIB_UOM />
<EVSTATUSFLAG>1</EVSTATUSFLAG>
</Final>
<DocumentElement>

can anybody help me to solve this issue

thanks in advance

Regards
Durgesh J

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 06:03:31

use bulkinsert

http://msdn.microsoft.com/en-us/library/ms188365.aspx

DURGESH
Posting Yak Master

105 Posts

Posted - 2008-09-17 : 06:43:20

hi visakh16,
i am using sql server2000
i have to read from a xmlfile and insert into a table
can u help me to solve this issue

thanks in advance
Regards
DUrgesh j

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 06:44:45

quote:
Originally posted by DURGESH

hi visakh16,
i am using sql server2000
i have to read from a xmlfile and insert into a table
can u help me to solve this issue

thanks in advance
Regards
DUrgesh j

have you tried using BULKINSERT command whose link i posted?

DURGESH
Posting Yak Master

105 Posts

Posted - 2008-09-17 : 07:00:09

hi visakh16,
i tried the bulk insert but it is giving the following error
Code Snippet
------------
CREATE TABLE #tempXML(HIPL_ID VARCHAR(50),HNMSID VARCHAR(50),TECHSPECNO INT,ATTRIB_VALUE VARCHAR(50),ATTRIB_UOM VARCHAR(50),EVSTATUSFLAG BIT)
INSERT #tempXML
SELECT CONVERT(xml, BulkColumn, 2) FROM
OPENROWSET(Bulk 'c:\evignclient.xml', SINGLE_BLOB) [rowsetresults]
drop table #tempXML
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'Bulk'.

Regards
Durgesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 07:02:17

quote:
Originally posted by DURGESH

hi visakh16,
i tried the bulk insert but it is giving the following error
Code Snippet
------------
CREATE TABLE #tempXML(HIPL_ID VARCHAR(50),HNMSID VARCHAR(50),TECHSPECNO INT,ATTRIB_VALUE VARCHAR(50),ATTRIB_UOM VARCHAR(50),EVSTATUSFLAG BIT)
INSERT #tempXML
SELECT CONVERT(xml, BulkColumn, 2) FROM
OPENROWSET(Bulk 'c:\evignclient.xml', SINGLE_BLOB) [rowsetresults]
drop table #tempXML
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'Bulk'.

Regards
Durgesh

this is not waht i suggested.DId you refer link i posted? what i told was to use BULK INSERT. OPENROWSET(BULK..) is available only from sql 2005 onwards..

DURGESH
Posting Yak Master

105 Posts

Posted - 2008-09-17 : 07:08:03

hi visakh16,
i am using sql server2000
how can i read xmlfile in sql server2000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 07:12:25

quote:
Originally posted by DURGESH

hi visakh16,
i am using sql server2000
how can i read xmlfile in sql server2000

Have you looked at link i posted?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources