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  |
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 #480070 is a reply to message #479986] |
Thu, 21 October 2010 00:18  |
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 |
 |
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
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 |
|
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #480091 is a reply to message #480084] |
Thu, 21 October 2010 02:53 |
_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 |
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 #480155 is a reply to message #480144] |
Thu, 21 October 2010 08:35 |
|
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
Goto Forum:
Current Time: Mon Feb 03 17:22:47 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
