Using XML Source

XML Source - Making things easier with XSLT

SSIS Stuff — Sat, 15 Dec 2007 19:36:45 GMT

As I wrote in my previous post , complex XML documents will produce multiple outputs when you're using

re: Using XML Source

SenthilS — Tue, 05 Feb 2008 17:37:29 GMT

How it can done using vb macro? can u explain

re: Using XML Source

jamiet — Tue, 04 Mar 2008 00:32:51 GMT

Nice. I've never read this before. Shows how valuable a bit of out-of-the-box thinking can be.

-Jamie

re: Using XML Source

kalcee — Sat, 03 May 2008 03:02:09 GMT

hello there,

Just as your example had 3 outputs, one of the XML I am working on produced 5 outputs. So I tried using multiple "merge and joins". The outputs were merged fine, but when I try to give the outputs of the resultant merge and joins to the next one, en error shows up saying that

"The IsSorted property must be set to true on both sources of this transformation"

How do I set that on a merge and join transform ??

re: Using XML Source

mmasson — Tue, 06 May 2008 02:05:03 GMT

Hi Kalcee,

Looks like you might have missed a step. Take a look at the step that starts with "Right click on the XML Source, and bring up the Advanced Editor" ... you can force the IsSorted property to true on the "Input and Output Properties" page.

~Matt

re: Using XML Source

Telly — Wed, 21 May 2008 15:12:22 GMT

Hi,

Great information, I can remove most of my manual sorts! It will clear my task up a lot!

Quick question though, in your XML you have your main tag with a 'date' attribute, taking a look at your list of record sets you don't have a extract set. I've run into the same problem as the parent tag has values in which I need. Do you know how to access this data?

In my case it's for the report date, I need to know for which date the data in the XML is for, unfortunately the date is held within the main tag like in your example.

The date is not accessible according to my SSIS

Cheers

Ian

re: Using XML Source

mmasson — Sat, 24 May 2008 00:51:19 GMT

Hi Ian,

I noticed this too - it looks like our XML Source doesn't pick up attribute values from the root element.

Your best bet is to massage the data bit with XSLT before processing. See my other post about this here:

http://blogs.msdn.com/mattm/archive/2007/12/15/xml-source-making-things-easier-with-xslt.aspx

~Matt

re: Using XML Source

KeelioSoftware — Mon, 16 Jun 2008 06:19:46 GMT

As a side note to using XML as a source, there is an XML destination adapter for SSIS available at http://www.keelio.com

XML source and multiple data outputs for elements.

Gully Coder — Sat, 25 Oct 2008 18:54:01 GMT

XML source and multiple data outputs for elements.

re: Using XML Source

Arild - IMMIX — Sun, 26 Oct 2008 05:02:14 GMT

Excellent! Looked all over to find a good and simple explanation of how to get the XML files into SQL Server

re: Using XML Source

bre — Wed, 12 Nov 2008 20:21:18 GMT

Hello,

I have a single XML file and I use your technique (great post by the way..).

But my problem is that I have more that two outputs coming from my XML Source Adapter.

How could I deal with this without having to "cascade" the merge join ?

Any help is welcome.

Thanks.

re: Using XML Source

rperetz — Fri, 19 Dec 2008 06:32:19 GMT

what if I don't want to merge all the data in one table, what if I want to have one to many tables with a FK key?

I did get the keys generated from SSIS but they don't seem work give right relationship.

for example I got a table called events which hold the callid and eventid and I was hoping it would have the relationship there, but nope.

the ids don't match in both tables, in fact I can't even find the eventid that is linked to the callid...

at this point I got my data mapped to my SQL tables fine, but with out a relationship. I can't tell how many events are for one call.

I am about to re do everything using C# and xml objects to import the data. it's hard to believe that this could not be done in SSIS.

please help.

Here is the example of the xml I am working with

- <calls>

- <call id="@BR8" callkey="1" date="09062008" acd="0" starttime="2118" endtime="2181" type="1" result="1" from="" to="x82185 CUSTOMS" dnis="2623" ansby="x82185 CUSTOMS" requeues="0" intf="0" overflowed="0" transfd="0" held="0" confd="0" timetoabn="0" timetoans="6" tottalktm="57" totconftm="0" totholdtm="0">

- <events>

- <event starttime="2118" callkey="1" endtime="2118" type="1" ani="" dnis="" digits="82185" cause="0" montd="0" rqud="0" intf="0" overflowed="0">

</event>

- <event starttime="2118" callkey="1" endtime="2124" type="2" ani="" dnis="2623" digits="82185" cause="22" montd="0" rqud="0" intf="0" overflowed="0">

</event>

- <event starttime="2124" callkey="1" endtime="2181" type="3" ani="" dnis="2623" digits="" cause="0" montd="0" rqud="0" intf="0" overflowed="0">

</event>

</events>

</call>