Home » Developer & Programmer » JDeveloper, Java & XML » Problem Query XML TABLE with attributes
Problem Query XML TABLE with attributes [message #478874] Wed, 13 October 2010 02:44 Go to next message
czinsou
Messages: 23
Registered: August 2009
Junior Member
Hi, I have some troubles to extract values from xml file that I load in XMLType like this :
INSERT INTO loadxml VALUES 
(XMLType('
    <?xml version='1.0'?>
     <InvoiceTransmission>
       ...
       <Invoice>
             .....
       </Invoice>
       ...
     <InvoiceTransmission>' 
));

It's OK for that.
The values that I'm trying to extract are stored in xml like that:
<Invoice>
  ...
  <LineItem>
  <LineItemNumber>1</LineItemNumber>
  <ChargeCode>P</ChargeCode>
  <ChargeAmount Name="Weight">340.000</ChargeAmount>
  <ChargeAmount Name="Valuation">999.000</ChargeAmount>
  <Tax>
    <TaxType>VAT</TaxType>
    <TaxAmount>16.000</TaxAmount>
  </Tax>
  <AddOnCharges>
    <AddOnChargeName>ISCAllowed</AddOnChargeName>
    <AddOnChargeAmount>-23.000</AddOnChargeAmount>
  </AddOnCharges>
  <TotalNetAmount>333.000</TotalNetAmount>
  <DetailCount>2</DetailCount>
</LineItem>
...
</Invoice>

To get all information run this SQL statement :
SELECT a.LineItemNumber,a.ChargeCode,a.WeightCharge,a.Valuation,a.VATAmount,a.ISCAllowed,a.OtherChargesAllowed,a.TotalNetAmount,a.DetailCount
FROM LOADXML,	 
XMLTABLE('/InvoiceTransmission/Invoice[InvoiceHeader/InvoiceNumber="ABX1234567"]/LineItem'
PASSING LOADXML.data COLUMNS							 
LineItemNumber NUMBER(6) PATH '//LineItemNumber',							 
ChargeCode VARCHAR2(1) PATH '//ChargeCode',							 
WeightCharge NUMBER(18,3) PATH '//ChargeAmount[Name="Weight"]/.',
Valuation NUMBER(18,3) PATH '//ChargeAmount[Name="Valuation"]/.',
VATAmount NUMBER(18,3) PATH '//Tax/TaxAmount',							 
ISCAllowed  NUMBER(18,3) PATH '//AddOnCharges[AddOnChargeName="ISCAllowed"]/AddOnChargeAmount',
OtherChargesAllowed NUMBER(18,3) PATH '//AddOnCharges[AddOnChargeName="OtherChargesAllowed"]/AddOnChargeAmount',
TotalNetAmount NUMBER(18,3) PATH '//TotalNetAmount',
DetailCount NUMBER(6) PATH '//DetailCount'
)a;

I have all the present values in the result but not values got by attributes (ChargeAmount[Name="Weight"] and ChargeAmount[Name="Valuation"]).

Can someone help me to solve it ? I have a lot a values in the same problem ... Confused

Thanks ! Smile

Re: Problem Query XML TABLE with attributes [message #478878 is a reply to message #478874] Wed, 13 October 2010 03:00 Go to previous messageGo to next message
czinsou
Messages: 23
Registered: August 2009
Junior Member
No Thanks I've just forgotten to put @ before Name ...
Like that :

ChargeAmount[@Name="Weight"]
ChargeAmount[@Name="Valuationt"]

Smile
Re: Problem Query XML TABLE with attributes [message #478880 is a reply to message #478878] Wed, 13 October 2010 03:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback, it will help people with the same problem in the future.

Regards
Michel
Re: Problem Query XML TABLE with attributes [message #478881 is a reply to message #478878] Wed, 13 October 2010 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another to write it is:
SQL> with 
  2    data as (
  3      select xmltype ('
  4  <Invoice>
  5    <LineItem>
  6    <LineItemNumber>1</LineItemNumber>
  7    <ChargeCode>P</ChargeCode>
  8    <ChargeAmount Name="Weight">340.000</ChargeAmount>
  9    <ChargeAmount Name="Valuation">999.000</ChargeAmount>
 10    <Tax>
 11      <TaxType>VAT</TaxType>
 12      <TaxAmount>16.000</TaxAmount>
 13    </Tax>
 14    <AddOnCharges>
 15      <AddOnChargeName>ISCAllowed</AddOnChargeName>
 16      <AddOnChargeAmount>-23.000</AddOnChargeAmount>
 17    </AddOnCharges>
 18    <TotalNetAmount>333.000</TotalNetAmount>
 19    <DetailCount>2</DetailCount>
 20  </LineItem>
 21  </Invoice>') val 
 22      from dual
 23    )
 24  select extractvalue(val, '//ChargeAmount[@Name="Valuation"]') ChargeAmountValuation
 25  from data
 26  /
CHARGEAMOUNTVALUATION
-----------------------------------------------------------------
999.000

Regards
Michel
Re: Problem Query XML TABLE with attributes [message #478893 is a reply to message #478881] Wed, 13 October 2010 05:28 Go to previous messageGo to next message
czinsou
Messages: 23
Registered: August 2009
Junior Member
Hi, I have another problem , I try to run that SQL statement :
SELECT a.BatchSequenceNumber,a.RecordSequenceWithinBatch,a.WeightBilled
FROM LOADXML,
XMLTABLE('/InvoiceTransmission/Invoice[InvoiceHeader/InvoiceNumber="ABX1234567"]/LineItemDetail[LineItemNumber=3 and RecordSequenceWithinBatch=4]'
PASSING LOADXML.data COLUMNS
BatchSequenceNumber NUMBER(5) PATH '//BatchSequenceNumber',
RecordSequenceWithinBatch NUMBER(6) PATH '//RecordSequenceWithinBatch',							   WeightBilled NUMBER(18,3) PATH '//ChargeAmount[@Name="WeightBilled"]'
)a;	

The xml part is :
...
<LineItemDetail>
<DetailNumber>7</DetailNumber>
  <LineItemNumber>3</LineItemNumber>
  <BatchSequenceNumber>3</BatchSequenceNumber>
  <RecordSequenceWithinBatch>3</RecordSequenceWithinBatch>
  <ChargeAmount Name="WeightBilled">400.000</ChargeAmount>
</LineItemDetail>
<LineItemDetail>
  <DetailNumber>8</DetailNumber>
  <LineItemNumber>3</LineItemNumber>
  <BatchSequenceNumber>3</BatchSequenceNumber>
  <RecordSequenceWithinBatch>4</RecordSequenceWithinBatch>
  <ChargeAmount Name="WeightBilled">550.000</ChargeAmount>
</LineItemDetail>
...

Although I have well precised the element ([LineItemNumber=3 and RecordSequenceWithinBatch=4) in my SQL statement,
I have this message :
Error: ORA-19279: XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

An idea to solve the problem ?

[Updated on: Wed, 13 October 2010 05:36]

Report message to a moderator

Re: Problem Query XML TABLE with attributes [message #478894 is a reply to message #478893] Wed, 13 October 2010 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a complete XML exemple.
Keep your lines in 80 characters width.

Regards
Michel
Re: Problem Query XML TABLE with attributes [message #478896 is a reply to message #478894] Wed, 13 October 2010 05:39 Go to previous messageGo to next message
czinsou
Messages: 23
Registered: August 2009
Junior Member
@Michel-> I've just modified the message.
Re: Problem Query XML TABLE with attributes [message #478899 is a reply to message #478896] Wed, 13 October 2010 06:11 Go to previous messageGo to next message
czinsou
Messages: 23
Registered: August 2009
Junior Member
Nobody can find out the solution ?
I've tried a lot but nothing good interesting, I'm pretty new on oracle xpath ...
Re: Problem Query XML TABLE with attributes [message #478900 is a reply to message #478899] Wed, 13 October 2010 06:29 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
Your example works for me fine after adapting (my) NLS and wellforming the XML:
WITH loadxml AS 
 (select xmltype ('
 <xml>        
   <LineItemDetail>
     <DetailNumber>7</DetailNumber>
     <LineItemNumber>3</LineItemNumber>
     <BatchSequenceNumber>3</BatchSequenceNumber>
     <RecordSequenceWithinBatch>3</RecordSequenceWithinBatch>
     <ChargeAmount Name="WeightBilled">400,000</ChargeAmount>
  </LineItemDetail>
  <LineItemDetail>
    <DetailNumber>8</DetailNumber>
    <LineItemNumber>3</LineItemNumber>
    <BatchSequenceNumber>3</BatchSequenceNumber>
    <RecordSequenceWithinBatch>4</RecordSequenceWithinBatch>
    <ChargeAmount Name="WeightBilled">550,000</ChargeAmount>
  </LineItemDetail>
</xml>') data from dual)
SELECT 
  a.BatchSequenceNumber,a.RecordSequenceWithinBatch,a.WeightBilled
 FROM loadxml,
      XMLTABLE('//LineItemDetail[LineItemNumber=3 and RecordSequenceWithinBatch=4]'
        PASSING loadxml.data COLUMNS
        BatchSequenceNumber NUMBER(5) PATH '//BatchSequenceNumber',
        RecordSequenceWithinBatch NUMBER(6) PATH '//RecordSequenceWithinBatch', 
        WeightBilled NUMBER(18,3) PATH '//ChargeAmount[@Name="WeightBilled"]')a;    

BATCHSEQUENCENUMBER RECORDSEQUENCEWITHINBATCH WEIGHTBILLED
------------------- ------------------------- ------------
                  3                         4          550
1 row selected.


Re: Problem Query XML TABLE with attributes [message #478902 is a reply to message #478900] Wed, 13 October 2010 06:59 Go to previous message
czinsou
Messages: 23
Registered: August 2009
Junior Member
OK fine too Thanks a lot !
Previous Topic: How to get XML node position
Next Topic: Problem getting parent node element in XML Table
Goto Forum:
  


Current Time: Fri Mar 29 06:32:02 CDT 2024