Thursday, April 19, 2012 11:42 PM
Lets say I have an XML of this sort:
<data> <row> <column name="FirstName">Joe</column> <column name="LastName">McMan</column> <column name="Age">25</column> </row> </data> <data> <row> <column name="FirstName">Jill</column> <column name="LastName">McDonald</column> <column name="Age">19</column> </row> </data>When I do a conditional split and separate out the columns as FirstName, LastName, and Age, I am not able to store that data in the same SQL table using a conditional flow task. Is there a better way of doing this?
Friday, April 20, 2012 12:37 AM
You could try to pivot the results. The basic principles are here:
Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008
Friday, April 20, 2012 2:27 AMIn my case, the only output options I get is name, text, and rowID. There are no specific output columns that I can get my value from. I was doing a conditional split on name== 'FirstName' and so on to get the columns but that still will give me one column at a time. I can't use them together to load data into a SQL table by the data flow task...
Regards, Pankaj Behera
Thursday, April 26, 2012 3:38 PMModerator
Hi Pankaj Behera,
Have you tried use XML source component? The XML source reads an XML data file and populates the columns in the source output with the data.For details about it, please refer to the following links:
Using XML Source: http://blogs.msdn.com/b/mattm/archive/2007/12/11/using-xml-source.aspx
XML Source - Making things easier with XSLT: http://blogs.msdn.com/b/mattm/archive/2007/12/15/xml-source-making-things-easier-with-xslt.aspx
Friday, April 27, 2012 2:09 AM
Yes I did use the XML source component first and then used that as an input for the conditional split. BTW I got it to work the very next day I posted this question.
The workaround was to create 2 tables. One acting as a raw feed table which will be truncated everytime (I am using the XML as a drop from a source system to feed into another's system back end database), and the other as the primary table.
After the XML source component, I used a conditional split to split the columns based on the attribute name. I basically split it as
name == 'FirstName' || name == 'LastName' || name == 'Age'
The raw feed table, has 3 columns, 'name', 'text', and 'rowId' from the conditional split. I feed all those three columns into the feed table using a OLEDB destination for the SQL. Here the data gets stored one after the other in the following sequence name=firstname, text=Joe, rowID=3, then next follows name=lastname, text=McMan, rowID=3 and so on.
This is when I use a join and do a insert of the data in the following way:
INSERT INTO TABLENAME (FIRSTNAME, LASTNAME, AGE) SELECT A.TEXT, B.TEXT, C.TEXT FROM RAWFEEDTABLE A INNER JOIN RAWFEEDTABLE B ON A.ROWID = B.ROWID INNER JOIN RAWFEEDTABLE C ON A.ROWID = C.ROWID WHERE A.NAME='FirstName' AND B.NAME='LastName' AND C.NAME='Age'
I hope that makes sense. And thanks a lot everyone for the answers!!
- Pankaj Behera