Oracle XMLTABLE函数无法正确获取数据 [英] Oracle XMLTABLE function not fetching data correctly
问题描述
我正在使用Oracle 11g XMLTABLE函数从附加的XML文件中获取数据.当我执行代码时,我得到3行.我正在正确检索产品,为与客户相对应的每个产品生成一行.问题是我没有得到其余的客户数据,而客户名,客户DOB和客户电子邮件都为空.由于某种原因,我正在使用的代码Oracle没有将其升级2个级别.请帮忙!我在做什么错了?
I am using Oracle 11g XMLTABLE function to fetch data from the attached XML file. When I execute the code, I get 3 rows. I am retrieving the Products correctly generating one row for each Product corresponding to a Customer. The problem is that I'm not getting the rest of the Customer data, I' getting nulls for CustomerName, CustomerDOB, and CustomerEmail. For some reason the code that I am using, Oracle is not fetching 2 levels up. Please help! What am I doing wrong?
<?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>
CODE
SELECT
CustomerName,
CustomerDOB,
CustomerEmail,
PoductType
FROM XMLTABLE ( '/USACustomers/NewYorkCustomers/Customer/CustomerProducts/CustomerProduct'
PASSING xmltype (BFILENAME ('BACKUP', 'TEST.xml'), NLS_CHARSET_ID ('AL32UTF8'))
COLUMNS CustomerName VARCHAR2(50) PATH 'CustomerData/CustomerName',
CustomerDOB VARCHAR2(50) PATH 'CustomerData/CustomerDOB',
CustomerEmail VARCHAR2(50) PATH 'CustomerData/CustomerEmail',
PoductType VARCHAR2(50) PATH 'PoductType',
ProductNumber VARCHAR2(50) PATH 'ProductNumber');
推荐答案
您可以这样操作(只需调用一个xmltable()
:
You can do it like this (with one xmltable()
call:
SELECT
CustomerName,
CustomerDOB,
CustomerEmail,
PoductType
FROM XMLTABLE ('for $i in $custt/USACustomers/NewYorkCustomers/Customer,
$j in $i//CustomerProduct
return <deNormalizedCust>{$i//CustomerData}{$j}</deNormalizedCust>'
PASSING xmltype(BFILENAME ('BACKUP', 'TEST.xml'), NLS_CHARSET_ID ('AL32UTF8'))
as "custt"
COLUMNS CustomerName VARCHAR2(50) PATH '/deNormalizedCust/CustomerData/CustomerName',
CustomerDOB VARCHAR2(50) PATH '/deNormalizedCust/CustomerData/CustomerDOB',
CustomerEmail VARCHAR2(50) PATH '/deNormalizedCust/CustomerData/CustomerEmail',
PoductType VARCHAR2(50) PATH '/deNormalizedCust/CustomerProduct/PoductType',
ProductNumber VARCHAR2(50) PATH '/deNormalizedCust/CustomerProduct/ProductNumber');
这篇关于Oracle XMLTABLE函数无法正确获取数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!