November 14, 2011 at 7:25 am
Hi all,
Need some pointers on the following...
I have a fairly complex xml file that will be my data source. All I want to do is write the entire xml contents of that file into a single column in a single record of my DB destination.
Imagine a table with 2 columns (FileName,XMLContents), that's all I'm after. XMLContents is of type xml in a sqlserver 2005 DB.
I took for granted that this would be a simple case of pointing a flatfile connection at my xml source, setting it to contain a single column (I believe NTEXT is the xml equivalent for SSIS) set row delimiters to be something not used in the file and Bob's your uncle.
Hasn't worked, every new line in the xml file is being treated as a new record in the output. Has anyone done this before?
Cheers
Chris
November 14, 2011 at 7:31 am
There must be a way to do this, i've just re-read my post and it sounds ridiculously simple!
How do i get xml from an xml file into an xml field in a database!!!!!
November 14, 2011 at 8:47 am
OK Here's how I did it should anyone else need to know.
Forget dataflows...
I used a script task into which I fed the sml filepath via a variable and read the contents into a second variable:
Dim oFile As System.IO.File
Dim oRead As System.IO.StreamReader
oRead = oFile.OpenText(Dts.Variables("FileFullPath").Value.ToString)
Dts.Variables("XMLContents").Value = oRead.ReadToEnd
I then used an execute sql task with an expression to just write the 2 variables into a record via an insert statement.
By doing it this way I avoid some of the data type problems encountered when going down the flat file source in a dataflow route.
Ta
Chris
November 14, 2011 at 8:53 am
I use OpenRowset within a T-SQL script for exactly this.
EXEC sp_configure 'xp_cmdshell', 1 ;
RECONFIGURE ;
USE MyDatabase ;
SET NOCOUNT ON ;
IF OBJECT_ID(N'tempdb..#T') IS NOT NULL
DROP TABLE #T ;
CREATE TABLE #T (F VARCHAR(1000)) ;
INSERT INTO #T
(F)
EXEC xp_cmdshell 'dir MyDrive:\MyPath\*.xml /b' ;
DECLARE Cur CURSOR LOCAL FAST_FORWARD
FOR
SELECT F
FROM #T
WHERE F LIKE '%.xml' ;
DECLARE @F VARCHAR(1000),
@Cmd VARCHAR(8000) ;
OPEN Cur ;
FETCH NEXT FROM Cur INTO @F ;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @F ;
BEGIN
SET @Cmd = 'INSERT INTO MyDatabase.dbo.MyTable (SourceFile, XMLCol) SELECT '''
+ @F
+ ''', BulkColumn FROM OPENROWSET(BULK ''MyDrive:\MyPath\'
+ @F + ''', SINGLE_CLOB) AS RawFile;' ;
EXEC (@Cmd) ;
SET @Cmd = 'MOVE MyDrive:\MyPath\' + @F + ' MyDrive:\MyPath\imported\' ;
EXEC xp_cmdshell @Cmd, no_output ;
END ;
FETCH NEXT FROM Cur INTO @F ;
END ;
CLOSE Cur ;
DEALLOCATE Cur ;
EXEC sp_configure 'xp_cmdshell', 0 ;
RECONFIGURE ;
WHILE @@TRANCOUNT > 0
COMMIT ;
The target table has an ID column, a column for the name of the file, an XML column for the contents of the file, and a datetime column that defaults to GetDate() to track when the file was imported.
Works beautifully.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply