March 3, 2005 at 2:37 am
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
Niladri Kumar Saha
March 3, 2005 at 3:37 am
Type "sql server select from xml file" into google.
March 3, 2005 at 4:10 am
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
Niladri Kumar Saha
March 4, 2005 at 1:00 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply