SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Import XML Data into SQL Server database using SSIS package?????


Import XML Data into SQL Server database using SSIS package?????

Author
Message
Divya Agrawal
Divya Agrawal
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1058 Visits: 604
Are you generating the XSD or it is automatically generated while selecting the XML source in Data Flow.

Try generating it automatically through the XML source task in Data flow.

Then you can easily redirect to the corresponding tables one by one..

--Divya
TonyP
TonyP
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 56
Hello Divya,

I haven't work SSIS in a while. We have since moved on using BizTalk.
Divya Agrawal
Divya Agrawal
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1058 Visits: 604
There is nothing much in that. Just take the XML source in data flow and then it will ask you that whether you are providing XSD or it will generate it based on the XML file.
So, when you will select the XSD as to automatically generate it will create different XSD based on the XML file.

Then you can select different destination for each XSD.
Hope you have understood everything now.
I can send you a sample application if you want.Please send me your email id.

--Divya
AmolNaik
AmolNaik
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1677 Visits: 1234
Hello all,

I have a similar requirement of importing an XML file to SQL Server db. XML Source is the way to go for. But i have a question, some of the fields in the xml schema have been marked as "min occurs = 0" that means they may or may not occur, now if these certain fields do not appear in the XML file that i am trying to import, will my existing mapping go for a toss?
It so happened that one of the XML files that i am trying to import did not have one of the fields because of the fact that it was an optional field, as a result i am not seeing this in the node when i click on the Advanced properties.

How do i let my XML Source know about such fields?

Thanks,
Amol

Amol Naik
merbacher
merbacher
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 23
Hi guys,

What I am trying to do is this:

I want to import the entire contents of an XML file into a single column of datatype XML.

I know I can use an XML source for a data flow task, but this doesn't seem to lend itself to importing the entire file contents into one column.

Is this possible? There are reasons for me wanting to put the data into a single column, rather than breaking it out into seperate columns...

Thanks in advance....
Andy Tran
Andy Tran
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 427
Is possible to send me a package sample to import XML files to table?
lesliel
lesliel
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 60
It's easy without SSIS. Here's a demo
--make the table you need
CREATE TABLE XmlImportTesting(

xmlFileName VARCHAR(300) NOT NULL,
xml_data XML NOT NULL
)
GO

DECLARE @xmlFileName VARCHAR(300)
SELECT @xmlFileName = '\\aitl-testsql1\e$\EProfTestXML\Item3.txt'

-- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET

EXEC('INSERT INTO XmlImportTesting(xmlFileName,xml_data)
SELECT ''' + @xmlFileName + ''', xmlData
FROM (
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
GO


SELECT * FROM XmlImportTesting


DROP TABLE XmlImportTesting
troy2.net
troy2.net
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 106
@Tim

I have the same Problem ,

I tried to get the datastructure same as the XML file but how

Shall i get the data From the xml file to Table ?

Any idea ??
khanff
khanff
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 7
Divya,
Can you send me the application sample?
thanks
fad
rashmiptl22
rashmiptl22
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 309
Hi

Can u send a sample xml file and the details of the table u want to load the data .So that I can help you.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search