Home » Developer & Programmer » JDeveloper, Java & XML » Extracting Data from Xml (merged)
| Extracting Data from Xml (merged) [message #631230] |
Sat, 10 January 2015 12:49  |
 |
Asfakul
Messages: 43
Registered: July 2014
|
Member |
|
|
I am getting an error "Extractvalue returns value of only one node" while trying to execute the below Query.
select id, extractvalue(xmltype(t.data),'/breakfast_menu/food/name')
from breakfast_menu t
where id=1
I googled it and it says that for extractvalue to work the current node should not have any subnode but "name" clearly doesnot have any sub node. But I know my understanding is flawed. Please shed a little light on the XPath.
Here is DDL scripts .
create table breakfast_menu
(
id number,
data CLOB
);
/
select * from breakfast_menu;
insert into breakfast_menu
values(1,'<breakfast_menu>
<food>
<name>Belgian Waffles</name>
<price>$5.95</price>
<description>
Two of our famous Belgian Waffles with plenty of real maple syrup
</description>
<calories>650</calories>
</food>
<food>
<name>Strawberry Belgian Waffles</name>
<price>$7.95</price>
<description>
Light Belgian waffles covered with strawberries and whipped cream
</description>
<calories>900</calories>
</food>
<food>
<name>Berry-Berry Belgian Waffles</name>
<price>$8.95</price>
<description>
Light Belgian waffles covered with an assortment of fresh berries and whipped cream
</description>
<calories>900</calories>
</food>
<food>
<name>French Toast</name>
<price>$4.50</price>
<description>
Thick slices made from our homemade sourdough bread
</description>
<calories>600</calories>
</food>
<food>
<name>Homestyle Breakfast</name>
<price>$6.95</price>
<description>
Two eggs, bacon or sausage, toast, and our ever-popular hash browns
</description>
<calories>950</calories>
</food>
</breakfast_menu>');
/
|
|
|
|
|
|
|
|
| Extracting Data from Xml Using XMLTable Function [message #631311 is a reply to message #631230] |
Mon, 12 January 2015 10:54  |
|
Asfakul
Messages: 43
Registered: July 2014
|
Member |
|
|
Hi All, I have just started learning handling XML data in PLSQL , Here is my Problem.
create table purchase_order
(
data XMLType
);
insert into purchase_order
values(XMLType('<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation=
"http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd">
<Reference>SBELL-2002100912333601PDT</Reference>
<Actions>
<Action>
<User>SVOLLMAN</User>
</Action>
</Actions>
<Reject/>
<Requestor>Sarah J. Bell</Requestor>
<User>SBELL</User>
<CostCenter>S30</CostCenter>
<ShippingInstructions>
<name>Sarah J. Bell</name>
<address>400 Oracle Parkway
Redwood Shores
CA
94065
USA</address>
<telephone>650 506 7400</telephone>
</ShippingInstructions>
<SpecialInstructions>Air Mail</SpecialInstructions>
<LineItems>
<LineItem ItemNumber="1">
<Description>A Night to Remember</Description>
<Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="2">
<Description>The Unbearable Lightness Of Being</Description>
<Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="3">
<Description>Sisters</Description>
<Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
</LineItem>
</LineItems>
</PurchaseOrder>'));
I want to extract values like ItemNumber , Description, Part Id , UnitPrice, Quantity and show them as Relational Table. But I am getting an Error. I have attached the error message . Here is my Code.
select x.*
from purchase_order t,
xmltable('/PurchaseOrder'
passing t.data
columns Reference varchar2(300) path 'Reference',
Usr varchar2(20) path '//Action',
Requestor varchar2(20) path '//Requestor',
CostCenter varchar2(20) path '//CostCenter',
ShippingInstructions varchar2(500) path '//ShippingInstructions',
SpecialInstructions varchar2(50) path '//SpecialInstructions',
ItemNumber varchar(10) path '//LineItems/LineItem/@ItemNumber',
Description varchar(100) path '//Description'
) x
[mod-edit: image inserted into message body by bb]
-
Attachment: Capture.PNG
(Size: 8.34KB, Downloaded 2935 times)
[Updated on: Mon, 12 January 2015 14:01] by Moderator Report message to a moderator |
|
|
|
| Re: Extracting Data from Xml Using XMLTable Function [message #631313 is a reply to message #631311] |
Mon, 12 January 2015 11:03 |
 |
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Do not attach an image for just an error message.
Use text in-line.
SQL> select x.*
2 from purchase_order t,
3 xmltable('/PurchaseOrder'
4 passing t.data
5 columns Reference varchar2(300) path 'Reference',
6 Usr varchar2(20) path '//Action',
7 Requestor varchar2(20) path '//Requestor',
8 CostCenter varchar2(20) path '//CostCenter',
9 ShippingInstructions varchar2(500) path '//ShippingInstructions',
10 SpecialInstructions varchar2(50) path '//SpecialInstructions',
11 ItemNumber varchar(10) path '//LineItems/LineItem/@ItemNumber',
12 Description varchar(100) path '//Description'
13 ) x
14 /
select x.*
*
ERROR at line 1:
ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
*Cause: The XQuery sequence passed in had more than one item.
*Action: Correct the XQuery expression to return a single item sequence.
This is the same error than your previous question.
I merge both topics.
|
|
|
|
| Re: Extracting Data from Xml Using XMLTable Function [message #631342 is a reply to message #631311] |
Mon, 12 January 2015 15:08 |
 |
Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
When you have more than one value with the same name, then you have to declare that as xmltype and use another xmltable to get the separate values, as shown below.
SCOTT@orcl> select * from purchase_order
2 /
DATA
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd">
<Reference>SBELL-2002100912333601PDT</Reference>
<Actions>
<Action>
<User>SVOLLMAN</User>
</Action>
</Actions>
<Reject/>
<Requestor>Sarah J. Bell</Requestor>
<User>SBELL</User>
<CostCenter>S30</CostCenter>
<ShippingInstructions>
<name>Sarah J. Bell</name>
<address>400 Oracle Parkway
Redwood Shores
CA
94065
USA</address>
<telephone>650 506 7400</telephone>
</ShippingInstructions>
<SpecialInstructions>Air Mail</SpecialInstructions>
<LineItems>
<LineItem ItemNumber="1">
<Description>A Night to Remember</Description>
<Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="2">
<Description>The Unbearable Lightness Of Being</Description>
<Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="3">
<Description>Sisters</Description>
<Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
</LineItem>
</LineItems>
</PurchaseOrder>
1 row selected.
SCOTT@orcl> column shippinginstructions format a31
SCOTT@orcl> select x.Reference, x.Usr, x.CostCenter,
2 x.name || chr(10) || x.address ShippingInstructions,
3 x.SpecialInstructions,
4 y.ItemNumber, y.Description
5 from purchase_order t,
6 xmltable
7 ('PurchaseOrder'
8 passing t.data
9 columns
10 Reference varchar2(25) path 'Reference',
11 Usr varchar2( 8) path 'Actions/Action/User',
12 Requestor varchar2(13) path 'Requestor',
13 CostCenter varchar2(10) path 'CostCenter',
14 Name varchar2(83) path 'ShippingInstructions/name',
15 Address varchar2(83) path 'ShippingInstructions/address',
16 SpecialInstructions varchar2(19) path 'SpecialInstructions',
17 LineItem xmltype path 'LineItems/LineItem') x,
18 xmltable
19 ('LineItem'
20 passing x.LineItem
21 columns
22 ItemNumber varchar2(10) path '@ItemNumber',
23 Description varchar2(33) path 'Description') y
24 /
REFERENCE USR COSTCENTER SHIPPINGINSTRUCTIONS SPECIALINSTRUCTIONS ITEMNUMBER DESCRIPTION
------------------------- -------- ---------- ------------------------------- ------------------- ---------- ---------------------------------
SBELL-2002100912333601PDT SVOLLMAN S30 Sarah J. Bell Air Mail 1 A Night to Remember
400 Oracle Parkway
Redwood Shores
CA
94065
USA
SBELL-2002100912333601PDT SVOLLMAN S30 Sarah J. Bell Air Mail 2 The Unbearable Lightness Of Being
400 Oracle Parkway
Redwood Shores
CA
94065
USA
SBELL-2002100912333601PDT SVOLLMAN S30 Sarah J. Bell Air Mail 3 Sisters
400 Oracle Parkway
Redwood Shores
CA
94065
USA
3 rows selected.
[Updated on: Mon, 12 January 2015 15:12] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Sun Jan 26 01:22:04 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
