Home » Developer & Programmer » JDeveloper, Java & XML » unconventional XML- extracting data at two levels (Oracle 10.2.0.4 (On UNIX - Sun Solaris 9 with current patch set) )
unconventional XML- extracting data at two levels [message #382065] Tue, 20 January 2009 18:02 Go to next message
karaseg
Messages: 14
Registered: January 2007
Junior Member
How do I get data out of an XML file? This is the same as my last post, only with another hierarchical level to it.

We have an app that we are getting XML output from and need to read from it and place it into Oracle tables. It does not appear to be conventional formatting.

An example of one XML file that we get is:
<?xml version="1.0"?>
<typelist
  desc="Indicates the Detailed Benefit Type"
  name="DetailedBenefitType">
  <typecode
    code="010"
    desc="Fatal"
    name="Fatal"
    priority="1">
    <category
      code="regular"
      typelist="BenefitType"/>
  </typecode>
  <typecode
    code="020"
    desc="Permanent Total"
    name="Permanent Total"
    priority="2">
    <category
      code="regular"
      typelist="BenefitType"/>
  </typecode>
  <typecode
    code="050"
    desc="Temporary Total"
    name="Temporary Total"
    priority="6">
    <category
      code="regular"
      typelist="BenefitType"/>
  </typecode>
  <typecode
    code="501"
    desc="Medical Lump Sum Pmt/Settlement"
    name="Medical Lump Sum Pmt/Settlement"
    priority="17">
    <category
      code="lumpsum"
      typelist="BenefitType"/>
  </typecode>
  <typecode
    code="510"
    desc="Fatal Lump Sum Pmt/Settlement"
    name="Fatal Lump Sum Pmt/Settlement"
    priority="18">
    <category
      code="lumpsum"
      typelist="BenefitType"/>
  </typecode>
</typelist>


We would like to run SQL to extract data from this file in the following way:
type_code type_desc                       priority category_code typelist
010       Fatal                           1        regular       BenefitType
020       Permanent Total                 2        regular       BenefitType
050       Temporary Total                 6        regular       BenefitType
501       Medical Lump Sum Pmt/Settlement 17       lumpsum       BenefitType
510       Fatal Lump Sum Pmt/Settlement   18       lumpsum       BenefitType


I try:
 SELECT
   EXTRACTVALUE (COLUMN_VALUE, '/typecode/@code') AS type_code,
    EXTRACTVALUE (COLUMN_VALUE, '/typecode/@desc') AS type_desc,
    EXTRACTVALUE (COLUMN_VALUE, '/typecode/@priority') AS priority,
    EXTRACTVALUE (COLUMN_VALUE, '/category/@code') AS category_code,
   EXTRACTVALUE (COLUMN_VALUE, '/category/@typelist') AS typelist
 FROM   TABLE
          (XMLSEQUENCE
     (EXTRACT
       (XMLTYPE
         (BFILENAME ('CC_XML_DIR', 'DetailedBenefitType.xml'),
           NLS_CHARSET_ID ('WE8ISO8859P1')),
       '/typelist/typecode')))


and I only get:
type_code type_desc                       priority category_code typelist
010       Fatal                           1        
020       Permanent Total                 2        
050       Temporary Total                 6        
501       Medical Lump Sum Pmt/Settlement 17       
510       Fatal Lump Sum Pmt/Settlement   18       


When I try:

 SELECT
   EXTRACTVALUE (COLUMN_VALUE, '/typecode/@code') AS type_code,
    EXTRACTVALUE (COLUMN_VALUE, '/typecode/@desc') AS type_desc,
    EXTRACTVALUE (COLUMN_VALUE, '/typecode/@priority') AS priority,
    EXTRACTVALUE (COLUMN_VALUE, '/category/@code') AS category_code,
   EXTRACTVALUE (COLUMN_VALUE, '/category/@typelist') AS typelist
 FROM   TABLE
          (XMLSEQUENCE
     (EXTRACT
       (XMLTYPE
         (BFILENAME ('CC_XML_DIR', 'DetailedBenefitType.xml'),
           NLS_CHARSET_ID ('WE8ISO8859P1')),
       '/typelist/typecode/category')))

and I only get:
type_code type_desc                       priority category_code typelist
                                                   regular       BenefitType
                                                   regular       BenefitType
                                                   regular       BenefitType
                                                   lumpsum       BenefitType
                                                   lumpsum       BenefitType


Can someone please help me get the data from both levels at the same time?


[Updated on: Wed, 21 January 2009 00:46] by Moderator

Report message to a moderator

Re: unconventional XML- extracting data at two levels [message #382120 is a reply to message #382065] Wed, 21 January 2009 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
category_code and typelist are in /typecode/category not in /category. This is like files in directories, you have to give the full path from the root you defined.
SQL> SELECT
  2     EXTRACTVALUE (COLUMN_VALUE, '/typecode/@code') AS type_code,
  3      EXTRACTVALUE (COLUMN_VALUE, '/typecode/@desc') AS type_desc,
  4      EXTRACTVALUE (COLUMN_VALUE, '/typecode/@priority') AS priority,
  5      EXTRACTVALUE (COLUMN_VALUE, '/typecode/category/@code') AS category_code,
  6     EXTRACTVALUE (COLUMN_VALUE, '/typecode/category/@typelist') AS typelist
  7   FROM   TABLE
  8            (XMLSEQUENCE
  9       (EXTRACT
 10         (XMLTYPE
 11           (BFILENAME ('CC_XML_DIR', 'DetailedBenefitType.xml'),
 12             NLS_CHARSET_ID ('WE8ISO8859P1')),
 13         '/typelist/typecode')))
 14  /
TYPE_CO TYPE_DESC                        PRIORITY CATEGORY_CODE TYPELIST
------- -------------------------------- -------- ------------- -----------
010     Fatal                            1        regular       BenefitType
020     Permanent Total                  2        regular       BenefitType
050     Temporary Total                  6        regular       BenefitType
501     Medical Lump Sum Pmt/Settlement  17       lumpsum       BenefitType
510     Fatal Lump Sum Pmt/Settlement    18       lumpsum       BenefitType

5 rows selected.

Regards
Michel

[Updated on: Wed, 21 January 2009 00:55]

Report message to a moderator

Re: unconventional XML- extracting data at two levels [message #382265 is a reply to message #382065] Wed, 21 January 2009 10:33 Go to previous messageGo to next message
karaseg
Messages: 14
Registered: January 2007
Junior Member
Thanks so much Michel! I know these are very basic questions, but I am just clueless with XML. Hopefully it becomes more familiar to me as I work with it, but after 15 years of regular SQL on regular RDBMS, this just seems like a foreign language.
Re: unconventional XML- extracting data at two levels [message #382267 is a reply to message #382265] Wed, 21 January 2009 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For me too. And questions from others improve myself.

Regards
Michel
Re: unconventional XML- extracting data at two levels [message #382533 is a reply to message #382065] Thu, 22 January 2009 19:09 Go to previous messageGo to next message
karaseg
Messages: 14
Registered: January 2007
Junior Member
Okay, Michel, now the next level-

What if there are repeating values within a node (I think it is called a node):

<?xml version="1.0"?>
<typelist
  desc="User-defined categories for costs"
  final="false"
  name="CostCategory">
  <typecode
    code="unspecified"
    desc="Unspecified Cost Category"
    name="Unspecified Cost Category"
    priority="1">
    <category
      code="COLL"
      typelist="coveragetype"/>
    <category
      code="SPECCAUSEOFLOSS"
      typelist="coveragetype"/>
    <category
      code="claimcost"
      typelist="costtype"/>
    <category
      code="aoexpense"
      typelist="costtype"/>
   <categorylist
      typelist="CoverageType"/>
  </typecode>
  <typecode
    code="indemnity"
    desc="Indemnity"
    name="Indemnity"
    retired="true">
    <category
      code="BLDG"
      typelist="coveragetype"/>
    <category
      code="UNDSTRG"
      typelist="coveragetype"/>
    <category
      code="FARM"
      typelist="coveragetype"/>
    <category
      code="claimcost"
      typelist="costtype"/>
    <category
      code="unspecified"
      typelist="costtype"/>
  </typecode>
  <typecode
    code="disfigurement"
    desc="Disfigurement"
    name="Disfigurement">
    <category
      code="WI"
      typelist="coveragetype"/>
    <category
      code="claimcost"
      typelist="costtype"/>
  </typecode>

So I need output from an extract SQL to look like a flattened table:
type_code     type_desc                 name                      priority retired category_code   cat_typelist
unspecified   Unspecified Cost Category Unspecified Cost Category 1                COLL            coveragetype
unspecified   Unspecified Cost Category Unspecified Cost Category 1                SPECCAUSEOFLOSS coveragetype
unspecified   Unspecified Cost Category Unspecified Cost Category 1                claimcost       costtype
unspecified   Unspecified Cost Category Unspecified Cost Category 1                aoexpense       costtype
indemnity     Indemnity                 Indemnity                          TRUE    BLDG            coveragetype
indemnity     Indemnity                 Indemnity                          TRUE    UNDSTRG         coveragetype
indemnity     Indemnity                 Indemnity                          TRUE    FARM            coveragetype
indemnity     Indemnity                 Indemnity                          TRUE    claimcost       costtype
indemnity     Indemnity                 Indemnity                          TRUE    unspecified     costtype
disfigurement Disfigurement             Disfigurement                              WI              coveragetype
disfigurement Disfigurement             Disfigurement                              claimcost       costtype


When I use the basic SQL you showed me:
SELECT
  EXTRACTVALUE (COLUMN_VALUE, '/typecode/@code') AS type_code,
   EXTRACTVALUE (COLUMN_VALUE, '/typecode/@desc') AS type_desc,
   EXTRACTVALUE (COLUMN_VALUE, '/typecode/@name') AS name,
   EXTRACTVALUE (COLUMN_VALUE, '/typecode/@priority') AS priority,
   EXTRACTVALUE (COLUMN_VALUE, '/typecode/@retired') AS retired,
   EXTRACTVALUE (COLUMN_VALUE, '/typecode/category/@code') AS category_code,
  EXTRACTVALUE (COLUMN_VALUE, '/typecode/category/@typelist') AS cat_typelist
FROM   TABLE
         (XMLSEQUENCE
    (EXTRACT
      (XMLTYPE
        (BFILENAME ('CC_XML_DIR', 'CostCategory.xml'),
          NLS_CHARSET_ID ('WE8ISO8859P1')),
      '/typelist/typecode')))


I get:
      (XMLTYPE
       *
ERROR at line 12:
ORA-19025: EXTRACTVALUE returns value of only one node


Please help! Thanks!
Re: unconventional XML- extracting data at two levels [message #382566 is a reply to message #382533] Fri, 23 January 2009 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Each time you extract multiple branches below a node you have to create a new table using XMLSEQUENCE:
SQL> SELECT
  2    EXTRACTVALUE (a.COLUMN_VALUE, '/typecode/@code') AS type_code,
  3    EXTRACTVALUE (a.COLUMN_VALUE, '/typecode/@desc') AS type_desc,
  4    EXTRACTVALUE (a.COLUMN_VALUE, '/typecode/@name') AS name,
  5    EXTRACTVALUE (a.COLUMN_VALUE, '/typecode/@priority') AS priority,
  6    EXTRACTVALUE (a.COLUMN_VALUE, '/typecode/@retired') AS retired,
  7    EXTRACTVALUE (b.COLUMN_VALUE, '/category/@code') AS category_code,
  8    EXTRACTVALUE (b.COLUMN_VALUE, '/category/@typelist') AS cat_typelist
  9  FROM TABLE (XMLSEQUENCE (EXTRACT (
 10               XMLTYPE (
 11                 BFILENAME ('CC_XML_DIR', 'CostCategory.xml'), NLS_CHARSET_ID ('WE8ISO8859P1')),
 12               '/typelist/typecode'))) a,
 13       TABLE (XMLSEQUENCE (EXTRACT (a.COLUMN_VALUE, '/typecode/category'))) b
 14  /
TYPE_CODE     TYPE_DESC                 NAME                      PRIORITY RETIRED CATEGORY_CODE   CAT_TYPELIST
------------- ------------------------- ------------------------- -------- ------- --------------- ------------
unspecified   Unspecified Cost Category Unspecified Cost Category 1                COLL            coveragetype
unspecified   Unspecified Cost Category Unspecified Cost Category 1                SPECCAUSEOFLOSS coveragetype
unspecified   Unspecified Cost Category Unspecified Cost Category 1                claimcost       costtype
unspecified   Unspecified Cost Category Unspecified Cost Category 1                aoexpense       costtype
indemnity     Indemnity                 Indemnity                          true    BLDG            coveragetype
indemnity     Indemnity                 Indemnity                          true    UNDSTRG         coveragetype
indemnity     Indemnity                 Indemnity                          true    FARM            coveragetype
indemnity     Indemnity                 Indemnity                          true    claimcost       costtype
indemnity     Indemnity                 Indemnity                          true    unspecified     costtype
disfigurement Disfigurement             Disfigurement                              WI              coveragetype
disfigurement Disfigurement             Disfigurement                              claimcost       costtype

11 rows selected.

Regards
Michel

[Updated on: Fri, 23 January 2009 00:58]

Report message to a moderator

Re: unconventional XML- extracting data at two levels [message #384198 is a reply to message #382566] Mon, 02 February 2009 12:24 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Also see XMLTABLE in favor of XMLSEQUENCE.

Useful link from Kevin ==> http://www.orafaq.com/node/2016
Previous Topic: query an xmltype column/table
Next Topic: Storing XML docs in Oracle 10g
Goto Forum:
  


Current Time: Fri Mar 29 08:43:13 CDT 2024