5 Replies Latest reply on Jul 2, 2019 9:08 AM by CarstenDD

    XMLTABLE get parent data to child

    CarstenDD

      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

        • 1. Re: XMLTABLE get parent data to child
          Mike Kutz

          The forum XML DB might be a better fit.

           

          I did this once .. 5+ years ago.  It involved a more complex XQuery than what you have ('/FinancingContract/ContractLineItems/LineItem/Item')

           

          The "alternative" would be to use XMLTABLE() twice. Once for the LineItem info (including an XMLType) and once for the other details (using the XMLType from the first XMLTable()

           

          It wasn't as fast as using a "proper" XQuery.

           

          MK

          1 person found this helpful
          • 2. Re: XMLTABLE get parent data to child
            CarstenDD

            Hello Mike,

            I moved the question to XML-DB.

            Regards Carsten

            • 3. Re: XMLTABLE get parent data to child
              mNem

              Not the efficient way of doing things ... but something like

               

              ...

              ,lineitem_id varchar2(20) path './../@id'

              ,lineitem_assettype varchar2(20) path './../@assettype'

              ) x

              ;

              1 person found this helpful
              • 4. Re: XMLTABLE get parent data to child
                Siva ManU

                It worked   . Thanks a lot .@mNem

                 

                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="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>

                            <Quantity>2</Quantity>

                        </LineItem>

                    </ContractLineItems>

                </FinancingContract>') AS content FROM dual)

                SELECT x.*

                FROM xtbl t,

                      XMLTABLE ('/FinancingContract/ContractLineItems/LineItem/Item'--[ ID="741509-200-1" ]'

                                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'

                                       ,lineitem_id varchar2(30) path './../@id'

                                       ,lineitem_assettype varchar2(30) path './../@assettype'

                               ) x

                ;

                • 5. Re: XMLTABLE get parent data to child
                  CarstenDD

                  Hello mNem,

                  for me it is a fine solution. I have tried '/../@id' but didn't realize the necessity to put ./ in front. You say "not the efficient way doing things" so what would you suggest to do more efficient?

                   

                  Regards Carsten