从 XMLTable 中的 XML 检索节点值 (Oracle 11gR2)

Question

希望这很简单，我只是错过了它，但请考虑这个例子：

with et as(
     SELECT 
           xmlType('<Invoice>
    <InvoiceInformation>
        <Number>123456</Number>
    </InvoiceInformation>
    <InvoiceLines>
        <InvoiceLine>
            <Detail>
                <Amount>100</Amount>
                <Line>1</Line>
            </Detail>
            <Type>
                <CheesyPotato>
                    <Instructions>
                        <CookTime>120</CookTime>
                        <CookTimeUnits>Minutes</CookTimeUnits>
                        <CookTemperature>450</CookTemperature>
                    </Instructions>
                </CheesyPotato>
            </Type>
        </InvoiceLine>
        <InvoiceLine>
            <Detail>
                <Amount>10000</Amount>
                <Line>2</Line>
            </Detail>
            <Type>
                <DeathStar>
                    <Instructions>
                        <CookTime>4</CookTime>
                        <CookTimeUnits>5 "parsecs"</CookTimeUnits>
                        <CookTemperature>1000000</CookTemperature>
                    </Instructions>
                </DeathStar>
            </Type>
        </InvoiceLine>  
        <InvoiceLine>
            <Detail>
                <Amount>250</Amount>
                <Line>3</Line>
            </Detail>
            <Type>
                <Quiche>
                    <Instructions>
                        <CookTime>75</CookTime>
                        <CookTimeUnits>Minutes</CookTimeUnits>
                        <CookTemperature>350</CookTemperature>
                    </Instructions>
                </Quiche>
            </Type>      
        </InvoiceLine>    
    </InvoiceLines>  
</Invoice>  
 ') xt
      from dual
       ) 
        SELECT     
            ext.*
           FROM
           et,
           XMLTABLE(
           'for $Invoice in $INV/Invoice
                for $InvoiceItem in $Invoice/InvoiceLines/InvoiceLine
                  return <row> 
                  {
                    $Invoice
                    ,$InvoiceItem
                  } 
                  </row>'           
              PASSING et.xt as INV
                    COLUMNS
               INVOICENUMBER                   VARCHAR2  (6)       PATH 'Invoice/InvoiceInformation/Number'                    
              ,InvoiceLineNumber               VARCHAR2  (5)       PATH 'InvoiceLine/Detail/Line'
              ,Amount                          VARCHAR2  (5)       PATH 'InvoiceLine/Detail/Amount'
              ,CookTime                        VARCHAR2  (5)       PATH 'InvoiceLine/Type//Instructions/CookTime'
              ,CookTimeUnits                   VARCHAR2  (15)       PATH 'InvoiceLine/Type//Instructions/CookTimeUnits'
              ,CookTemperature                 VARCHAR2  (10)       PATH 'InvoiceLine/Type//Instructions/CookTemperature'
            ) ext
/            

--给我这些结果

    INVOICENUMBER INVOICELINENUMBER AMOUNT COOKTIME COOKTIMEUNITS   COOKTEMPERATURE 
------------- ----------------- ------ -------- --------------- --------------- 
123456        1                 100    120      Minutes         450             
123456        2                 10000  4        5 "parsecs"     1000000         
123456        3                 250    75       Minutes         350

但是我想在这个查询中获取 Type 值。因此，如何获取类型的“名称”的子节点，以便我的结果是这样的？

TypeChild       INVOICENUMBER INVOICELINENUMBER AMOUNT COOKTIME COOKTIMEUNITS   COOKTEMPERATURE 
------------------- ------------- ----------------- ------ -------- --------------- --------------- 
CheesyPotato    123456        1                 100    120      Minutes         450             
DeathStar       123456        2                 10000  4        5 "parsecs"     1000000         
Quiche          123456        3                 250    75       Minutes         350

我尝试过各种方法；沿着“，$InvoiceItem/Type/child/name”的路线无济于事，任何帮助将不胜感激，谢谢

或者我只是以错误的方式这样做？ （但是，无法更改的是我需要能够在查询中使用此 XML！）

Answer 1

,TypeChild  VARCHAR2(15)  PATH  'InvoiceLine/Type/*/name()'