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 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 66800
Registered: March 2007
Location: Nanterre, France, http://...
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 #633867 is a reply to message #633863] Thu, 26 February 2015 10:19 Go to previous messageGo to next message
Asfakul
Messages: 43
Registered: July 2014
Member
Nice!! Understood,How can I correlate In case I ever have to? Can you please give an example. Thanks in advance.
Re: Getting Wrong result set from XMLTable funciton [message #633868 is a reply to message #633867] Thu, 26 February 2015 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 66800
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Getting Wrong result set from XMLTable funciton [message #633935 is a reply to message #633868] Sat, 28 February 2015 03:41 Go to previous message
Asfakul
Messages: 43
Registered: July 2014
Member
Thanks ! Excellent. Surprised
Previous Topic: Extracting Data from Xml (merged)
Next Topic: creating views from XML
Goto Forum:
  


Current Time: Sat Jan 25 21:49:41 CST 2020