Hello,
I'm on Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
and use the following statement to get the child data to a so called "LineItem"
WITH xtbl AS (SELECT xmltype ('<?xml version="1.0" encoding="UTF-8"?>
<FinancingContract xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="EE1 Request - createContractProposal.xsd">
\<ContractLineItems>
\<LineItem assettype="hardware" id="740362-100">
\<Item id="741509-100-1">
\<ItemValue>
\<BillingType>onetime\</BillingType>
\<BuyBackPrice>270.88\</BuyBackPrice>
\<ContractValue>2047.45\</ContractValue>
\<ResellerPurchasePrice>1909.63\</ResellerPurchasePrice>
\<LessorPurchasePrice>2318.33\</LessorPurchasePrice>
\<ResellerMargin>408.70\</ResellerMargin>
\<Currency>EUR\</Currency>
\</ItemValue>
\<Fee>
\<BillingType>monthly\</BillingType>
\<Currency>EUR\</Currency>
\<Amount>80.46\</Amount>
\</Fee>
\</Item>
\<Item id="741509-100-2">
\<ItemValue>
\<BillingType>onetime\</BillingType>
\<BuyBackPrice>270.88\</BuyBackPrice>
\<ContractValue>2047.45\</ContractValue>
\<ResellerPurchasePrice>1909.63\</ResellerPurchasePrice>
\<LessorPurchasePrice>2318.33\</LessorPurchasePrice>
\<ResellerMargin>408.70\</ResellerMargin>
\<Currency>EUR\</Currency>
\</ItemValue>
\<Fee>
\<BillingType>monthly\</BillingType>
\<Currency>EUR\</Currency>
\<Amount>80.46\</Amount>
\</Fee>
\</Item>
\<Product>
\<DistributionMaterialNumber>3134188\</DistributionMaterialNumber>
\<ManufacturerPartNumber>LQT-00041\</ManufacturerPartNumber>
\<Description>MICROSOFT Surface Laptop 2 (13,5" | i7 | 16 GB | 512 GB | Cobalt Blue)\</Description>
\<EAN>889842386127\</EAN>
\<Category>Workplace User Device\</Category>
\<Manufacturer>MICROSOFT\</Manufacturer>
\</Product>
\<Project>
\<ProjectIdentifier source="distributor">12345678\</ProjectIdentifier>
\<ProjectManufacturer>MICROSOFT\</ProjectManufacturer>
\<ProjectDescription>SurfaceLaptop 2 Rabattaktion\</ProjectDescription>
\</Project>
\<Quantity>2\</Quantity>
\</LineItem>
\<LineItem assettype="service" id="740362-200">
\<Item id="741509-200-1">
\<ItemValue>
\<BillingType>onetime\</BillingType>
\<ContractValue>218.94\</ContractValue>
\<ResellerPurchasePrice>218.94\</ResellerPurchasePrice>
\<LessorPurchasePrice>218.94\</LessorPurchasePrice>
\<Currency>EUR\</Currency>
\</ItemValue>
\<Fee>
\<BillingType>monthly\</BillingType>
\<Currency>EUR\</Currency>
\<Amount>0\</Amount>
\</Fee>
\</Item>
\<Item id="741509-200-2">
\<ItemValue>
\<BillingType>onetime\</BillingType>
\<ContractValue>218.94\</ContractValue>
\<ResellerPurchasePrice>218.94\</ResellerPurchasePrice>
\<LessorPurchasePrice>218.94\</LessorPurchasePrice>
\<Currency>EUR\</Currency>
\</ItemValue>
\<Fee>
\<BillingType>monthly\</BillingType>
\<Currency>EUR\</Currency>
\<Amount>0\</Amount>
\</Fee>
\</Item>
\<Product>
\<DistributionMaterialNumber>2859643\</DistributionMaterialNumber>
\<ManufacturerPartNumber>LQT-00041\</ManufacturerPartNumber>
\<Description>CarePack\</Description>
\<EAN>889842214512\</EAN>
\<Category>CarePack\</Category>
\<Manufacturer>MICROSOFT\</Manufacturer>
\</Product>
\<Quantity>2\</Quantity>
\</LineItem>
\</ContractLineItems>
</FinancingContract>') AS content FROM dual)
SELECT x.*
FROM xtbl t,
XMLTABLE ('
/FinancingContract/ContractLineItems/LineItem/Item
'
PASSING t.content
COLUMNS id VARCHAR2(30) PATH '@id'
,BillingType VARCHAR2(30) PATH 'ItemValue/BillingType'
,BuyBackPrice NUMBER(15,2) PATH 'ItemValue/BuyBackPrice'
,ContractValue NUMBER(15,2) PATH 'ItemValue/ContractValue'
,ResellerPurchasePrice NUMBER(15,2) PATH 'ItemValue/ResellerPurchasePrice'
,LessorPurchasePrice NUMBER(15,2) PATH 'ItemValue/LessorPurchasePrice'
,ResellerMargin NUMBER(15,2) PATH 'ItemValue/ResellerMargin'
,Currency VARCHAR2(3) PATH 'ItemValue/Currency'
,BillingType\_2 VARCHAR2(30) PATH 'Fee/BillingType'
,Currency\_2 VARCHAR2(3) PATH 'Fee/Currency'
,Amount NUMBER(15,2) PATH 'Fee/Amount'
) x
;
Result looks like
Thats fine so far. I would like to have in addition the two attribute informations
| | <LineItem assettype="service" id="740362-200"> |
of the individual parent at the beginning or at the end of each item.
All my tries ended with a mess or no data, so any help is appreciated.
Thanks a lot in advance.
Regards Carsten