locked
Import Data from 2 XML Nodes into 1 SQL Table RRS feed

  • Question

  • Hello,
    I'll try and keep this simple.
    I have an XML file, 2 nodes of which I'm concerning about. A 'Supplier' Node listing their products, and an 'Attributes' node. Simply put, there are attributes to every Product produced by a supplier.
    There's only one field that could possibly link the two together, and that's Product_ID. However, multiple suppliers will have a product_id of 1, thus making the join in T-SQL tables impossible.

    Does SSIS in BIDS (which I'm relatively new too) have a fucntion/feature/transformation, where by I can populate an Attributes table with all of it's corresponding data, while also inserting the supplier_id as an extra column.
    The supplier_id needs to come from the supplier node, while 'joining' the attributes table; so to speak.
    You would think this is somehow possible, based on the fact that the attributes node is always a node within the supplier node.

    Any help or guidance would be much appreciated.

    Cheers,
    -Ro

    Tuesday, November 22, 2011 12:12 AM

Answers

  • Hi Ro, given problem can be solved by using Merge Join transaformation with little trick as given in the link.(http://blogs.msdn.com/b/mattm/archive/2007/12/11/using-xml-source.aspx). Using the given scenerio for every parent node in XML you will have corresponding Child node with attributes,you can also double check by taking product id check.
    Bhoopendra Pratap Singh Please mark the post as answered if it answers your question.
    • Proposed as answer by Karthikeyan Anbarasan Tuesday, November 22, 2011 5:53 AM
    • Marked as answer by rhare Tuesday, November 22, 2011 5:45 PM
    Tuesday, November 22, 2011 4:39 AM
  • Hi

    As Bhoopendra suggested using Merge Join Transformation will be an option to approach... Check this article on what is Merge Join and how to use that

    http://beyondrelational.com/blogs/sherryli/archive/2011/08/22/ssis-97-when-merge-join-is-your-friend.aspx


    Thanks Karthikeyan Anbarasan http://f5debug.net/
    • Marked as answer by rhare Tuesday, November 22, 2011 5:45 PM
    Tuesday, November 22, 2011 5:58 AM

All replies

  • This will be more helpful if you put some examples of XML node and attribute you have mentioned. Although you explain it well but an example of the xml data and what you want to achieve is more helpful to provide some insight on this. The proplem is doable in ssis but need more details.
    Vikash Kumar Singh || www.singhvikash.in
    Tuesday, November 22, 2011 1:01 AM
  • Hey,
    Below is a small extract. I wasn't sure how to attach the XML file.
    You will notice the first line in the product node, it details the product as a Deluxe King Bed for a hotel with an ID of 1152984 (SupplierID).
    I want to create an attributes table in SQL that lists, LocationID, ProductID, and then all the various attributes.
    The only way I know how to do this, is by creating a Product table, and an Attribute table. But as you can see below, the only identifying column in attributes is the product ID of '8'.

    Whereas, most suppliers have at least 8 products...therefore the join would be useless.
    Let me know if this makes sense or not.
    </Product>
    				<Product LocationID="1152984" ProductID="8" Name="Deluxe King" SuperCategoryID="1" SuperCategory="Lodging" CategoryID="1" Category="Lodging" SupplierID="1152984" SupplierName="Aava Whistler Hotel" SortRank="1" DateActive="11/16/2009" DateInActive="12/31/2100" City="Whistler" Region="BC" Country="CA">
    					<Attribute ProductId="8" Code="1" CodeDescription="Bedroom" CodeType="501" CodeTypeDescription="Rooms In Unit" Value="0"/>
    					<Attribute ProductId="8" Code="1" CodeDescription="Hotel Room" CodeType="500" CodeTypeDescription="Unit Type" Value="1"/>
    					<Attribute ProductId="8" Code="1" CodeDescription="Peak Rating" CodeType="506" CodeTypeDescription="Unit Rating" Value="8"/>
    					<Attribute ProductId="8" Code="2" CodeDescription="Air conditioning" CodeType="53" CodeTypeDescription="Unit Amenities" Value="1"/>
    					<Attribute ProductId="8" Code="3" CodeDescription="Alarm clock" CodeType="53" CodeTypeDescription="Unit Amenities" Value="1"/>
    					<Attribute ProductId="8" Code="3" CodeDescription="King" CodeType="6" CodeTypeDescription="Beds in Unit" Value="1"/>
    					<Attribute ProductId="8" Code="5" CodeDescription="AM/FM radio" CodeType="53" CodeTypeDescription="Unit Amenities" Value="1"/>
    					<Attribute ProductId="8" Code="10" CodeDescription="Bathrobe" CodeType="53" CodeTypeDescription="Unit Amenities" Value="1"/>
    					<Attribute ProductId="8" Code="11" CodeDescription="Bathroom amenities" CodeType="53" CodeTypeDescription="Unit Amenities" Value="1"/>
    					
    Tuesday, November 22, 2011 1:15 AM
  • Hi Ro, given problem can be solved by using Merge Join transaformation with little trick as given in the link.(http://blogs.msdn.com/b/mattm/archive/2007/12/11/using-xml-source.aspx). Using the given scenerio for every parent node in XML you will have corresponding Child node with attributes,you can also double check by taking product id check.
    Bhoopendra Pratap Singh Please mark the post as answered if it answers your question.
    • Proposed as answer by Karthikeyan Anbarasan Tuesday, November 22, 2011 5:53 AM
    • Marked as answer by rhare Tuesday, November 22, 2011 5:45 PM
    Tuesday, November 22, 2011 4:39 AM
  • Hi

    As Bhoopendra suggested using Merge Join Transformation will be an option to approach... Check this article on what is Merge Join and how to use that

    http://beyondrelational.com/blogs/sherryli/archive/2011/08/22/ssis-97-when-merge-join-is-your-friend.aspx


    Thanks Karthikeyan Anbarasan http://f5debug.net/
    • Marked as answer by rhare Tuesday, November 22, 2011 5:45 PM
    Tuesday, November 22, 2011 5:58 AM
  • Karthikeyan and Bhoopendra,

    Both answers are correct.

    Thank you so much for the advice. I'm still in my early stages of getting this to work exactly how I want, but my initial task of joining two columns from node to all columns in another node and successfully spitting the results into one SQL table has worked.

    You guys rock!

    Tuesday, November 22, 2011 5:47 PM