Home » Developer & Programmer » JDeveloper, Java & XML » Getting Wrong result set from XMLTable funciton (Oracle 11.2g)
| Getting Wrong result set from XMLTable funciton [message #633862] |
Thu, 26 February 2015 07:18  |
 |
Asfakul
Messages: 43
Registered: July 2014
|
Member |
|
|
Hi All,
I was trying to extract the Customer information from a Table's XML column. Was using the below SQL.
select t.id,y.CustomerName,y.CustomerDOB,y.CustomerEmail,z.Address,z.city,z.Zip,z.State
from test_xml t,
XMLTable('/USACustomers/NewYorkCustomers'
passing XMLType(t.xml_date)
columns Customer XMLType path '//Customer' ) x,
XMLTable('/Customer/CustomerData'
passing x.Customer
columns CustomerName varchar2(10) path '//CustomerName',
CustomerDOB varchar2(10) path '//CustomerDOB',
CustomerEmail varchar2(30) path '//CustomerEmail'
) y,
XMLTable('/Customer/CustomerAddress'
passing x.Customer
columns Address varchar2(10) path '//Address',
City varchar2(10) path '//City',
State varchar2(30) path '//State',
Zip varchar2(20) path '//Zip'
) z;
-- DDL for Table TEST_XML
--------------------------------------------------------
CREATE TABLE TEST_XML
( ID NUMBER,
XML_DATE CLOB
);
/
Insert into test_xml (ID,XML_DATE) values (1,'<?xml version="1.0" encoding="iso-8859-1"?>
<USACustomers>
<NewYorkCustomers>
<Customer>
<CustomerData>
<CustomerName>John Stark</CustomerName>
<CustomerDOB>07/09/1973</CustomerDOB>
<CustomerEmail>j.stark@liquid.com</CustomerEmail>
</CustomerData>
<CustomerAddress>
<Address>400 Railroad Way</Address>
<City>Larchmont</City>
<State>NY</State>
<Zip>10542</Zip>
</CustomerAddress>
<CustomerProducts>
<CustomerProduct>
<PoductType>CTD</PoductType>
<ProductNumber>2050035302</ProductNumber>
</CustomerProduct>
<CustomerProduct>
<PoductType>REC</PoductType>
<ProductNumber>2050920400</ProductNumber>
</CustomerProduct>
</CustomerProducts>
</Customer>
<Customer>
<CustomerData>
<CustomerName>Maria Stark</CustomerName>
<CustomerDOB>07/09/1972</CustomerDOB>
<CustomerEmail>m.stark@liquid.com</CustomerEmail>
</CustomerData>
<CustomerAddress>
<Address>410 Railroad Way</Address>
<City>Larchmont</City>
<State>NY</State>
<Zip>10542</Zip>
</CustomerAddress>
<CustomerProducts>
<CustomerProduct>
<PoductType>REC</PoductType>
<ProductNumber>2050920400</ProductNumber>
</CustomerProduct>
</CustomerProducts>
</Customer>
</NewYorkCustomers>
</USACustomers>');
While running the above query , I get the address field wrong , although Other fields are showing fine. Please let me know what's wrong here
[Edit MC: remove empty lines between each line.]
[Updated on: Thu, 26 February 2015 07:37] by Moderator Report message to a moderator |
|
|
|
| Re: Getting Wrong result set from XMLTable funciton [message #633863 is a reply to message #633862] |
Thu, 26 February 2015 07:45  |
 |
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
You need to correlate the xmltable expressions or use only one:
SQL> select t.id, x.CustomerName, x.CustomerDOB, x.CustomerEmail, x.Address, x.city, x.Zip, x.State
2 from test_xml t,
3 XMLTable('/USACustomers/NewYorkCustomers/Customer'
4 passing XMLType(t.xml_date)
5 columns
6 CustomerName varchar2(10) path '//CustomerName',
7 CustomerDOB varchar2(10) path '//CustomerDOB',
8 CustomerEmail varchar2(20) path '//CustomerEmail',
9 Address varchar2(10) path '//Address',
10 City varchar2(10) path '//City',
11 State varchar2(30) path '//State',
12 Zip varchar2(5) path '//Zip' ) x
13 /
ID CUSTOMERNA CUSTOMERDO CUSTOMEREMAIL ADDRESS CITY ZIP STATE
---------- ---------- ---------- -------------------- ---------- ---------- ----- -----------------
1 John Stark 07/09/1973 j.stark@liquid.com 400 Railro Larchmont 10542 NY
1 Maria Star 07/09/1972 m.stark@liquid.com 410 Railro Larchmont 10542 NY
2 rows selected.
[Edit: remove useless expression]
[Updated on: Thu, 26 February 2015 10:32] Report message to a moderator |
|
|
|
|
|
| Re: Getting Wrong result set from XMLTable funciton [message #633868 is a reply to message #633867] |
Thu, 26 February 2015 10:30 |
|
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
You have to use a result from a previous XMLTable as an input to a subsequent one.
For your example, for instance:
SQL> select t.id, y.CustomerName, y.CustomerDOB, y.CustomerEmail, z.Address, z.city, z.Zip, z.State
2 from test_xml t,
3 XMLTable('/USACustomers/NewYorkCustomers/Customer'
4 passing XMLType(t.xml_date)
5 columns
6 CustomerData xmltype path '/Customer/CustomerData',
7 CustomerAddress xmltype path '/Customer/CustomerAddress') x,
8 XMLTable('/CustomerData'
9 passing x.CustomerData
10 columns
11 CustomerName varchar2(10) path '//CustomerName',
12 CustomerDOB varchar2(10) path '//CustomerDOB',
13 CustomerEmail varchar2(20) path '//CustomerEmail') y,
14 XMLTable('/CustomerAddress'
15 passing x.CustomerAddress
16 columns
17 Address varchar2(10) path '//Address',
18 City varchar2(10) path '//City',
19 State varchar2(30) path '//State',
20 Zip varchar2(5) path '//Zip' ) z
21 /
ID CUSTOMERNA CUSTOMERDO CUSTOMEREMAIL ADDRESS CITY ZIP STATE
---------- ---------- ---------- -------------------- ---------- ---------- ----- -------------------
1 John Stark 07/09/1973 j.stark@liquid.com 400 Railro Larchmont 10542 NY
1 Maria Star 07/09/1972 m.stark@liquid.com 410 Railro Larchmont 10542 NY
2 rows selected.
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Jan 26 01:18:03 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
