Home » Developer & Programmer » JDeveloper, Java & XML » Extracting Data from Xml (merged)
Extracting Data from Xml (merged) [message #631230] Sat, 10 January 2015 12:49 Go to next message
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>');
/
Re: Extract value of a node using EXTRACTVALUE function [message #631233 is a reply to message #631230] Sat, 10 January 2015 14:33 Go to previous messageGo to next message
Michel Cadot
Messages: 67847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
but "name" clearly doesnot have any sub node


The problem is that you have several nodes "name" and this is why the function returns the error, it can't know which one you want.
If you want all of them then you have to first extract each (food) node:
SQL> select id, extractvalue(value(x),'/food/name')     name
  2  from breakfast_menu t, table(xmlsequence(extract(xmltype(t.data), '/breakfast_menu/food'))) x
  3  /
        ID NAME
---------- --------------------------------------------------
         1 Belgian Waffles
         1 Strawberry Belgian Waffles
         1 Berry-Berry Belgian Waffles
         1 French Toast
         1 Homestyle Breakfast

Re: Extract value of a node using EXTRACTVALUE function [message #631235 is a reply to message #631233] Sat, 10 January 2015 15:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
You can also use the newer XMLTABLE syntax, as demonstrated below.

SCOTT@orcl> SELECT banner FROM v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

5 rows selected.

SCOTT@orcl> SELECT * FROM breakfast_menu
  2  /

        ID
----------
DATA
--------------------------------------------------------------------------------
         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 cr
eam
</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>


1 row selected.

SCOTT@orcl> SELECT t.id, x.name
  2  FROM   breakfast_menu t,
  3  	    XMLTABLE
  4  	      ('/breakfast_menu/food'
  5  	       PASSING XMLTYPE (t.data)
  6  	       COLUMNS
  7  		 name  VARCHAR2(30)  PATH '/food/name') x
  8  /

        ID NAME
---------- ------------------------------
         1 Belgian Waffles
         1 Strawberry Belgian Waffles
         1 Berry-Berry Belgian Waffles
         1 French Toast
         1 Homestyle Breakfast

5 rows selected.

Extracting Data from Xml Using XMLTable Function [message #631311 is a reply to message #631230] Mon, 12 January 2015 10:54 Go to previous messageGo to next message
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


/forum/fa/12435/0/


[mod-edit: image inserted into message body by bb]


  • Attachment: Capture.PNG
    (Size: 8.34KB, Downloaded 456 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 Go to previous messageGo to next message
Michel Cadot
Messages: 67847
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous message
Barbara Boehmer
Messages: 8922
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

Previous Topic: Sql query for the unexpected scenario
Next Topic: Getting Wrong result set from XMLTable funciton
Goto Forum:
  


Current Time: Wed May 12 04:18:48 CDT 2021