February 10, 2009 at 3:00 pm
Hi there,
I have bunch of xml files and I want to store data from them in SQL Table.
I started by iterating through those xml files using Foreach task(SSIS; file enumeration) and storing their file name. Next, I stored the contents of xml file ( it is a small file.. few kbs) in the another table.Also, I provided an identity column ,fileId, to identify different files.
Then I used Xquery to get the information from XML file into SQL table. For this, I have created a fileId variable. following is a sample code:
CREATE TABLE [dbo].[XMLFile]( -- contains FileName
[FileName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
CREATE TABLE [dbo].[XMLContent]( -- contains File contents, fileName & FileID
[FileID] [int] IDENTITY(1,1) NOT NULL,
[FileName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[XMLText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
Create Table UserInfo ( -- Target table for storing XML Data
Date varchar(20)
,Email varchar(20)
,Phone varchar(20)
, varchar(20)
)
--My Query
DECLARE @doc XML
DECLARE @FileID int
SET @FileId='2'
SET @doc= (select xmlText from dbo.XMLContent where fileid=@FileID)
Insert into dbo.UserInfo
SELECT
A.FileID,
@doc.value('/form[1]/DateCompleted[1]','varchar(20)') AS Date,
@doc.value('/form[1]/EmailAddress[1]','varchar(20)') AS Email,
@doc.value('/form[1]/Phone[1]','varchar(20)') AS Phone,
@doc.value('/form[1]/CompletedBy[1]','varchar(20)') AS [User]
FROM dbo.XMLContent AS A
WHERE FileID=@FileID
In short, by changing value of @fileId manually, I can store values from multiple xml file. But I know this is not effective way. I am trying to figure out how can I make this process smooth.
I guess by using cursor for 'fileId' , it may be possible. But I am not sure about it.
I was wondering if I can use SSIS to automate the process. For eg. I can use For each loop to iterate through each row of dbo.XMLContent table which is being done by fileID field as it is unique for each and every file. Is it possible to plug that value of FileId (from For each loop)into 'My Query' 's @fileId ???
Please let me know what you guys think about this?
Suggestions welcome..
thanks!
February 12, 2009 at 8:38 am
Just to clarify, are you saying you want to pull the fileids from a table and then use them as the parameter in your query inside the for each loop?
February 12, 2009 at 8:42 am
Yes. That's exactly what I am want to do.
But I don't know if its possible..
btw, I used cursor to do the work. But if its possible with SSIS, I would like to know that.
Thanks!
February 12, 2009 at 9:03 am
Yes it can be done.
Create a variable as object type
Create an ADO.Net connection to your database
Use an Execute SQL Task - Set the connection type to ADO.Net - Set result set to "Full Result Set" - add the variable you created in step 1 to the EST task result set - add your sql statement (i.e. select fileid from table)
Create a for each loop container and attach the Execute SQL task to it
Open the For Each Loop and click on Collection - Click the drop down box next to Enumerator and select Foreach ADO Enumerator - under Enumerator Configuration select the Object variable from step one in the drop down box - create a variable under Variable Mappings to store the individual fileids
Pass the variable containing the individual fileids as a parameter in your query (i.e. select * from table where fileid = ?) ? will be populated by the parameter
This process will loop through once for each fileid returned in the original select statement
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply