How to retrieve data from a Physical XML file with Select statement(Again)

Question

Post reply

How to retrieve data from a Physical XML file with Select statement(Again)

Niladri Kumar Saha

SSC Veteran

Points: 234
More actions
March 3, 2005 at 2:37 am

#362097

Hi guys,
Again I have to throw this question.Again I am explaining that I don't have any table or field which contains xml data. I have a xml file in my hard disk.I want to read that file and want to retrieve records from that file with Select statement from SQL Query Analyzer.
So before giving any solution, please read my requirement carefully.
Thanks & Regards
Niladri Saha
Thanks & Regards,
Niladri Kumar Saha

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

si downes Grasshopper Points: 22 More actions · Answer 1

Type "sql server select from xml file" into google.

Niladri Kumar Saha SSC Veteran Points: 234 More actions · Answer 2

Hi guys

I have checked the coding.But it doesn't read the xml file properly.I am getting a message from #tempXML file that "The system cannot find the file specified." What to do?

I executed the following code.

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 = 'C:\emp.xml'

SET @ExecCmd = 'type ' + @FileName

SET @FileContents = ''

INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd

SELECT @y = count(*) from #tempXML

SET @x = 0

WHILE @x <> @y

BEGIN

SET @x = @x + 1

SELECT @FileContents = @FileContents + ThisLine from #tempXML WHERE PK

= @x

END

SELECT @FileContents as FileContents

DROP TABLE #tempXML

Thanks & Regards,

Niladri Kumar Saha

Calvin Lawson SSChampion Points: 11030 More actions · Answer 3

Maybe your file name is "C:\Temp.xml" instead of "C:\emp.xml"? Slow down....

That code you found is pretty crappy anyway....here's some better stuff (still limited to 8000 characters, though):

----------------------------------------------------------------------------------------

DECLARE @FileName varchar(255)

DECLARE @ExecCmd VARCHAR(255)

DECLARE @FileContents VARCHAR(8000)

if object_ID('tempdb..#tempXML') is not null DROP TABLE #tempXML

CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))

SET @FileName = 'C:\Temp.xml'

SET @ExecCmd = 'type ' + @FileName

SET @FileContents = ''

INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd

select @FileContents = ''

select @FileContents = @FileContents + ThisLine

from #TempXML

where ThisLine is not null

order by PK

SELECT @FileContents as FileContents

DROP TABLE #tempXML

-------------------------------------------------------------------------------------

Of course, the correct way to do this in a M$ environment is using ADO.NET.

Signature is NULL