Solved

Loading XML into table with SSIS

Posted on 2012-03-28
3
324 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 40

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

626 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