Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
XML conditional split into separte columns and insert into single SQL table

Answered XML conditional split into separte columns and insert into single SQL table

  • Thursday, April 19, 2012 11:42 PM
     
      Has Code
    Sign In to Vote
    0

    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?
     

All Replies

  • Friday, April 20, 2012 12:37 AM
     
     
    Sign In to Vote
    0

    You could try to pivot the results.  The basic principles are here:

    http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/093b71e7-a7c1-4930-af35-a4f73278a32e

    Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

     
  • Friday, April 20, 2012 2:27 AM
     
     
    Sign In to Vote
    0
    In 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 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    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

    Thanks,
    Eileen

     
  • Friday, April 27, 2012 2:09 AM
     
     Answered Has Code
    Sign In to Vote
    0

    Hello Eileen,

    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