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

    Question

  • 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?
    Thursday, April 19, 2012 11:42 PM

Answers

  • 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

    Thursday, April 26, 2012 3:38 PM
  • 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



    Friday, April 27, 2012 2:09 AM

All replies

  • 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 12:37 AM
  • 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

    Friday, April 20, 2012 2:27 AM
  • 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

    Thursday, April 26, 2012 3:38 PM
  • 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



    Friday, April 27, 2012 2:09 AM