OraFAQ Forum: JDeveloper, Java & XML » Problem getting parent node element in XML Table

Home » Developer & Programmer » JDeveloper, Java & XML » Problem getting parent node element in XML Table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem getting parent node element in XML Table [message #478989]

Thu, 14 October 2010 00:37

czinsou
Messages: 23
Registered: August 2009

Junior Member

Hi, I have a to retrieve a data from xml table.
My xml is :

<InvoiceTransmission>
 <Invoice>
  ....
 <LineItemDetail>
   <DetailNumber>7</DetailNumber>
   <LineItemNumber>3</LineItemNumber>
   <BatchSequenceNumber>3</BatchSequenceNumber>
   <RecordSequenceWithinBatch>3</RecordSequenceWithinBatch>
   <ChargeAmount Name="WeightBilled">700.000</ChargeAmount>
   <RejectionMemoDetails>
     <RejectionMemoNumber>3</RejectionMemoNumber>
     <RejectionStage>3</RejectionStage>
     <ReasonCode>01</ReasonCode>
     <AirWaybillBreakdown>
         <BreakdownSerialNumber>1</BreakdownSerialNumber>
         <AWBDate>081102</AWBDate>
     </AirWaybillBreakdown>
     <AirWaybillBreakdown>
       <BreakdownSerialNumber>2</BreakdownSerialNumber>
       <AWBDate>101102</AWBDate>
     </AirWaybillBreakdown>
   </RejectionMemoDetails>
 </LineItemDetail>
 <LineItemDetail>
  ...
 </LineItemDetail>
  ...
 </Invoice>
</InvoiceTransmission>

The XQuery is :

SELECT a.BatchSequenceNumber,a.RecordSequenceWithinBatch,a.WeightBilled,
a.TotalNetAmount,a.RejectionMemoNumber,a.RejectionStage,a.ReasonCode
a.AWBDate
FROM LOADXML,
XMLTABLE('InvoiceTransmission/Invoice[InvoiceHeader/InvoiceNumber="ABX1234567"]/
LineItemDetail[LineItemNumber=3]/RejectionMemoDetails/AirWaybillBreakdown'
PASSING LOADXML.data COLUMNS
BatchSequenceNumber NUMBER(5) PATH 'BatchSequenceNumber',
RecordSequenceWithinBatch NUMBER(6) PATH 'RecordSequenceWithinBatch',
WeightBilled NUMBER(18,3) PATH 'ChargeAmount[@Name="WeightBilled"]',
TotalNetAmount NUMBER(18,3) PATH 'TotalNetAmount',
RejectionMemoNumber NUMBER(11) PATH 'RejectionMemoNumber ',
RejectionStage NUMBER(1) PATH  'RejectionStage',
ReasonCode VARCHAR2(2) PATH 'ReasonCode',
AWBDate VARCHAR2(6) PATH 'AWBDate'	
)a;

The result is that BatchSequenceNumber, RecordSequenceWithinBatch ,RejectionMemoNumber,RejectionStage,ReasonCode are returned NULL
whereas WeightBilled ,TotalNetAmount , AWBDate are well returned.

How can I do to for the NULL field to be well returned ?
Thanks for you help .

[Updated on: Thu, 14 October 2010 01:03] by Moderator

Report message to a moderator

Re: Problem getting parent node element in XML Table [message #478991 is a reply to message #478989]

Thu, 14 October 2010 01:11

_jum
Messages: 577
Registered: February 2008

Senior Member

1. there is no:

[InvoiceHeader/InvoiceNumber="ABX1234567"]

in your XML, so the query gives no rows to parse.

2. there is no tag "BatchSequenceNumber" at the level you parse (//AirWaybillBreakdown):

XMLTABLE('InvoiceTransmission/Invoice[InvoiceHeader/InvoiceNumber="ABX1234567"]/
LineItemDetail[LineItemNumber=3]/RejectionMemoDetails/AirWaybillBreakdown'

The following query gets two rows, compare them to your example and have a look at xmltable

WITH loadxml AS 
 (select xmltype ('
<InvoiceTransmission>
 <Invoice>
 <LineItemDetail>
   <DetailNumber>7</DetailNumber>
   <LineItemNumber>3</LineItemNumber>
   <BatchSequenceNumber>3</BatchSequenceNumber>
   <RecordSequenceWithinBatch>3</RecordSequenceWithinBatch>
   <ChargeAmount Name="WeightBilled">700.000</ChargeAmount>
   <RejectionMemoDetails>
     <RejectionMemoNumber>3</RejectionMemoNumber>
     <RejectionStage>3</RejectionStage>
     <ReasonCode>01</ReasonCode>
     <AirWaybillBreakdown>
         <BreakdownSerialNumber>1</BreakdownSerialNumber>
         <AWBDate>081102</AWBDate>
         <ReasonCode>NO</ReasonCode>
     </AirWaybillBreakdown>
     <AirWaybillBreakdown>
       <BreakdownSerialNumber>2</BreakdownSerialNumber>
       <AWBDate>101102</AWBDate>
       <RejectionStage>9</RejectionStage>
       <ReasonCode>YE</ReasonCode>
     </AirWaybillBreakdown>
   </RejectionMemoDetails>
 </LineItemDetail>
 <LineItemDetail>
 </LineItemDetail>
 </Invoice>
</InvoiceTransmission>') data from dual)
SELECT 
  --a.BatchSequenceNumber,
  --a.RecordSequenceWithinBatch,
  --a.WeightBilled,a.TotalNetAmount,a.RejectionMemoNumber,
  a.RejectionStage,a.ReasonCode,
  a.AWBDate
FROM LOADXML,
  XMLTABLE('//Invoice/LineItemDetail[LineItemNumber=3]/RejectionMemoDetails/AirWaybillBreakdown'
  PASSING LOADXML.data COLUMNS
  BatchSequenceNumber NUMBER(5) PATH 'BatchSequenceNumber',
  RecordSequenceWithinBatch NUMBER(6) PATH 'RecordSequenceWithinBatch',
  WeightBilled NUMBER(18,3) PATH 'ChargeAmount[@Name="WeightBilled"]',
  TotalNetAmount NUMBER(18,3) PATH 'TotalNetAmount',
  RejectionMemoNumber NUMBER(11) PATH 'RejectionMemoNumber ',
  RejectionStage NUMBER(1) PATH  'RejectionStage',
  ReasonCode VARCHAR2(2) PATH 'ReasonCode',
  AWBDate VARCHAR2(6) PATH 'AWBDate'    
)a;

REJECTIONSTAGE REASONCODE AWBDATE
-------------- ---------- -------
               NO         081102 
             9 YE         101102

[Updated on: Thu, 14 October 2010 01:29] by Moderator

Report message to a moderator

Re: Problem getting parent node element in XML Table [message #478994 is a reply to message #478991]

Thu, 14 October 2010 01:39

czinsou
Messages: 23
Registered: August 2009

Junior Member

Ok you are right, there is no tag "BatchSequenceNumber" at the level that I parse but I still can get TotalNetAmount !
It's strange because they are at same level ...

Actually I'd like to get BatchSequence (and the others at the same level) as I can get TotalNetAmount.

How can do that ?

Thanks for your help .

[Updated on: Thu, 14 October 2010 02:26]

Report message to a moderator

Re: Problem getting parent node element in XML Table [message #479034 is a reply to message #478994]

Thu, 14 October 2010 06:37

_jum
Messages: 577
Registered: February 2008

Senior Member

Post an example/testcase with "TotalNetAmount" as I did, so we can answer Your question.

Report message to a moderator

Previous Topic:	Problem Query XML TABLE with attributes
Next Topic:	got LPX-00601: Invalid token while try to read xml

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 01:11:17 CST 2025