Home » Developer & Programmer » JDeveloper, Java & XML » Problem with XMLTABLE with parameters (Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi)
Problem with XMLTABLE with parameters [message #479962] Wed, 20 October 2010 08:54 Go to next message
czinsou
Messages: 23
Registered: August 2009
Junior Member
Hi, when I run that query , it's OK :
WITH XMLT AS (SELECT xmltype('
<InvoiceTransmission>
 <Invoice>
  <InvoiceHeader>
   <InvoiceNumber>ABX1234567</InvoiceNumber>
  </InvoiceHeader>
 <LineItem>
 <LineItemNumber>1</LineItemNumber>
<ChargeCode>P</ChargeCode>
</LineItem>
<LineItem>
<LineItemNumber>2</LineItemNumber>
<ChargeCode>C</ChargeCode>
</LineItem>
</Invoice>
</InvoiceTransmission>') data from dual)
SELECT a.LineItemNumber,a.ChargeCode
FROM XMLT,
XMLTABLE('/InvoiceTransmission/Invoice[InvoiceHeader/InvoiceNumber="ABX1234567]"]/LineItem'
PASSING XMLT.data COLUMNS
LineItemNumber NUMBER(6) PATH 'LineItemNumber',
ChargeCode VARCHAR2(1) PATH 'ChargeCode'
)a

Results : 
LINEITEMNUMBER CHARGECODE
-------------- ----------
             1 P         
             2 C         
2 rows selected


I need that to be included in a procedure and
have the value of InvoiceNumber ("ABX1234567") in parameters.

How can do to make my query modulable according InvoiceNumber value
beacause in fact XMLTable requires literal string.

Thanks a lot for your help.
Re: Problem with XMLTABLE with parameters [message #479986 is a reply to message #479962] Wed, 20 October 2010 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you get the answer to your question at http://www.orafaq.com/forum/t/162613/102589/?

Regards
Michel
Re: Problem with XMLTABLE with parameters [message #480070 is a reply to message #479986] Thu, 21 October 2010 00:18 Go to previous messageGo to next message
czinsou
Messages: 23
Registered: August 2009
Junior Member
No, that's not the same question
(the other topic is extract xmlns and I had the solution
at http://forums.oracle.com/forums/thread.jspa?threadID=1772303&tstart=0):
I trying to put variables parameters in XMLTABLE function
because I need to build a procedure to read the xml according specific values.
Actually I want to build a procedure like that:

PROCEDURE getItems(pInvoiceNumber IN VARCHAR2) IS 
vlin number; -- LineItemNumber
vcc number;  -- ChargeCode
BEGIN
SELECT a.LineItemNumber,a.ChargeCode INTO vlin,vcc
FROM XMLT,
XMLTABLE('/InvoiceTransmission/Invoice[InvoiceHeader/InvoiceNumber="'||pInvoiceNumber||'"]]/LineItem'
PASSING XMLT.data COLUMNS
LineItemNumber NUMBER(6) PATH 'LineItemNumber',
ChargeCode VARCHAR2(1) PATH 'ChargeCode'
)a
END;

But XMLTABLE only accept literal string, so '||pInvoiceNumber||' does not work. Someone has the solution ?


Re: Problem with XMLTABLE with parameters [message #480076 is a reply to message #480070] Thu, 21 October 2010 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I know this is not the same question, I just wanted to know if you get the answer as I (and I bet many) don't know it neither I know this one as I don't use XMLTABLE syntax as you could see in the previous questions you posted but the following one:
SQL> WITH XMLT AS (SELECT xmltype('
  2  <InvoiceTransmission>
  3   <Invoice>
  4    <InvoiceHeader>
  5     <InvoiceNumber>ABX1234567</InvoiceNumber>
  6    </InvoiceHeader>
  7   <LineItem>
  8   <LineItemNumber>1</LineItemNumber>
  9  <ChargeCode>P</ChargeCode>
 10  </LineItem>
 11  <LineItem>
 12  <LineItemNumber>2</LineItemNumber>
 13  <ChargeCode>C</ChargeCode>
 14  </LineItem>
 15  </Invoice>
 16  </InvoiceTransmission>') data from dual)
 17  SELECT extractvalue(value(y), '/LineItem/LineItemNumber') LineItemNumber,
 18         extractvalue(value(y), '/LineItem/ChargeCode') ChargeCode
 19  from xmlt, 
 20       table(xmlsequence(extract(data, '/InvoiceTransmission/Invoice'))) x,
 21       table(xmlsequence(extract(data, '/InvoiceTransmission/Invoice/LineItem'))) y
 22  where extractvalue(value(x), '/Invoice/InvoiceHeader/InvoiceNumber') = 'ABX1234567'
 23  /
LINEITEMNUMBER  CHARGECODE
--------------- ----------
1               P
2               C


Regards
Michel
Re: Problem with XMLTABLE with parameters [message #480080 is a reply to message #480076] Thu, 21 October 2010 01:02 Go to previous messageGo to next message
czinsou
Messages: 23
Registered: August 2009
Junior Member
Thanks , your query works fine.
I just forgot to mention that I could have several <Invoice> element :
WITH XMLT AS (SELECT xmltype('
<InvoiceTransmission>
  <Invoice>
	<InvoiceHeader>
	    <InvoiceNumber>ABX1234567</InvoiceNumber>
	</InvoiceHeader>
	<LineItem>
	   <LineItemNumber>1</LineItemNumber>
	   <ChargeCode>P</ChargeCode>
	</LineItem>
	<LineItem>
	   <LineItemNumber>2</LineItemNumber>
	    <ChargeCode>C</ChargeCode>
	</LineItem>
   </Invoice>
   <Invoice>
      <InvoiceHeader>
	<InvoiceNumber>ABX9999</InvoiceNumber>
      </InvoiceHeader>
      <LineItem>
	<LineItemNumber>1</LineItemNumber>
	<ChargeCode>K</ChargeCode>
      </LineItem>
      <LineItem>
	<LineItemNumber>2</LineItemNumber>
	<ChargeCode>T</ChargeCode>
      </LineItem>
   </Invoice>
</InvoiceTransmission>') data from dual)
SELECT 
extractvalue(value(y), '/LineItem/LineItemNumber') LineItemNumber,
extractvalue(value(y), '/LineItem/ChargeCode') ChargeCode
FROM xmlt, 
TABLE(xmlsequence(extract(data, '/InvoiceTransmission/Invoice'))) x,
TABLE(xmlsequence(extract(data, '/InvoiceTransmission/Invoice/LineItem'))) y
WHERE extractvalue(value(x), '/Invoice/InvoiceHeader/InvoiceNumber') = 'ABX1234567'

Results:
LINEITEMNUMBER CHARGECODE
---------------- --------------
1 P
2 C
1 K
2 T
4 rows selected

I have 4 rows selected whereas I was expected to have only 2
like the previous query:

LINEITEMNUMBER CHARGECODE
--------------- ----------
1 P
2 C

[Updated on: Thu, 21 October 2010 01:04]

Report message to a moderator

Re: Problem with XMLTABLE with parameters [message #480082 is a reply to message #480080] Thu, 21 October 2010 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry I missed to relate X and Y:
SQL> WITH XMLT AS (SELECT xmltype('
  2  <InvoiceTransmission>
  3    <Invoice>
  4   <InvoiceHeader>
  5       <InvoiceNumber>ABX1234567</InvoiceNumber>
  6   </InvoiceHeader>
  7   <LineItem>
  8      <LineItemNumber>1</LineItemNumber>
  9      <ChargeCode>P</ChargeCode>
 10   </LineItem>
 11   <LineItem>
 12      <LineItemNumber>2</LineItemNumber>
 13       <ChargeCode>C</ChargeCode>
 14   </LineItem>
 15     </Invoice>
 16     <Invoice>
 17        <InvoiceHeader>
 18   <InvoiceNumber>ABX9999</InvoiceNumber>
 19        </InvoiceHeader>
 20        <LineItem>
 21   <LineItemNumber>1</LineItemNumber>
 22   <ChargeCode>K</ChargeCode>
 23        </LineItem>
 24        <LineItem>
 25   <LineItemNumber>2</LineItemNumber>
 26   <ChargeCode>T</ChargeCode>
 27        </LineItem>
 28     </Invoice>
 29  </InvoiceTransmission>') data from dual)
 30  SELECT 
 31         extractvalue(value(y), '/LineItem/LineItemNumber') LineItemNumber,
 32         extractvalue(value(y), '/LineItem/ChargeCode') ChargeCode
 33  FROM xmlt, 
 34       TABLE(xmlsequence(extract(data, '/InvoiceTransmission/Invoice'))) x,
 35       TABLE(xmlsequence(extract(value(x), '/Invoice/LineItem'))) y
 36  WHERE extractvalue(value(x), '/Invoice/InvoiceHeader/InvoiceNumber') = 'ABX1234567'
 37  /
LINEITEMNUMBER  CHARGECODE
--------------- ----------
1               P
2               C

Regards
Michel

[Updated on: Thu, 21 October 2010 01:26]

Report message to a moderator

Re: Problem with XMLTABLE with parameters [message #480083 is a reply to message #480082] Thu, 21 October 2010 01:31 Go to previous messageGo to next message
czinsou
Messages: 23
Registered: August 2009
Junior Member
Thanks a lot Michel, I think I'm gonne give up XMLTABLE for XMLSEQUENCE.
Re: Problem with XMLTABLE with parameters [message #480084 is a reply to message #480083] Thu, 21 October 2010 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Some stuff are accessible only with XMLTABLE, see the following topic:
http://www.orafaq.com/forum/mv/msg/162210/478210/102589/#msg_478210

Regards
Michel
Re: Problem with XMLTABLE with parameters [message #480091 is a reply to message #480084] Thu, 21 October 2010 02:53 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
A possible solution is using dynamic sql:
SET SERVEROUTPUT ON SIZE 900000;

DROP TABLE test_176;
/

CREATE TABLE test_176 AS 
  SELECT 1 id,
    XMLTYPE('
     <table>
       <item>
         <id>1001</id>
         <nm>ABCD</nm>
       </item>
       <item>
         <id>1002</id>
         <nm>EFGH</nm>
       </item>
       <item>
         <id>1006</id>
         <nm>KKK</nm>
       </item>
       <item>
         <id>1007</id>
       </item>
     </table>') xdata
  FROM dual;
/

DECLARE
  xmlstr  VARCHAR2(1024);
  xpos    INTEGER;
  xid     INTEGER;    
  xnm     VARCHAR2(20);
  testid  INTEGER := 1006;    
BEGIN
 xmlstr := 'SELECT  ipos, iid, inm FROM test_176,
   xmltable(''for $f in //item  where $f//id='||testid||' return $f''
            PASSING xdata  
            COLUMNS
              ipos FOR ORDINALITY,
              iid  INTEGER PATH ''//id'',
              inm  VARCHAR2(20) PATH ''//nm'')';

 EXECUTE IMMEDIATE xmlstr INTO xpos, xid, xnm;

 dbms_output.put_line('Position='||xpos||' ID='||xid||' NM='||xnm);
               
END;

Position=1 ID=1006 NM=KKK 

Re: Problem with XMLTABLE with parameters [message #480141 is a reply to message #480084] Thu, 21 October 2010 07:36 Go to previous messageGo to next message
czinsou
Messages: 23
Registered: August 2009
Junior Member
Thanks _jum but I need to return more than one row , I don't want to implement pl/sql tables except if it's my last option.
Thanks a lot for your answer Michel, I can go forward with that in my project.
I'm facing another problem. The query is based on the same model you gave me :
WITH xmlt AS
(SELECT xmltype('
<InvoiceTransmission>
 <Invoice>
   <InvoiceHeader>
    <InvoiceNumber>ABX1234567</InvoiceNumber>
   </InvoiceHeader>
   <LineItemDetail>
    <DetailNumber>7</DetailNumber>
    <LineItemNumber>3</LineItemNumber>
    <RejectionMemoDetails> 
     <RejectionMemoNumber>3</RejectionMemoNumber>
     <AirWaybillBreakdown>
      <BreakdownSerialNumber>1</BreakdownSerialNumber>
      <AWBDate>071102</AWBDate>
     </AirWaybillBreakdown>
     <AirWaybillBreakdown>
      <BreakdownSerialNumber>2</BreakdownSerialNumber>
      <AWBDate>081102</AWBDate>
     </AirWaybillBreakdown>
    </RejectionMemoDetails>	
   </LineItemDetail>
  </Invoice>
  <Invoice>
   <InvoiceHeader>
    <InvoiceNumber>ABX999999</InvoiceNumber>
   </InvoiceHeader>
   <LineItemDetail>
    <DetailNumber>7</DetailNumber>
    <LineItemNumber>3</LineItemNumber>
    <RejectionMemoDetails> 
     <RejectionMemoNumber>3</RejectionMemoNumber>
     <AirWaybillBreakdown>
      <BreakdownSerialNumber>3</BreakdownSerialNumber>
      <AWBDate>071102</AWBDate>
     </AirWaybillBreakdown>
     <AirWaybillBreakdown>
      <BreakdownSerialNumber>4</BreakdownSerialNumber>
      <AWBDate>081102</AWBDate>
     </AirWaybillBreakdown>
    </RejectionMemoDetails>	
   </LineItemDetail>
 </Invoice>
</InvoiceTransmission>') data from dual)
SELECT 
extractvalue(value(x),'/AirWaybillBreakdown/BreakdownSerialNumber') BreakdownSerialNumber
FROM XMLT, 
TABLE(xmlsequence(extract(data, '/InvoiceTransmission/Invoice'))) y, 
TABLE(xmlsequence(extract(value(y), '/Invoice/LineItemDetail'))) z, 
TABLE(xmlsequence(extract(value(z), '/RejectionMemoDetails/AirWaybillBreakdown'))) x 
WHERE extractvalue(value(y), '/Invoice/InvoiceHeader/InvoiceNumber') = 'ABX1234567' 
AND extractvalue(value(z), '/LineItemDetail/LineItemNumber') = 3 
AND extractvalue(value(z), '/LineItemDetail/DetailNumber') = 7;


I have no rows returned. I'm expecting to have the result :

BREAKDOWNSERIALNUMBER
---------------------
1
2

So can you please tell me what is my mistake ?

[Updated on: Thu, 21 October 2010 07:48]

Report message to a moderator

Re: Problem with XMLTABLE with parameters [message #480142 is a reply to message #480141] Thu, 21 October 2010 07:44 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
check and correct the level of RejectionMemoDetails/AirWaybillBreakdown':
...
  TABLE(xmlsequence(extract(value(z), '//RejectionMemoDetails/AirWaybillBreakdown'))) x 
...
Re: Problem with XMLTABLE with parameters [message #480144 is a reply to message #480142] Thu, 21 October 2010 07:53 Go to previous messageGo to next message
czinsou
Messages: 23
Registered: August 2009
Junior Member
Thanks a lot _jum , It works fine !
What a speed !
Re: Problem with XMLTABLE with parameters [message #480155 is a reply to message #480144] Thu, 21 October 2010 08:35 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"z" value starts with "/LineItemDetail".
You can either specify this one when the path must be this or use "//" to tell it is anywhere below the root:
SQL> WITH xmlt AS
  2  (SELECT xmltype('
  3  <InvoiceTransmission>
  4   <Invoice>
  5     <InvoiceHeader>
  6      <InvoiceNumber>ABX1234567</InvoiceNumber>
  7     </InvoiceHeader>
  8     <LineItemDetail>
  9      <DetailNumber>7</DetailNumber>
 10      <LineItemNumber>3</LineItemNumber>
 11      <RejectionMemoDetails> 
 12       <RejectionMemoNumber>3</RejectionMemoNumber>
 13       <AirWaybillBreakdown>
 14        <BreakdownSerialNumber>1</BreakdownSerialNumber>
 15        <AWBDate>071102</AWBDate>
 16       </AirWaybillBreakdown>
 17       <AirWaybillBreakdown>
 18        <BreakdownSerialNumber>2</BreakdownSerialNumber>
 19        <AWBDate>081102</AWBDate>
 20       </AirWaybillBreakdown>
 21      </RejectionMemoDetails> 
 22     </LineItemDetail>
 23    </Invoice>
 24    <Invoice>
 25     <InvoiceHeader>
 26      <InvoiceNumber>ABX999999</InvoiceNumber>
 27     </InvoiceHeader>
 28     <LineItemDetail>
 29      <DetailNumber>7</DetailNumber>
 30      <LineItemNumber>3</LineItemNumber>
 31      <RejectionMemoDetails> 
 32       <RejectionMemoNumber>3</RejectionMemoNumber>
 33       <AirWaybillBreakdown>
 34        <BreakdownSerialNumber>3</BreakdownSerialNumber>
 35        <AWBDate>071102</AWBDate>
 36       </AirWaybillBreakdown>
 37       <AirWaybillBreakdown>
 38        <BreakdownSerialNumber>4</BreakdownSerialNumber>
 39        <AWBDate>081102</AWBDate>
 40       </AirWaybillBreakdown>
 41      </RejectionMemoDetails> 
 42     </LineItemDetail>
 43   </Invoice>
 44  </InvoiceTransmission>') data from dual)
 45  SELECT 
 46  extractvalue(value(x),'/AirWaybillBreakdown/BreakdownSerialNumber') BreakdownSerialNumber
 47  FROM XMLT, 
 48  TABLE(xmlsequence(extract(data, '/InvoiceTransmission/Invoice'))) y, 
 49  TABLE(xmlsequence(extract(value(y), '/Invoice/LineItemDetail'))) z, 
 50  TABLE(xmlsequence(extract(value(z), '/LineItemDetail/RejectionMemoDetails/AirWaybillBreakdown'))) x 
 51  WHERE extractvalue(value(y), '/Invoice/InvoiceHeader/InvoiceNumber') = 'ABX1234567' 
 52  AND extractvalue(value(z), '/LineItemDetail/LineItemNumber') = 3 
 53  AND extractvalue(value(z), '/LineItemDetail/DetailNumber') = 7;
BREAKDOWNSERIALNUMBER
--------------------------------------------------------------------------------------------------------------
1
2

When the path is clear and precise, it is better to use the say path and avoid using the "//" shortcut.
An advantage of using this shortcut is that you can change the top of the path keeping the bottom without changing the code, its drawbacks is like using "%" or "*": if something else satisfies the condition then it is also selected.

Regards
Michel
Previous Topic: update the node value
Next Topic: id
Goto Forum:
  


Current Time: Thu Mar 28 05:14:36 CDT 2024