Solved

Loading XML into table with SSIS

Posted on 2012-03-28
3
319 Views
Last Modified: 2012-04-07
Good morning,
I have been given an XML file and XSD that I need to load into a relational table structure.   I am fairly new to working with both XML and SSIS, but I have setup an XML source and an ADO.NET destination.   When I use the XML Source Editor and look at the "Columns", I see numerous output names.   I was not expecting this since the XML records themselves are mostly non-hierarchical.   However, I noticed that there is something different in some of my XML elements and those elements are showing up as different outputs.  

Many of my XML elements look like normal:
<Description>Test</Description>

But some have an additional parameter:
<ACTIVEFLAG code="0">No</ACTIVEFLAG>

Any element that has this code="0" or similar code= reference is being setup as a separate output.    I really only want the "No" value for the ACTIVEFLAG element.  I don't need the code value (which I assume is an alternative value for the element).   Is there a way to tell SSIS to ignore the code= and hopefully reduce the number of outputs that are being created?

This is the smallest of my XML files that I need to convert and there are over 90 outputs generated by the XML Source Editor.   My intent was to use a MERGE JOIN to bring elements together if needed, but that would be unmanageable with 90 outputs.

Ideas?

Thank you!
John
0
Comment
Question by:jzlamal
  • 2
3 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 37778215
Please see my answer at link below and hope it helps you as well - you can ndo it by running a SQL stored proc:

http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Server/MS-SharePoint/Q_26951210.html?cid=1131#a35378977
0
 

Accepted Solution

by:
jzlamal earned 0 total points
ID: 37795750
I found that by altering the XSD (schema definition) and changing it from a complex type to a simple type, the "code" parameter would be ignored.
0
 

Author Closing Comment

by:jzlamal
ID: 37818632
I really didn't get a response that resolved my particular situation.   I went ahead and experiemented until I found the answer.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Pivot Rows To Columns 10 54
SQL Server - executing an agent job from a stored proc 2 19
MS SQL Delete Query 9 33
SSRS Subscription jobs disabled, yet still running 4 36
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question