Results 1 to 5 of 5
  1. #1
    Join Date
    May 2010
    Posts
    56

    Unanswered: Shred XML File data into relational tables using SQL Loader

    Hi,

    I want to shred xml file data into relational tables using SQL Loader. How to do that?

    As a starter, below are the XML file contents :

    <identifier><IdentifierType>DEV.VAULT</IdentifierType><IdentifierValue>356226</IdentifierValue></identifier>

    What should be the ctl file like, if we have to insert this data in a table 'A' with columns 'IdentifierType' and 'IdentifierValue'.


    Thanks,
    Sudhir

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    I don't think this is possible.

    You need to load the XML documents into a staging table with a XMLType column. Then use XQuery or XPath queries to shred the XML into relational tables.

  3. #3
    Join Date
    May 2010
    Posts
    56
    Hey Shammat,

    Thanks for the answer..
    Actually it does work like:

    load data
    infile 'C:\test.xml' "str '</identifier>'"
    truncate
    into table A
    (
    dummy filler terminated by ">",
    IdentifierType enclosed by "<IdentifierType>" and "</IdentifierType>",
    IdentifierValue enclosed by "<IdentifierValue>" and "</IdentifierValue>"
    )

    But I have a very huge and complex XML file. So how can I create such a control file for it.

    Regards,
    Sudhir

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by sudhirc212 View Post
    Hey Shammat,

    Thanks for the answer..
    Actually it does work like:

    load data
    infile 'C:\test.xml' "str '</identifier>'"
    truncate
    into table A
    (
    dummy filler terminated by ">",
    IdentifierType enclosed by "<IdentifierType>" and "</IdentifierType>",
    IdentifierValue enclosed by "<IdentifierValue>" and "</IdentifierValue>"
    )
    If you have more complex dependencies in the XML file (e.g. 1:many relationships) this will not work.

    The only stable and viable solution is to put the XML into a single XML column and use Oracle's XML features to shred it.

  5. #5
    Join Date
    May 2010
    Posts
    56
    Thanks Shammt.
    I will look more into it to understand and try some other ways too.

    I thought that if we want to use XML file directly then XMLTYPE is good but for shredding there has to be some other efficient way.

    Regards,
    Sudhir

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •