Hi,
Using Oracle 11g Release 2, I have an XML file stored in the following table:
CREATE TABLE XML_FILES
(
FILENAME VARCHAR2(1000),
FILECONTENT XMLTYPE
);
The XML file example:
<includedClaimProcessingResult>
<ClaimRecordIdentifier>1000</ClaimRecordIdentifier>
<ClaimIdentifier>0x1ABC2D123456789</ClaimIdentifier>
<classifyingProcessingStatusType>
<statusCode>R</statusCode>
</classifyingProcessingStatusType>
<recordedError>
<Name>Pharmacy Claim</Name>
<Value></Value>
<ErrorCode>1.2.3</ErrorCode>
<ErrorCode>4.5.6<ErrorCode>
<ErrorMessage>Claim rejected because claimIdentifier already exist in the database</ErrorMessage>
<ErrorMessage>Inbound claim must match a claim</ErrorMessage>
<ErrorDetail>Claim Identifier already exists in the DB</ErrorDetail>
<ErrorDetail></ErrorDetail>
</recordedError>
</includedClaimProcessingResult>
All elements have a frequency of 1, with the exception of the ErrorCode (1 or more), ErrorMessage (0 or more), & the ErrorDetail (0 or more).
Here is my query:
select
r.REC_ID,
r.NAME,
r.VALUE,
ec.ERR_CODE,
ed.ERR_DTL,
em.ERR_MSG
from xml_files xf,
XMLTable('/includedClaimProcessingResult' passing xf.filecontent
columns
REC_ID NUMBER(10) path 'ClaimRecordIdentifier' ,
NAME VARCHAR2(20) path 'recordedError/Name',
ELMNT_VAL VARCHAR2(5) path 'recordedError/Value',
ierrc xmltype path 'recordedError/ErrorCode',
ierrd xmltype path 'recordedError/ErrorDetail',
ierrm xmltype path 'recordedError/ErrorMessage') r,
XMLTable('ErrorCode' passing r.ierrc
columns
ERR_CODE VARCHAR2(15) path '.') ec,
XMLTable('ErrorDetail' passing r.ierrd
columns
ERR_DTL VARCHAR2(100) path '.')(+) ed,
XMLTable('ErrorMessage' passing r.ierrm
columns
ERR_MSG VARCHAR2(100) path '.')(+) em ;
I am attempting to return the element values where the first occurrence of multiple elements is in one row, the second occurrence is in another row, etc. (The number of possible occurrences is not static):
REC_ID NAME VALUE ERR_CODE ERR_DTL ERR_MSG
---------------------- -------------------- --------- --------------- ----------------------------------- -----------------------------
1000 Pharmacy Claim 1.2.3 Claim Identifier already exists in the DB Claim rejected because claimIdentifier already exist in the database
1000 Pharmacy Claim 4.5.6 Inbound claim must match a claim
Thank you for any assistance!