none
XML Integration SQL 2008 RRS feed

  • Question

  • I have xml files as my source and want to integrate into SQL DB destination staging tables.The problem I have is that for example my one file Itemmaster file, has mulitple entities/outputs. Therefore containts the required data being scattered allover different outputs/entities and also data that I dont need, for example Item name in one output/entity and Item Leadtime in another, but I need this to integrate into one table in SQl DB.Is there a function in SSIS were I can link one source file with multiple outputs/entities to one destination table?(merge,map or link these outputs...?)

    Many Thanks
    Tuesday, May 10, 2011 12:37 PM

Answers

  • Hi Arthur,

    Sorry, but I am a novice in the integration of xml, do I have to write the XSLT transformation or is the some sort of a wizzard or way to identify the required fields and fields not needed and then generate the XSLT transformation file?How do I then merge/join this different outputs into one tbale is one package?Or would you recommend going into different sql tables and then manipulating/coding the data into one table?

    My biggest query is how to be able to use for example two seperate outputs, output 1 "Itemmasterid,Itemdesription" and output 2  "Itemleadtime" and integrate them into one SQL table Itemmaster.How do I go about? Do I create 2 different packages?Or can I do in one and how do I JOIN this information?Or should I import into different sql tables and then create a sql task to join/merge?

    Yes, I need to automate this integration of daily xml files into SQL DB tables.Please see example of source file/xml file

    -<ApplicationArea>-<Sender><LogicalID>BaanItemmaster</LogicalID><ComponentID/><TaskID/><ReferenceID>5051151009553</ReferenceID></Sender><CreationDateTime>2011-04-13T13:20:06Z</CreationDateTime><BODID>BODID-12345</BODID></ApplicationArea>-<DataArea>-<Show><ResponseCriteria/></Show>-<ItemMaster>-<ItemMasterHeader><DocumentDateTime>2011-04-13T13:20:06Z</DocumentDateTime></ItemMasterHeader>-<ItemLocation>-<ItemID><ID>100123</ID></ItemID><UPCID>999999999999999</UPCID><Description>Test 1</Description></ItemLocation>-<ItemLocation>-<ItemID><ID>100124</ID></ItemID><UPCID>999999999999999</UPCID><Description>Item Description is Test 1</Description></ItemLocation>

    Really hope you can assist in the SSIS process?

     

    Thanks

    Rheinhardt

    • Marked as answer by Boorkie Wednesday, May 11, 2011 8:52 AM
    Wednesday, May 11, 2011 6:02 AM

All replies

  • ... one file Itemmaster file, has mulitple entities/outputs. Therefore containts the required data being scattered allover different outputs/entities and also data that I dont need, for example Item name in one output/entity and Item Leadtime in another, but I need this to integrate into one table in SQl DB.Is there a function in SSIS were I can link one source file with multiple outputs/entities to one destination table?(merge,map or link these outputs...?)

    Do you have an example of that file and what you need as an end result.

    On the surface, what it seems you need is to flatten the XML file and in such case please take a look at XSLT Transformation:

    XML Source - Making things easier with XSLT at http://blogs.msdn.com/b/mattm/archive/2007/12/15/xml-source-making-things-easier-with-xslt.aspx


    Arthur My Blog
    By: TwitterButtons.com
    Tuesday, May 10, 2011 1:44 PM
    Moderator
  • 1) I think XML Task can help you out here.

    The XML task is used to work with XML data. Using this task, a package can retrieve XML documents, apply operations to the documents by using Extensible Stylesheet Language Transformations (XSLT) style sheets and XPath expressions, merge multiple documents, or validate, compare, and save the updated documents to files and variables.

     

    Merge documents from many sources. For example, the task can download reports from multiple sources and dynamically merge them into one comprehensive XML document.

     

    Check http://msdn.microsoft.com/en-us/library/ms141055.aspx


    Happy to help! Thanks. Regards and good Wishes, Deepak.
    Tuesday, May 10, 2011 6:36 PM
  • And one more technique (even though it is not SSIS related), you can shred XML into tables (assuming you have SQL Server 2005 and up)

    Using XML data type to load XML data with bulk insert:

    http://sqlblog.com/blogs/kent_tegels/archive/2008/01/23/4659.aspx


    Arthur My Blog
    By: TwitterButtons.com
    Tuesday, May 10, 2011 7:04 PM
    Moderator
  • Hi Arthur,

    Sorry, but I am a novice in the integration of xml, do I have to write the XSLT transformation or is the some sort of a wizzard or way to identify the required fields and fields not needed and then generate the XSLT transformation file?How do I then merge/join this different outputs into one tbale is one package?Or would you recommend going into different sql tables and then manipulating/coding the data into one table?

    My biggest query is how to be able to use for example two seperate outputs, output 1 "Itemmasterid,Itemdesription" and output 2  "Itemleadtime" and integrate them into one SQL table Itemmaster.How do I go about? Do I create 2 different packages?Or can I do in one and how do I JOIN this information?Or should I import into different sql tables and then create a sql task to join/merge?

    Yes, I need to automate this integration of daily xml files into SQL DB tables.Please see example of source file/xml file

    -<ApplicationArea>-<Sender><LogicalID>BaanItemmaster</LogicalID><ComponentID/><TaskID/><ReferenceID>5051151009553</ReferenceID></Sender><CreationDateTime>2011-04-13T13:20:06Z</CreationDateTime><BODID>BODID-12345</BODID></ApplicationArea>-<DataArea>-<Show><ResponseCriteria/></Show>-<ItemMaster>-<ItemMasterHeader><DocumentDateTime>2011-04-13T13:20:06Z</DocumentDateTime></ItemMasterHeader>-<ItemLocation>-<ItemID><ID>100123</ID></ItemID><UPCID>999999999999999</UPCID><Description>Test 1</Description></ItemLocation>-<ItemLocation>-<ItemID><ID>100124</ID></ItemID><UPCID>999999999999999</UPCID><Description>Item Description is Test 1</Description></ItemLocation>

    Really hope you can assist in the SSIS process?

     

    Thanks

    Rheinhardt

    • Marked as answer by Boorkie Wednesday, May 11, 2011 8:52 AM
    Wednesday, May 11, 2011 6:02 AM