none
How to Create a Table from XML Schema File (.xsd) in SSIS 2005

    Question

  • Hi,
    I have a .xsd schema file from an Informix table. I want to create the same table in the SQL Server 2005 using SSIS 2005. Is there any work arround that i can do this from a file?
    OR
    If could do this using any other SQL Server 2005 file Objects.

    Immidiate Help would be appriciated.
    Thanks!

    Thursday, June 05, 2008 12:25 PM

Answers

  • I just tried the following and it worked. Try:

    1. Create a new ssis projet
    2. Add a data flow task
    3. Add an XML source
    4. From the properties, select ANY xml file as source, and select your XSD file in 'xsd location'
    5. Add an OLEDB destination and link to the source.
    6. From the properties, connect to a sql server database.
    7. click on NEW where it asks you to select 'table or view' and it will give you the script of the table (based on the xsd schema)

    regards

    Jacob

     

     

    Wednesday, June 11, 2008 12:04 PM

All replies

  • I just tried the following and it worked. Try:

    1. Create a new ssis projet
    2. Add a data flow task
    3. Add an XML source
    4. From the properties, select ANY xml file as source, and select your XSD file in 'xsd location'
    5. Add an OLEDB destination and link to the source.
    6. From the properties, connect to a sql server database.
    7. click on NEW where it asks you to select 'table or view' and it will give you the script of the table (based on the xsd schema)

    regards

    Jacob

     

     

    Wednesday, June 11, 2008 12:04 PM
  • Thanks Jacob, but i want this process in such a way; like i need to pull the data from Informix and need to verify before that if the source table doesn't exist in the destination Database (SQL Server) then create the table and then move the data. If its already there then dont need to create.

    Its just like we play with the Directories and Files. I want to automate this process. Is this possible by continuing the above mentioned steps?
    Thanks!


    Wednesday, June 11, 2008 7:48 PM
  • How do you intend to transfer the data? are u going with SSIS or SQLXML/BuldInsert? based on the specific method you intend to use, you could add a conditional logic to create the table only if it does not exist.

     

    Thursday, June 12, 2008 1:54 AM
  • Whatever method that is fast. I know to do from SSIS but what you suggest about the SQLXML?
    which method is optimized from performance?
    Thursday, June 12, 2008 11:21 AM
  • I have not compared the performance. But if it is a matter of just bulk insert, then SQLXML might be better (if you know how to use it). SSIS is good if you have a few activities to be performed as part of the import. I do have SSIS packages that takes care of regular imports of data from different applications/systems. I use SQLXML when I dont have access to SSIS on a given project.

     

    I think it depends on how  comfortable you are with the specific approach. since you know SSIS, better to go with that.

    Thursday, June 12, 2008 11:42 AM
  • Thank you jacob, also please give me some examples link for SQLXML/Bulk Insert. If possible.
    Thanks!
    Thursday, June 12, 2008 11:50 AM
  • Jacob:

    But when we have multiple table structure in XSD and when i click new...i am able to create only one table by this method. Can you please tell him how to create multiple tables from the XSD all at one time?

    Thanks
    Mahesh
    Wednesday, June 18, 2008 2:55 PM
  • Hi Mahesh,

    I only tried with XSD that has definition for a single table. So, at the moment, I am not quite sure if there is a way to get this fixed. Another alternative is using SQLXML-bulkload instead of SSIS. This has options to generate the table schema automatically based on the XSD definition. But you need to make sure that the XSD is in the format required by bulkload. (Table relationship information etc)

     

    regards

    Jacob

    Wednesday, June 18, 2008 4:26 PM
  • Thanks for the reply. You are right, i think at the moment Bulkload is the only option.
    Wednesday, June 18, 2008 6:43 PM
  • Hi Jacob,

     

    In SSIS I don't seem to have XML as an option on the Add SSIS Connection Manager screen...!

     

    Do you know how I might rectify this unfortunate situation?

     

    Regards

     

    Walter de Jong

     

     

    Tuesday, August 26, 2008 6:16 AM
  • Walter,

    Add a Data Flow task to the SSIS package and double click on the data flow task to open the data flow designer. Add an XML data source there. Link the XML data source to your XML file. Add a destination component from the list of available components (based on your destination database).

     

    hope this helps

    regards

    Jacob

     

    Tuesday, August 26, 2008 6:28 AM
  • SSIS 2008:

    I know I have installed it, but had to spend 30 minutes to figure out how to get into it.

    When they change something so draaaaaaaaaaaaaaaaaastically, they should make some effort to minimize the initial hatret ....ie. keeping the previous "How Do I Start This Darn Thing" path somewhat similar to the previous version.

     

    Don't understand why Microsoft had to do GUI so "UNINTUITIVE" and COMPLICATED and FRUSTSTRATING to use.

     

    I am trying to use the below 2005 instructions with the 2008 version.  About to kill myself.  I can write serious programs with my eyes closed, but cannot figure out a simple task with new tools.

     

    Came to step 5/6.  Added an OLEDB connection.....but the think is I want SSIS to create tables.

    XML data is valid, XSD Schema data is valid (does not have annotation section)

     

    Tried importing to Access.....it is easy, it worked....over 255 field length were cut off, though.

     

    Want to do the same thing with SSIS.........but it is just frustrating.

    This is a single XML/XSD, but goes into multiple tables and tables are not there....SSIS needs to create.

     

    Do I want to much?

     

     

    ++++++++++++++++++++

    I  just tried the following and it worked. Try:
    1. Create a new ssis projet
    2. Add a data flow task
    3. Add an XML source
    4. From the properties, select ANY xml file as source, and select your XSD file in 'xsd location'
    5. Add an OLEDB destination and link to the source.
    6. From the properties, connect to a sql server database.
    7. click on NEW where it asks you to select 'table or view' and it will give you the script of the table (based on the xsd schema)

    regards

    Jacob

    Friday, September 05, 2008 11:54 PM
  • hi Jacob..
      I want to crate table from xml file and which will be read from another server. But i want to create table according XML file name and column according xml file's fields. Please Give Code


    Thanks In Advance......
    Rohit Kumar
    Thursday, May 21, 2009 5:41 AM
  • hi Jacob..
      I want to crate table from xml file and which will be read from another server. But i want to create table according XML file name and column according xml file's fields. Please Give Code


    Thanks In Advance......
    Rohit Kumar

    Hi Jacob,
    I'm trying to create some table from xml schema. I tried the following list that you have wrote but I don't understand how to create the destination table based on xsd:
           7. click on NEW where it asks you to select 'table or view' and it will give you the script of the table (based on the xsd schema)

    Can you explain me with an example how I must wrote the command to create a table from xsd, please?

    Thanks in advance

    J.
    Friday, August 28, 2009 12:38 PM
  • Hi,

    You have mentioned that SQLXML-bulkload has options to generate the table schema automatically based on the XSD. Can you please elaborate on this ? Any pointers would be very helpful.

    [PS: I use SQL Server 2008 and I am new to SQLXML]

    Thanks,

    Aparajith

    Tuesday, November 10, 2009 7:07 AM
  • Aparajith,

     

    Did you find any articles which guide you in using SQLXML, if so please mention them.

    Thursday, April 28, 2011 7:09 AM
  • I'm not sure that the SQLXML bulk loader will be quite the magic bullet you're looking for - not without a hefty preprocessing step at least. That would be possible, but messy...

    Anyway, lots of examples in the MSDN - try http://msdn.microsoft.com/en-us/library/ms171806.aspx for starters.

    Thursday, April 28, 2011 8:50 AM
  • Mahesh,

    When using SSIS, for exporting XML data into a table that has multiple tables embedded in its XSD.. there isn't a way to create all the related tables from XSD at one go.

    I believe, you would be seeing multiple structures when you - Double-Click XML Source Component>"XML Source Editor">Columns> Output Name dropdown. If this is yes, well we are on the same page then.

    And to answer you about creating multiple/all tables in one strike - No with SSIS, but YES with the Excel.

    Open a empty Excel file, then Data > From Other Sources> From XML Data Import. Specify your XML file( during this the Excel will look for corresponding XSD as specified in the XML file in this tag "xsi:schemaLocation").

    You could write a small piece of code or build a SSIS package that could push all this data into a table.

    Please do understand that this Excel process creates ONLY ONE table for whole XML file, though its XSD has mutliple data structures.

    Let us know.. if this works.

     

    In 'thoughts'...

    Lonely Rogue.

     

     

    Thursday, October 27, 2011 12:13 PM