none
How to relate parent/child XML nodes in SSIS. RRS feed

  • Question

  • Hi,

    XML files contains one-to-many dataset with parent node containing its identifying elements and the collection of every child record. How can I convert this file into standard relational tables with referential integrity in place?

    Any help is appreciated.

    Thanks,

    IK

     

     Here is the example. File:

    <?xml version="1.0" encoding="UTF-8"?>

    <Library>

    <Author>

                                    <ID>12345</ID>

                                    <Name>Mr. Anderson</Name>

    <Books>

    <Book>

                                                                    <Title>C#</Title>

                                                                    <Year>1990</Year>

                                                    </Book>

    <Book>

                                                                    <Title>VB.NET</Title>

                                                                    <Year>2000</Year>

                                                    </Book>

                                    </Books>

    </Author>  

    <Author>

                                    <ID>99887</ID>

                                    <Name>CPT Hook</Name>

    <Books>

    <Book>

                                                                    <Title>Fishing</Title>

                                                                    <Year>1999</Year>

                                                    </Book>

                                    </Books>

    </Author>  

    </Library>  

     

    I’d like to see these records in two tables: Author and Book

     

    Author table

    Identity ID          LibraryID              Name

    1                              12345                    Mr. Anderson

    2                              99887                    CPT Hook

     

    Book Table

    Identity ID          FK_Author          Title                       Year

    1                              1                              C#                           1990

    2                              1                              VB.NET                 2000

    3                              2                              Fishing                  1999

    • Edited by IPLUSI Monday, August 3, 2009 3:38 PM
    Monday, August 3, 2009 5:30 AM

Answers

  • The XML Source will have multiple outputs (one per distinct element type in your XML). In each output, there will be an additional column that contains an ID. That ID column will allow you to relate the child element to the parent element.
    John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com
    • Marked as answer by Tony Tang_YJ Wednesday, August 12, 2009 8:04 AM
    Monday, August 3, 2009 10:22 PM
    Moderator

All replies

  • I believe Conditional Split is what you are looking for.

    Else

    Import the xml to a staging (temporary) table. And from there you can move the records to relevant table(s) as required.

    Microsoft Techie
    Monday, August 3, 2009 10:15 AM
  • Check if this link is helpful
    http://blogs.msdn.com/ashvinis/archive/2005/10/21/483690.aspx
    Nitesh Rai- Please mark the post as answered if it answers your question
    Monday, August 3, 2009 10:42 AM
  • The XML Source will have multiple outputs (one per distinct element type in your XML). In each output, there will be an additional column that contains an ID. That ID column will allow you to relate the child element to the parent element.
    John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com
    • Marked as answer by Tony Tang_YJ Wednesday, August 12, 2009 8:04 AM
    Monday, August 3, 2009 10:22 PM
    Moderator