This discussion is archived
7 Replies Latest reply: Mar 3, 2013 6:18 AM by odie_63 RSS

left outer join

form_dev Newbie
Currently Being Moderated
Hi,

I am having problem with left outer join, Can you please help me on this issue

XML is
<RevenueAccountingRecords xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.eds.com/AirlineSOASchema/RevenueAccountingHandoff">
<RevenueAccountingRecord>
<BatchControlNumber>7222</BatchControlNumber>
<BatchControlRecordNumber>2</BatchControlRecordNumber>
<FlightSegment>
<CouponNumber>1</CouponNumber>
<FlightNumber>VS0001</FlightNumber>
</FlightSegment>
</RevenueAccountingRecord>
<RevenueAccountingRecord>
<BatchControlNumber>7222</BatchControlNumber>
<BatchControlRecordNumber>37</BatchControlRecordNumber>
<AccountingHandoffDateTime>2008-09-13T07:01:46Z</AccountingHandoffDateTime>
<TransactionDateTime>2008-09-13T07:01:46Z</TransactionDateTime>
<HistoryTransactionNumber>0</HistoryTransactionNumber>
<RecordType>99</RecordType>
<PrimaryIssueDate>0001-01-01T00:00:00</PrimaryIssueDate>
<FreeFlowText>Processed :35 Records</FreeFlowText>
</RevenueAccountingRecord>

</RevenueAccountingRecords>


Xquery is
select a.*, y.*
from vgxml t
,
xmltable(
XMLNamespaces( 'http://www.eds.com/AirlineSOASchema/RevenueAccountingHandoff' as "xsd"),
'/xsd:RevenueAccountingRecords/xsd:RevenueAccountingRecord'
passing t.xmldoc
columns
BatchControlNumber number path 'xsd:BatchControlNumber'
, BatchControlRecordNumber number path 'xsd:BatchControlRecordNumber'
,FlightSegment xmltype path 'xsd:FlightSegment'

) a
left outer join

xmltable(
XMLNamespaces( 'http://www.eds.com/AirlineSOASchema/RevenueAccountingHandoff' as "xsd"),
'/xsd:FlightSegment'
passing a.FlightSegment
columns
FlightNumber varchar2(15) path 'xsd:FlightNumber'

) y on (1=1)

but when i ran this I am getting flightnumber always null;


7222     2     (XMLTYPE)     null



thanking you in advance.

Edited by: form_dev on Jan 27, 2013 6:01 AM
  • 1. Re: left outer join
    damorgan Oracle ACE Director
    Currently Being Moderated
    And your Oracle version number is?
  • 2. Re: left outer join
    odie_63 Guru
    Currently Being Moderated
    It's probably a bug.

    Here are a couple of workarounds that work for me on 11.2.0.2 :

    Adding a FOR ORDINALITY clause in the first XMLTable :
    SQL> select a.BatchControlNumber, a.BatchControlRecordNumber, y.FlightNumber
      2  from vgxml t
      3     , xmltable(
      4         xmlnamespaces(default 'http://www.eds.com/AirlineSOASchema/RevenueAccountingHandoff')
      5       , '/RevenueAccountingRecords/RevenueAccountingRecord'
      6         passing  t.xmldoc
      7         columns BatchControlNumber       number path 'BatchControlNumber'
      8               , BatchControlRecordNumber number path 'BatchControlRecordNumber'
      9               , FlightSegment            xmltype  path 'FlightSegment'
     10               , rn                       for ordinality
     11       )  a
     12       left outer join
     13       xmltable(
     14         xmlnamespaces(default 'http://www.eds.com/AirlineSOASchema/RevenueAccountingHandoff')
     15       , '/FlightSegment'
     16         passing a.FlightSegment
     17         columns FlightNumber varchar2(15) path 'FlightNumber'
     18       )  y  on (1=1)
     19  ;
     
    BATCHCONTROLNUMBER BATCHCONTROLRECORDNUMBER FLIGHTNUMBER
    ------------------ ------------------------ ---------------
                  7222                        2 VS0001
                  7222                       37 
     
    Adding another OUTER JOIN :
    SQL> select a.BatchControlNumber, a.BatchControlRecordNumber, y.FlightNumber
      2  from vgxml t
      3       left outer join
      4       xmltable(
      5         xmlnamespaces(default 'http://www.eds.com/AirlineSOASchema/RevenueAccountingHandoff')
      6       , '/RevenueAccountingRecords/RevenueAccountingRecord'
      7         passing  t.xmldoc
      8         columns BatchControlNumber       number path 'BatchControlNumber'
      9               , BatchControlRecordNumber number path 'BatchControlRecordNumber'
     10               , FlightSegment            xmltype  path 'FlightSegment'
     11       )  a on (1=1)
     12       left outer join
     13       xmltable(
     14         xmlnamespaces(default 'http://www.eds.com/AirlineSOASchema/RevenueAccountingHandoff')
     15       , '/FlightSegment'
     16         passing a.FlightSegment
     17         columns FlightNumber varchar2(15) path 'FlightNumber'
     18       )  y  on (1=1)
     19  ;
     
    BATCHCONTROLNUMBER BATCHCONTROLRECORDNUMBER FLIGHTNUMBER
    ------------------ ------------------------ ---------------
                  7222                        2 VS0001
                  7222                       37 
     
    Of course, if you don't expect more than one FlightSegment per record then you don't need the OUTER JOIN.
  • 3. Re: left outer join
    form_dev Newbie
    Currently Being Moderated
    Thanks a lot, Odie. Soultion you gave worked like a charm.
  • 4. Re: left outer join
    form_dev Newbie
    Currently Being Moderated
    How do I run xquery statement from XMLTYPE parameter passed in pl/sql?

    Edited by: form_dev on Feb 23, 2013 2:02 PM
  • 5. Re: left outer join
    odie_63 Guru
    Currently Being Moderated
    There's nothing special to do differently than in a standalone SQL statement. What have you tried?

    Just pass the XMLType parameter to XMLTable or XMLQuery using the PASSING clause.
  • 6. Re: left outer join
    form_dev Newbie
    Currently Being Moderated
    No, Since I am very new to XQUERY, Can you please provide an example.
  • 7. Re: left outer join
    odie_63 Guru
    Currently Being Moderated
    Your question has nothing to do with XQuery, don't focus on that.

    It's just a matter of embedding an SQL statement in PL/SQL code. Are you also new to PL/SQL programming?
    SQL> set serveroutput on
    SQL> 
    SQL> DECLARE
      2  
      3    v_xml  xmltype := xmltype('<root><item>ABC</item><item>XYZ</item></root>') ;
      4  
      5  BEGIN
      6  
      7    for r in (
      8      select val, rn
      9      from xmltable(
     10           '/root/item'
     11           passing v_xml
     12           columns val varchar2(3) path '.'
     13              , rn  for ordinality
     14           )
     15    )
     16    loop
     17  
     18      dbms_output.put_line('Item #' || to_char(r.rn) || ' = ' || r.val);
     19  
     20    end loop;
     21  
     22  END;
     23  /
     
    Item #1 = ABC
    Item #2 = XYZ
     
    PL/SQL procedure successfully completed
     

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points