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  |
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  |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #382533 is a reply to message #382065] |
Thu, 22 January 2009 19:09 |
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 |
|
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
|
|
|
|
Goto Forum:
Current Time: Tue Dec 17 23:00:57 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
