7 Replies Latest reply: Mar 3, 2013 8:18 AM by odie_63 RSS

    left outer join

    form_dev
      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
          And your Oracle version number is?
          • 2. Re: left outer join
            odie_63
            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
              Thanks a lot, Odie. Soultion you gave worked like a charm.
              • 4. Re: left outer join
                form_dev
                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
                  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
                    No, Since I am very new to XQUERY, Can you please provide an example.
                    • 7. Re: left outer join
                      odie_63
                      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