Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2007
    Posts
    5

    Unanswered: Loading XMl data into table

    hi,

    I gt a problem with loading data from xml files into oracle 10g. I am not understanding the XMLTYPE concept in the SQL Loader.

    XML file is as follows:
    - <cdr-data>
    - <cdr>
    - <networkInfo>
    <field name="requestType" value="11" />
    <field name="payer" value="296" />
    <field name="connectionId" value="0" />
    <field name="eventReferenceNumber" value="0" />
    <field name="eventSource" value="J@nus" />
    <field name="exponent" value="-2" />
    <field name="eventDateTime" value="20090904012824" />
    <field name="sessionId" value="2009090400000028540" />
    <field name="replenishmentId" value="1062" />
    <field name="calculationDate" value="20090904" />
    <field name="tariffId" value="1032" />
    <field name="status" value="1" />
    <field name="payerReplSequence" value="4" />
    <field name="errorCode" value="0" />
    </networkInfo>
    - <pricedInfo>
    <field name="bucketsUsed" value="0" />
    <field name="totalUsedUnits" value="0" />
    <field name="taxingOptionUsed" value="0" />
    <field name="totalSalePrice" value="0" />
    <field name="totalTax" value="0" />
    <field name="totalDiscountAmount" value="0" />
    <field name="totalCostPrice" value="0" />
    </pricedInfo>
    - <payerInfo>
    <field name="noOfWalletsUsed" value="0" />
    <field name="noOfPayerValidity" value="0" />
    <field name="baseWalletBalance" value="65500" />
    <field name="prevPayerStatus" value="1" />
    <field name="newPayerStatus" value="1" />
    </payerInfo>
    </cdr>
    </cdr-data>

    I want to insert every field name into a column. Can any one help please???

    thanks,

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If I am having a good day, I can successfully spell XML; let alone load data which may be in XML format.

    I do suspect that Oracle will not behave as expected or desired with columns names that are in mixedCase since it defaults to UPPERCASE names only.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by vanpersie
    I gt a problem with loading data from xml files into oracle 10g. I am not understanding the XMLTYPE concept in the SQL Loader.
    To my knowledge Oracle's XMLType only stores the full XML document.
    What makes you think SQL*Loader can parse a XML file and map tags to table columns?

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    As already mentioned, sqlldr does not DO xml. But no problem. You'll just have to write a procedure to do it.

    Code:
    SELECT EXTRACTVALUE(column_value, '//networkInfo/field[@name = "requestType"]/@value') requestType,
      EXTRACTVALUE(column_value, '//networkInfo/field[@name = "payer"]/@value') payer
    FROM XMLTABLE('/cdr-data/cdr' PASSING XMLTYPE('<cdr-data>
    <cdr>
    <networkInfo>
    <field name="requestType" value="11" />
    <field name="payer" value="296" />
    ...snip...
    </cdr>
    </cdr-data>')
    );

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You could use an XML parser, such as XMLStarlet Command Line XML Toolkit: Overview, to convert your XML into delimited data, then use SQL*Loader.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Feb 2009
    Posts
    62
    I'd either use Sqlldr to load all the data into a table with a single column of type XMLTYPE, or create an external table of that structure that pointed to the XML file on the disk.

    Then I'd use the Xml functions like ExtactValue to create a view on top of that table that displayed the data in the format I wanted.

Posting Permissions

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