Topic
  • 3 replies
  • Latest Post - ‏2011-05-27T16:22:48Z by JP_Parkin
SM11_tabassum_m
SM11_tabassum_m
5 Posts

Pinned topic Loding data from XML file

‏2011-05-21T06:37:29Z |
Hi..

I want to laod data from .xml file into table using design studio. I have created a dtabase and added connection to it from design studio . But when i try to load xml file it gives error like
Incompatible xml file..
How to solve this?
Updated on 2011-05-27T16:22:48Z at 2011-05-27T16:22:48Z by JP_Parkin
  • JP_Parkin
    JP_Parkin
    128 Posts

    Re: Loding data from XML file

    ‏2011-05-24T15:51:05Z  
    Hi,
    Could you please provide some more details about how exactly you are performing the import?
    Are you using the File Import and Bulk Load operators in a data flow or just performing an import via a SQL script?
    Could you attach the data flow to this posting ?
    What does the content of your XML file look like ( can you attach a sample XML doc to this posting )?

    Thanks,
    JP
  • SM11_tabassum_m
    SM11_tabassum_m
    5 Posts

    Re: Loding data from XML file

    ‏2011-05-25T09:07:53Z  
    • JP_Parkin
    • ‏2011-05-24T15:51:05Z
    Hi,
    Could you please provide some more details about how exactly you are performing the import?
    Are you using the File Import and Bulk Load operators in a data flow or just performing an import via a SQL script?
    Could you attach the data flow to this posting ?
    What does the content of your XML file look like ( can you attach a sample XML doc to this posting )?

    Thanks,
    JP
    I have created atable with only one xml column.

    In design studio ,from Data Source Explorer view I select the table and then select data option in that I select Load from XML and select the xml document. Here I got the message as incompatible xml file.

    The XML file format is

    <Customers>
    <Customer id="">
    <name></name>
    <addess></addess>
    </Customer>...................
  • JP_Parkin
    JP_Parkin
    128 Posts

    Re: Loding data from XML file

    ‏2011-05-27T16:22:48Z  
    I have created atable with only one xml column.

    In design studio ,from Data Source Explorer view I select the table and then select data option in that I select Load from XML and select the xml document. Here I got the message as incompatible xml file.

    The XML file format is

    <Customers>
    <Customer id="">
    <name></name>
    <addess></addess>
    </Customer>...................
    Hi,
    I have a better understanding of your situation. The Load for XML feature that you are using does not seem to support XML types - that particular interface is attempting to use an XML structure to store information about non-XML data and provide a way to insert the contents into the database.

    There's a couple of potential solutions depending on the frequency you need to perform this type of load, how many files you generally need to process and the number of different target tables you might need to process.

    1) Design Studio - create a data flow to import an XML file

    From the Design Studio you can create a data flow that contains a "XML File Reader" operator linked to a "Table Target" operator. You fill in the file information in the XML File Reader and select a table in the Table Target operator and map the XML column to the XML target. You can run this repeatable flow as often as you like.

    Suppose you have 100 such XML files that you would like to import. Rather than running 100 imports in order to process 100 files, you can instead use a variable for the filename in the XML File Reader and then place this data flow inside of a control flow that makes use of the Iterator operator which can loop over files in an input directory, assign the files to a variable and then invoke the data flow that loads the file into your table.
    2) Use the IBM Optim Database Administrator to perform an import or load

    The other option available to you is to download the IBM Optim Database Administrator (ODA). This is an eclipse tool that is available to you if you have purchased the ISW product. ODA is included as one of the bundled products that can be downloaded separately from Passport Advantage. There is also a trial available from http://www-01.ibm.com/software/data/optim/database-administrator/

    DB2 requires XML data to be stored in a separate file from the rest of the non-XML data when performing an import or load. In order to just import a single file containing XML you simply need to create a second file to let DB2 know where your XML data resides.

    For example, if your XML data is in a file named "d:\tmp\customer.xml" create a file named "d:\tmp\customer.del" that contains the following content :
    
    <XDS FIL = 
    "d:\tmp\customer.xml"/>
    


    Using the ODA tool you can navigate to the table that you wish to import your data to, but you still must provide a base delimited file that contains the name of the file you are attempting to load or import.

    To summarize, the steps are :
    • Open the Administration Explorer view from the Task Navigator -> IBM Database Administration
    • Navigate to the database you want to import the data into ( via the machine name and port number)
    • Click on the Tables folder - this will populate the main editor with all the tables - you can apply a filter to the editor in order to find the table
    • right click on the table and choose Load -> With Import Utility ( for one XML file import is better )
    • In the Import panel enter the name of the delimited import file (ie. d:\tmp\customer.del in my example above)
    • click on the Run button to perform the load.
    One final comment - do not try to shell share the ODA 2.2.3 release with any Design Studio release before 9.7.2. In my testing I found there were some problems with conflicting plugins that caused the control flow editor to not work properly. Testing ODA 2.2.3 with ISW 9.7.2 was fine and I know there has also been some testing with the ISW 9.7.3 release.

    Hopefully this helps to get you going. If you would like to see some more detailed examples of either of these steps let me know and I'll try to put together a screen recording of the Design Studio or ODA solution. I thought about doing this instead of the text posting, but I wanted to give you a more prompt reply.

    Best regards,
    JP