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

Question

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

TonyP

SSC-Addicted

Points: 401
More actions
March 13, 2008 at 3:56 pm

#128303

Is this even possible? I looked everywhere. I can't find a tutorial to save my life. All I found on internet are a bunch of unanswered questions like mine. Can someone point me in the right direction?

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Tim Mitchell SSCoach Points: 15681 More actions · Answer 1

In the SSIS data flow task, there is an XML Source component which allows you to specify your source XML file and optional XSD schema. From there you can pipe the data into a SQL Server destination.

hth,

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

TonyP SSC-Addicted Points: 401 More actions · Answer 2

Hello,

Thanks for the reply. What I ended up doing was purchasing the book Professional SQL Server 2005 Integration Services. I found the answer in there, but I ran into a new problem that doesn't seem to be addressed by the book.

Initially after running my package, I got an error on a couple of my fields stating that an unicode string could can not be convert to a non-unicode string. I solved this issue by changing the datatypes in my database from varchar to nvarchar.

However in the near future, I am not going to have a choice to change the datatypes in the database. Is there another way of handling situations like this within the package itself?

Tim Mitchell SSCoach Points: 15681 More actions · Answer 3

You bet... what you want is the Data Conversion component. Stick it between your XML source and your SQL destination in your Data Flow, and you can convert Unicode to ASCII or vice versa.

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

aliang SSC Journeyman Points: 93 More actions · Answer 4

I am also trying to import a xml file to SQL tables using SSIS Data Flow Task --> XML source

--> OLEDB destination. I provided the xml file and a xsd file in the XML souce editor.

The extraction of the XML file will created 5 tables. My problem is that one of the tables

is empty. Any idea how to solve this problem? I have tried to add a "root" node to the

XML file, and it worked. Is there any other way to solve this problem without manually

changing the XML file?

Thanks!

TonyP SSC-Addicted Points: 401 More actions · Answer 5

Hello

Actually, I haven't gotten this far myself. I ran into issues getting my XML file to validate using a provided XML schema. While trying to get this to work using C# code, I realized that I needed multiple schema files in order for it to work.

It looks like I ran into a limitation of the XML Source. It only allows you to use one XML schema file. I need to use multiple files.

ranjitk SSC Rookie Points: 27 More actions · Answer 6

Believe I too had this problem where one schema depended upon another one. I saved a copy of the second schema in the same location and it worked.

Issue I am facing is that I need to update a table based on information from the xml-source; lookup field in the xml source is in one logical group, while the source for update is in another. God save me, or may be an mvp/guru;)

Radoslaw Jaszek Valued Member Points: 57 More actions · Answer 7

Hello,

Can anyone help me with importing xml file that contains inline schema ? Any example, please...

Many thanks.

ronnieperetz Valued Member Points: 63 More actions · Answer 8

what page in the book tell you how to get the relationship to work between the 5 tables?

I getting a hard time getting the relations of "one to many" to work in my output sql tables

ramesh.sripathi-823300 SSC Journeyman Points: 76 More actions · Answer 9

Hi

I am working on SSIS

I have an XML file that consists of nearly 5 tables of data,I have one XSD generated based on the XML file.

I need to populate the data present in XML file into corresponding five tables

Please help me out how to resolve this issue

Divya Agrawal Hall of Fame Points: 3144 More actions · Answer 10

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 SSC-Addicted Points: 401 More actions · Answer 11

Hello Divya,

I haven't work SSIS in a while. We have since moved on using BizTalk.

Divya Agrawal Hall of Fame Points: 3144 More actions · Answer 12

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 SSCarpal Tunnel Points: 4767 More actions · Answer 13

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 SSC Rookie Points: 41 More actions · Answer 14

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....