Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Import XML Data into SQL Server database using SSIS package????? Expand / Collapse
Author
Message
Posted Tuesday, July 7, 2009 4:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 11:03 PM
Points: 143, Visits: 550
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
Post #748410
Posted Tuesday, July 7, 2009 9:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 16, 2011 7:33 AM
Points: 13, Visits: 56
Hello Divya,

I haven't work SSIS in a while. We have since moved on using BizTalk.
Post #748673
Posted Tuesday, July 7, 2009 11:27 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 11:03 PM
Points: 143, Visits: 550
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
Post #749039
Posted Tuesday, August 18, 2009 4:09 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 21, 2013 11:43 PM
Points: 945, Visits: 1,234
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
Post #773117
Posted Monday, December 14, 2009 7:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 11, 2010 5:08 AM
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....
Post #833836
Posted Thursday, April 8, 2010 10:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 25, 2014 1:29 PM
Points: 5, Visits: 372
Is possible to send me a package sample to import XML files to table?
Post #899784
Posted Thursday, June 17, 2010 4:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:23 AM
Points: 1, Visits: 36
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
Post #939300
Posted Wednesday, July 21, 2010 5:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 26, 2010 1:54 AM
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 ??
Post #956236
Posted Monday, November 21, 2011 11:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 28, 2011 1:39 PM
Points: 1, Visits: 7
Divya,
Can you send me the application sample?
thanks
fad
Post #1209536
Posted Wednesday, November 23, 2011 3:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 12, 2011 12:36 AM
Points: 23, 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.
Post #1210826
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse