OraFAQ Forum: JDeveloper, Java & XML » Problem Query XML TABLE with attributes

Home » Developer & Programmer » JDeveloper, Java & XML » Problem Query XML TABLE with attributes

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

Problem Query XML TABLE with attributes [message #478874]

Wed, 13 October 2010 02:44

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 !

Report message to a moderator

Re: Problem Query XML TABLE with attributes [message #478878 is a reply to message #478874]

Wed, 13 October 2010 03:00

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

Report message to a moderator

Re: Problem Query XML TABLE with attributes [message #478880 is a reply to message #478878]

Wed, 13 October 2010 03:03

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks for the feedback, it will help people with the same problem in the future.

Regards
Michel

Report message to a moderator

Re: Problem Query XML TABLE with attributes [message #478881 is a reply to message #478878]

Wed, 13 October 2010 03:05

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Problem Query XML TABLE with attributes [message #478893 is a reply to message #478881]

Wed, 13 October 2010 05:28

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

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Post a complete XML exemple.
Keep your lines in 80 characters width.

Regards
Michel

Report message to a moderator

Re: Problem Query XML TABLE with attributes [message #478896 is a reply to message #478894]

Wed, 13 October 2010 05:39

czinsou
Messages: 23
Registered: August 2009

Junior Member

@Michel-> I've just modified the message.

Report message to a moderator

Re: Problem Query XML TABLE with attributes [message #478899 is a reply to message #478896]

Wed, 13 October 2010 06:11

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 ...

Report message to a moderator

Re: Problem Query XML TABLE with attributes [message #478900 is a reply to message #478899]

Wed, 13 October 2010 06:29

_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.

Report message to a moderator

Re: Problem Query XML TABLE with attributes [message #478902 is a reply to message #478900]

Wed, 13 October 2010 06:59

czinsou
Messages: 23
Registered: August 2009

Junior Member

OK fine too Thanks a lot !

Report message to a moderator

Previous Topic:	How to get XML node position
Next Topic:	Problem getting parent node element in XML Table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 01:06:29 CST 2025