Home > HowTo, Miscellaneous > How To : Load XML data into multiple tables

How To : Load XML data into multiple tables

Here’s a short article presenting a common technique to load data from a single XML document into different tables, using a single INSERT statement.
A typical use case is an XML structure describing a master-detail relationship, such as the classic invoice or purchase order document (header + line items).

For the below example, I’ll be using a part of the HR demo schema published as XML :

<Locations>
  <Location id="1000">
    <address>1297 Via Cola di Rie</address>
    <postalCode>00989</postalCode>
    <city>Roma</city>
    <countryId>IT</countryId>
    <Departments/>
  </Location>
  <Location id="1100">
    <address>93091 Calle della Testa</address>
    <postalCode>10934</postalCode>
    <city>Venice</city>
    <countryId>IT</countryId>
    <Departments/>
  </Location>
  <Location id="1200">
    <address>2017 Shinjuku-ku</address>
    <postalCode>1689</postalCode>
    <city>Tokyo</city>
    <stateProvince>Tokyo Prefecture</stateProvince>
    <countryId>JP</countryId>
    <Departments/>
  </Location>
  <Location id="1300">
    <address>9450 Kamiya-cho</address>
    <postalCode>6823</postalCode>
    <city>Hiroshima</city>
    <countryId>JP</countryId>
    <Departments/>
  </Location>
  <Location id="1400">
    <address>2014 Jabberwocky Rd</address>
    <postalCode>26192</postalCode>
    <city>Southlake</city>
    <stateProvince>Texas</stateProvince>
    <countryId>US</countryId>
    <Departments>
      <Department id="60">
        <name>IT</name>
        <managerId>103</managerId>
        <Employees>
          <Employee id="103">
            <firstName>Alexander</firstName>
            <lastName>Hunold</lastName>
            <email>AHUNOLD</email>
            <phoneNo>590.423.4567</phoneNo>
            <hireDate>2006-01-03</hireDate>
            <jobId>IT_PROG</jobId>
            <salary>9000</salary>
            <managerId>102</managerId>
          </Employee>
          <Employee id="104">
            <firstName>Bruce</firstName>
            <lastName>Ernst</lastName>
            <email>BERNST</email>
            <phoneNo>590.423.4568</phoneNo>
            <hireDate>2007-05-21</hireDate>
            <jobId>IT_PROG</jobId>
            <salary>6000</salary>
            <managerId>103</managerId>
          </Employee>
          <Employee id="105">
            <firstName>David</firstName>
            <lastName>Austin</lastName>
            <email>DAUSTIN</email>
            <phoneNo>590.423.4569</phoneNo>
            <hireDate>2005-06-25</hireDate>
            <jobId>IT_PROG</jobId>
            <salary>4800</salary>
            <managerId>103</managerId>
          </Employee>
          <Employee id="106">
            <firstName>Valli</firstName>
            <lastName>Pataballa</lastName>
            <email>VPATABAL</email>
            <phoneNo>590.423.4560</phoneNo>
            <hireDate>2006-02-05</hireDate>
            <jobId>IT_PROG</jobId>
            <salary>4800</salary>
            <managerId>103</managerId>
          </Employee>
          <Employee id="107">
            <firstName>Diana</firstName>
            <lastName>Lorentz</lastName>
            <email>DLORENTZ</email>
            <phoneNo>590.423.5567</phoneNo>
            <hireDate>2007-02-07</hireDate>
            <jobId>IT_PROG</jobId>
            <salary>4200</salary>
            <managerId>103</managerId>
          </Employee>
        </Employees>
      </Department>
    </Departments>
  </Location>
  <Location id="1500">
    <address>2011 Interiors Blvd</address>
    <postalCode>99236</postalCode>
    <city>South San Francisco</city>
    <stateProvince>California</stateProvince>
    <countryId>US</countryId>
    <Departments>
      <Department id="50">
        <name>Shipping</name>
        <managerId>121</managerId>
        <Employees>
          <Employee id="120">
            <firstName>Matthew</firstName>
            <lastName>Weiss</lastName>
            <email>MWEISS</email>
            <phoneNo>650.123.1234</phoneNo>
            <hireDate>2004-07-18</hireDate>
            <jobId>ST_MAN</jobId>
            <salary>8000</salary>
            <managerId>100</managerId>
          </Employee>
          <Employee id="121">
            <firstName>Adam</firstName>
            <lastName>Fripp</lastName>
            <email>AFRIPP</email>
            <phoneNo>650.123.2234</phoneNo>
            <hireDate>2005-04-10</hireDate>
            <jobId>ST_MAN</jobId>
            <salary>8200</salary>
            <managerId>100</managerId>
          </Employee>
          <Employee id="122">
            <firstName>Payam</firstName>
            <lastName>Kaufling</lastName>
            <email>PKAUFLIN</email>
            <phoneNo>650.123.3234</phoneNo>
            <hireDate>2003-05-01</hireDate>
            <jobId>ST_MAN</jobId>
            <salary>7900</salary>
            <managerId>100</managerId>
          </Employee>
          <Employee id="123">
            <firstName>Shanta</firstName>
            <lastName>Vollman</lastName>
            <email>SVOLLMAN</email>
            <phoneNo>650.123.4234</phoneNo>
            <hireDate>2005-10-10</hireDate>
            <jobId>ST_MAN</jobId>
            <salary>6500</salary>
            <managerId>100</managerId>
          </Employee>
          <Employee id="124">
            <firstName>Kevin</firstName>
            <lastName>Mourgos</lastName>
            <email>KMOURGOS</email>
            <phoneNo>650.123.5234</phoneNo>
            <hireDate>2007-11-16</hireDate>
            <jobId>ST_MAN</jobId>
            <salary>5800</salary>
            <managerId>100</managerId>
          </Employee>
          <Employee id="125">
            <firstName>Julia</firstName>
            <lastName>Nayer</lastName>
            <email>JNAYER</email>
            <phoneNo>650.124.1214</phoneNo>
            <hireDate>2005-07-16</hireDate>
            <jobId>ST_CLERK</jobId>
            <salary>3200</salary>
            <managerId>120</managerId>
          </Employee>
          <Employee id="126">
            <firstName>Irene</firstName>
            <lastName>Mikkilineni</lastName>
            <email>IMIKKILI</email>
            <phoneNo>650.124.1224</phoneNo>
            <hireDate>2006-09-28</hireDate>
            <jobId>ST_CLERK</jobId>
            <salary>2700</salary>
            <managerId>120</managerId>
          </Employee>
          <Employee id="127">
            <firstName>James</firstName>
            <lastName>Landry</lastName>
            <email>JLANDRY</email>
            <phoneNo>650.124.1334</phoneNo>
            <hireDate>2007-01-14</hireDate>
            <jobId>ST_CLERK</jobId>
            <salary>2400</salary>
            <managerId>120</managerId>
          </Employee>
          <Employee id="128">
            <firstName>Steven</firstName>
            <lastName>Markle</lastName>
            <email>SMARKLE</email>
            <phoneNo>650.124.1434</phoneNo>
            <hireDate>2008-03-08</hireDate>
            <jobId>ST_CLERK</jobId>
            <salary>2200</salary>
            <managerId>120</managerId>
          </Employee>
          <Employee id="129">
            <firstName>Laura</firstName>
            <lastName>Bissot</lastName>
            <email>LBISSOT</email>
            <phoneNo>650.124.5234</phoneNo>
            <hireDate>2005-08-20</hireDate>
            <jobId>ST_CLERK</jobId>
            <salary>3300</salary>
            <managerId>121</managerId>
          </Employee>
          <Employee id="130">
            <firstName>Mozhe</firstName>
            <lastName>Atkinson</lastName>
            <email>MATKINSO</email>
            <phoneNo>650.124.6234</phoneNo>
            <hireDate>2005-10-30</hireDate>
            <jobId>ST_CLERK</jobId>
            <salary>2800</salary>
            <managerId>121</managerId>
          </Employee>
          <Employee id="131">
            <firstName>James</firstName>
            <lastName>Marlow</lastName>
            <email>JAMRLOW</email>
            <phoneNo>650.124.7234</phoneNo>
            <hireDate>2005-02-16</hireDate>
            <jobId>ST_CLERK</jobId>
            <salary>2500</salary>
            <managerId>121</managerId>
          </Employee>
          <Employee id="132">
            <firstName>TJ</firstName>
            <lastName>Olson</lastName>
            <email>TJOLSON</email>
            <phoneNo>650.124.8234</phoneNo>
            <hireDate>2007-04-10</hireDate>
            <jobId>ST_CLERK</jobId>
            <salary>2100</salary>
            <managerId>121</managerId>
          </Employee>
          <Employee id="133">
            <firstName>Jason</firstName>
            <lastName>Mallin</lastName>
            <email>JMALLIN</email>
            <phoneNo>650.127.1934</phoneNo>
            <hireDate>2004-06-14</hireDate>
            <jobId>ST_CLERK</jobId>
            <salary>3300</salary>
            <managerId>122</managerId>
          </Employee>
          <Employee id="134">
            <firstName>Michael</firstName>
            <lastName>Rogers</lastName>
            <email>MROGERS</email>
            <phoneNo>650.127.1834</phoneNo>
            <hireDate>2006-08-26</hireDate>
            <jobId>ST_CLERK</jobId>
            <salary>2900</salary>
            <managerId>122</managerId>
          </Employee>
          <Employee id="135">
            <firstName>Ki</firstName>
            <lastName>Gee</lastName>
            <email>KGEE</email>
            <phoneNo>650.127.1734</phoneNo>
            <hireDate>2007-12-12</hireDate>
            <jobId>ST_CLERK</jobId>
            <salary>2400</salary>
            <managerId>122</managerId>
          </Employee>
          <Employee id="136">
            <firstName>Hazel</firstName>
            <lastName>Philtanker</lastName>
            <email>HPHILTAN</email>
            <phoneNo>650.127.1634</phoneNo>
            <hireDate>2008-02-06</hireDate>
            <jobId>ST_CLERK</jobId>
            <salary>2200</salary>
            <managerId>122</managerId>
          </Employee>
          <Employee id="137">
            <firstName>Renske</firstName>
            <lastName>Ladwig</lastName>
            <email>RLADWIG</email>
            <phoneNo>650.121.1234</phoneNo>
            <hireDate>2003-07-14</hireDate>
            <jobId>ST_CLERK</jobId>
            <salary>3600</salary>
            <managerId>123</managerId>
          </Employee>
          <Employee id="138">
            <firstName>Stephen</firstName>
            <lastName>Stiles</lastName>
            <email>SSTILES</email>
            <phoneNo>650.121.2034</phoneNo>
            <hireDate>2005-10-26</hireDate>
            <jobId>ST_CLERK</jobId>
            <salary>3200</salary>
            <managerId>123</managerId>
          </Employee>
          <Employee id="139">
            <firstName>John</firstName>
            <lastName>Seo</lastName>
            <email>JSEO</email>
            <phoneNo>650.121.2019</phoneNo>
            <hireDate>2006-02-12</hireDate>
            <jobId>ST_CLERK</jobId>
            <salary>2700</salary>
            <managerId>123</managerId>
          </Employee>
          <Employee id="140">
            <firstName>Joshua</firstName>
            <lastName>Patel</lastName>
            <email>JPATEL</email>
            <phoneNo>650.121.1834</phoneNo>
            <hireDate>2006-04-06</hireDate>
            <jobId>ST_CLERK</jobId>
            <salary>2500</salary>
            <managerId>123</managerId>
          </Employee>
          <Employee id="141">
            <firstName>Trenna</firstName>
            <lastName>Rajs</lastName>
            <email>TRAJS</email>
            <phoneNo>650.121.8009</phoneNo>
            <hireDate>2003-10-17</hireDate>
            <jobId>ST_CLERK</jobId>
            <salary>3500</salary>
            <managerId>124</managerId>
          </Employee>
          <Employee id="142">
            <firstName>Curtis</firstName>
            <lastName>Davies</lastName>
            <email>CDAVIES</email>
            <phoneNo>650.121.2994</phoneNo>
            <hireDate>2005-01-29</hireDate>
            <jobId>ST_CLERK</jobId>
            <salary>3100</salary>
            <managerId>124</managerId>
          </Employee>
          <Employee id="143">
            <firstName>Randall</firstName>
            <lastName>Matos</lastName>
            <email>RMATOS</email>
            <phoneNo>650.121.2874</phoneNo>
            <hireDate>2006-03-15</hireDate>
            <jobId>ST_CLERK</jobId>
            <salary>2600</salary>
            <managerId>124</managerId>
          </Employee>
          <Employee id="144">
            <firstName>Peter</firstName>
            <lastName>Vargas</lastName>
            <email>PVARGAS</email>
            <phoneNo>650.121.2004</phoneNo>
            <hireDate>2006-07-09</hireDate>
            <jobId>ST_CLERK</jobId>
            <salary>2500</salary>
            <managerId>124</managerId>
          </Employee>
          <Employee id="180">
            <firstName>Winston</firstName>
            <lastName>Taylor</lastName>
            <email>WTAYLOR</email>
            <phoneNo>650.507.9876</phoneNo>
            <hireDate>2006-01-24</hireDate>
            <jobId>SH_CLERK</jobId>
            <salary>3200</salary>
            <managerId>120</managerId>
          </Employee>
          <Employee id="181">
            <firstName>Jean</firstName>
            <lastName>Fleaur</lastName>
            <email>JFLEAUR</email>
            <phoneNo>650.507.9877</phoneNo>
            <hireDate>2006-02-23</hireDate>
            <jobId>SH_CLERK</jobId>
            <salary>3100</salary>
            <managerId>120</managerId>
          </Employee>
          <Employee id="182">
            <firstName>Martha</firstName>
            <lastName>Sullivan</lastName>
            <email>MSULLIVA</email>
            <phoneNo>650.507.9878</phoneNo>
            <hireDate>2007-06-21</hireDate>
            <jobId>SH_CLERK</jobId>
            <salary>2500</salary>
            <managerId>120</managerId>
          </Employee>
          <Employee id="183">
            <firstName>Girard</firstName>
            <lastName>Geoni</lastName>
            <email>GGEONI</email>
            <phoneNo>650.507.9879</phoneNo>
            <hireDate>2008-02-03</hireDate>
            <jobId>SH_CLERK</jobId>
            <salary>2800</salary>
            <managerId>120</managerId>
          </Employee>
          <Employee id="184">
            <firstName>Nandita</firstName>
            <lastName>Sarchand</lastName>
            <email>NSARCHAN</email>
            <phoneNo>650.509.1876</phoneNo>
            <hireDate>2004-01-27</hireDate>
            <jobId>SH_CLERK</jobId>
            <salary>4200</salary>
            <managerId>121</managerId>
          </Employee>
          <Employee id="185">
            <firstName>Alexis</firstName>
            <lastName>Bull</lastName>
            <email>ABULL</email>
            <phoneNo>650.509.2876</phoneNo>
            <hireDate>2005-02-20</hireDate>
            <jobId>SH_CLERK</jobId>
            <salary>4100</salary>
            <managerId>121</managerId>
          </Employee>
          <Employee id="186">
            <firstName>Julia</firstName>
            <lastName>Dellinger</lastName>
            <email>JDELLING</email>
            <phoneNo>650.509.3876</phoneNo>
            <hireDate>2006-06-24</hireDate>
            <jobId>SH_CLERK</jobId>
            <salary>3400</salary>
            <managerId>121</managerId>
          </Employee>
          <Employee id="187">
            <firstName>Anthony</firstName>
            <lastName>Cabrio</lastName>
            <email>ACABRIO</email>
            <phoneNo>650.509.4876</phoneNo>
            <hireDate>2007-02-07</hireDate>
            <jobId>SH_CLERK</jobId>
            <salary>3000</salary>
            <managerId>121</managerId>
          </Employee>
          <Employee id="188">
            <firstName>Kelly</firstName>
            <lastName>Chung</lastName>
            <email>KCHUNG</email>
            <phoneNo>650.505.1876</phoneNo>
            <hireDate>2005-06-14</hireDate>
            <jobId>SH_CLERK</jobId>
            <salary>3800</salary>
            <managerId>122</managerId>
          </Employee>
          <Employee id="189">
            <firstName>Jennifer</firstName>
            <lastName>Dilly</lastName>
            <email>JDILLY</email>
            <phoneNo>650.505.2876</phoneNo>
            <hireDate>2005-08-13</hireDate>
            <jobId>SH_CLERK</jobId>
            <salary>3600</salary>
            <managerId>122</managerId>
          </Employee>
          <Employee id="190">
            <firstName>Timothy</firstName>
            <lastName>Gates</lastName>
            <email>TGATES</email>
            <phoneNo>650.505.3876</phoneNo>
            <hireDate>2006-07-11</hireDate>
            <jobId>SH_CLERK</jobId>
            <salary>2900</salary>
            <managerId>122</managerId>
          </Employee>
          <Employee id="191">
            <firstName>Randall</firstName>
            <lastName>Perkins</lastName>
            <email>RPERKINS</email>
            <phoneNo>650.505.4876</phoneNo>
            <hireDate>2007-12-19</hireDate>
            <jobId>SH_CLERK</jobId>
            <salary>2500</salary>
            <managerId>122</managerId>
          </Employee>
          <Employee id="192">
            <firstName>Sarah</firstName>
            <lastName>Bell</lastName>
            <email>SBELL</email>
            <phoneNo>650.501.1876</phoneNo>
            <hireDate>2004-02-04</hireDate>
            <jobId>SH_CLERK</jobId>
            <salary>4000</salary>
            <managerId>123</managerId>
          </Employee>
          <Employee id="193">
            <firstName>Britney</firstName>
            <lastName>Everett</lastName>
            <email>BEVERETT</email>
            <phoneNo>650.501.2876</phoneNo>
            <hireDate>2005-03-03</hireDate>
            <jobId>SH_CLERK</jobId>
            <salary>3900</salary>
            <managerId>123</managerId>
          </Employee>
          <Employee id="194">
            <firstName>Samuel</firstName>
            <lastName>McCain</lastName>
            <email>SMCCAIN</email>
            <phoneNo>650.501.3876</phoneNo>
            <hireDate>2006-07-01</hireDate>
            <jobId>SH_CLERK</jobId>
            <salary>3200</salary>
            <managerId>123</managerId>
          </Employee>
          <Employee id="195">
            <firstName>Vance</firstName>
            <lastName>Jones</lastName>
            <email>VJONES</email>
            <phoneNo>650.501.4876</phoneNo>
            <hireDate>2007-03-17</hireDate>
            <jobId>SH_CLERK</jobId>
            <salary>2800</salary>
            <managerId>123</managerId>
          </Employee>
          <Employee id="196">
            <firstName>Alana</firstName>
            <lastName>Walsh</lastName>
            <email>AWALSH</email>
            <phoneNo>650.507.9811</phoneNo>
            <hireDate>2006-04-24</hireDate>
            <jobId>SH_CLERK</jobId>
            <salary>3100</salary>
            <managerId>124</managerId>
          </Employee>
          <Employee id="197">
            <firstName>Kevin</firstName>
            <lastName>Feeney</lastName>
            <email>KFEENEY</email>
            <phoneNo>650.507.9822</phoneNo>
            <hireDate>2006-05-23</hireDate>
            <jobId>SH_CLERK</jobId>
            <salary>3000</salary>
            <managerId>124</managerId>
          </Employee>
          <Employee id="198">
            <firstName>Donald</firstName>
            <lastName>OConnell</lastName>
            <email>DOCONNEL</email>
            <phoneNo>650.507.9833</phoneNo>
            <hireDate>2007-06-21</hireDate>
            <jobId>SH_CLERK</jobId>
            <salary>2600</salary>
            <managerId>124</managerId>
          </Employee>
          <Employee id="199">
            <firstName>Douglas</firstName>
            <lastName>Grant</lastName>
            <email>DGRANT</email>
            <phoneNo>650.507.9844</phoneNo>
            <hireDate>2008-01-13</hireDate>
            <jobId>SH_CLERK</jobId>
            <salary>2600</salary>
            <managerId>124</managerId>
          </Employee>
        </Employees>
      </Department>
    </Departments>
  </Location>
  <Location id="1600">
    <address>2007 Zagora St</address>
    <postalCode>50090</postalCode>
    <city>South Brunswick</city>
    <stateProvince>New Jersey</stateProvince>
    <countryId>US</countryId>
    <Departments/>
  </Location>
  <Location id="1700">
    <address>2004 Charade Rd</address>
    <postalCode>98199</postalCode>
    <city>Seattle</city>
    <stateProvince>Washington</stateProvince>
    <countryId>US</countryId>
    <Departments>
      <Department id="10">
        <name>Administration</name>
        <managerId>200</managerId>
        <Employees>
          <Employee id="200">
            <firstName>Jennifer</firstName>
            <lastName>Whalen</lastName>
            <email>JWHALEN</email>
            <phoneNo>515.123.4444</phoneNo>
            <hireDate>2003-09-17</hireDate>
            <jobId>AD_ASST</jobId>
            <salary>4400</salary>
            <managerId>101</managerId>
          </Employee>
        </Employees>
      </Department>
      <Department id="30">
        <name>Purchasing</name>
        <managerId>114</managerId>
        <Employees>
          <Employee id="114">
            <firstName>Den</firstName>
            <lastName>Raphaely</lastName>
            <email>DRAPHEAL</email>
            <phoneNo>515.127.4561</phoneNo>
            <hireDate>2002-12-07</hireDate>
            <jobId>PU_MAN</jobId>
            <salary>11000</salary>
            <managerId>100</managerId>
          </Employee>
          <Employee id="115">
            <firstName>Alexander</firstName>
            <lastName>Khoo</lastName>
            <email>AKHOO</email>
            <phoneNo>515.127.4562</phoneNo>
            <hireDate>2003-05-18</hireDate>
            <jobId>PU_CLERK</jobId>
            <salary>3100</salary>
            <managerId>114</managerId>
          </Employee>
          <Employee id="116">
            <firstName>Shelli</firstName>
            <lastName>Baida</lastName>
            <email>SBAIDA</email>
            <phoneNo>515.127.4563</phoneNo>
            <hireDate>2005-12-24</hireDate>
            <jobId>PU_CLERK</jobId>
            <salary>2900</salary>
            <managerId>114</managerId>
          </Employee>
          <Employee id="117">
            <firstName>Sigal</firstName>
            <lastName>Tobias</lastName>
            <email>STOBIAS</email>
            <phoneNo>515.127.4564</phoneNo>
            <hireDate>2005-07-24</hireDate>
            <jobId>PU_CLERK</jobId>
            <salary>2800</salary>
            <managerId>114</managerId>
          </Employee>
          <Employee id="118">
            <firstName>Guy</firstName>
            <lastName>Himuro</lastName>
            <email>GHIMURO</email>
            <phoneNo>515.127.4565</phoneNo>
            <hireDate>2006-11-15</hireDate>
            <jobId>PU_CLERK</jobId>
            <salary>2600</salary>
            <managerId>114</managerId>
          </Employee>
          <Employee id="119">
            <firstName>Karen</firstName>
            <lastName>Colmenares</lastName>
            <email>KCOLMENA</email>
            <phoneNo>515.127.4566</phoneNo>
            <hireDate>2007-08-10</hireDate>
            <jobId>PU_CLERK</jobId>
            <salary>2500</salary>
            <managerId>114</managerId>
          </Employee>
        </Employees>
      </Department>
      <Department id="90">
        <name>Executive</name>
        <managerId>100</managerId>
        <Employees>
          <Employee id="100">
            <firstName>Steven</firstName>
            <lastName>King</lastName>
            <email>SKING</email>
            <phoneNo>515.123.4567</phoneNo>
            <hireDate>2003-06-17</hireDate>
            <jobId>AD_PRES</jobId>
            <salary>24000</salary>
          </Employee>
          <Employee id="101">
            <firstName>Neena</firstName>
            <lastName>Kochhar</lastName>
            <email>NKOCHHAR</email>
            <phoneNo>515.123.4568</phoneNo>
            <hireDate>2005-09-21</hireDate>
            <jobId>AD_VP</jobId>
            <salary>17000</salary>
            <managerId>100</managerId>
          </Employee>
          <Employee id="102">
            <firstName>Lex</firstName>
            <lastName>De Haan</lastName>
            <email>LDEHAAN</email>
            <phoneNo>515.123.4569</phoneNo>
            <hireDate>2001-01-13</hireDate>
            <jobId>AD_VP</jobId>
            <salary>17000</salary>
            <managerId>100</managerId>
          </Employee>
        </Employees>
      </Department>
      <Department id="100">
        <name>Finance</name>
        <managerId>108</managerId>
        <Employees>
          <Employee id="108">
            <firstName>Nancy</firstName>
            <lastName>Greenberg</lastName>
            <email>NGREENBE</email>
            <phoneNo>515.124.4569</phoneNo>
            <hireDate>2002-08-17</hireDate>
            <jobId>FI_MGR</jobId>
            <salary>12008</salary>
            <managerId>101</managerId>
          </Employee>
          <Employee id="109">
            <firstName>Daniel</firstName>
            <lastName>Faviet</lastName>
            <email>DFAVIET</email>
            <phoneNo>515.124.4169</phoneNo>
            <hireDate>2002-08-16</hireDate>
            <jobId>FI_ACCOUNT</jobId>
            <salary>9000</salary>
            <managerId>108</managerId>
          </Employee>
          <Employee id="110">
            <firstName>John</firstName>
            <lastName>Chen</lastName>
            <email>JCHEN</email>
            <phoneNo>515.124.4269</phoneNo>
            <hireDate>2005-09-28</hireDate>
            <jobId>FI_ACCOUNT</jobId>
            <salary>8200</salary>
            <managerId>108</managerId>
          </Employee>
          <Employee id="111">
            <firstName>Ismael</firstName>
            <lastName>Sciarra</lastName>
            <email>ISCIARRA</email>
            <phoneNo>515.124.4369</phoneNo>
            <hireDate>2005-09-30</hireDate>
            <jobId>FI_ACCOUNT</jobId>
            <salary>7700</salary>
            <managerId>108</managerId>
          </Employee>
          <Employee id="112">
            <firstName>Jose Manuel</firstName>
            <lastName>Urman</lastName>
            <email>JMURMAN</email>
            <phoneNo>515.124.4469</phoneNo>
            <hireDate>2006-03-07</hireDate>
            <jobId>FI_ACCOUNT</jobId>
            <salary>7800</salary>
            <managerId>108</managerId>
          </Employee>
          <Employee id="113">
            <firstName>Luis</firstName>
            <lastName>Popp</lastName>
            <email>LPOPP</email>
            <phoneNo>515.124.4567</phoneNo>
            <hireDate>2007-12-07</hireDate>
            <jobId>FI_ACCOUNT</jobId>
            <salary>6900</salary>
            <managerId>108</managerId>
          </Employee>
        </Employees>
      </Department>
      <Department id="110">
        <name>Accounting</name>
        <managerId>205</managerId>
        <Employees>
          <Employee id="205">
            <firstName>Shelley</firstName>
            <lastName>Higgins</lastName>
            <email>SHIGGINS</email>
            <phoneNo>515.123.8080</phoneNo>
            <hireDate>2002-06-07</hireDate>
            <jobId>AC_MGR</jobId>
            <salary>12008</salary>
            <managerId>101</managerId>
          </Employee>
          <Employee id="206">
            <firstName>William</firstName>
            <lastName>Gietz</lastName>
            <email>WGIETZ</email>
            <phoneNo>515.123.8181</phoneNo>
            <hireDate>2002-06-07</hireDate>
            <jobId>AC_ACCOUNT</jobId>
            <salary>8300</salary>
            <managerId>205</managerId>
          </Employee>
        </Employees>
      </Department>
      <Department id="120">
        <name>Treasury</name>
        <Employees/>
      </Department>
      <Department id="130">
        <name>Corporate Tax</name>
        <Employees/>
      </Department>
      <Department id="140">
        <name>Control And Credit</name>
        <Employees/>
      </Department>
      <Department id="150">
        <name>Shareholder Services</name>
        <Employees/>
      </Department>
      <Department id="160">
        <name>Benefits</name>
        <Employees/>
      </Department>
      <Department id="170">
        <name>Manufacturing</name>
        <Employees/>
      </Department>
      <Department id="180">
        <name>Construction</name>
        <Employees/>
      </Department>
      <Department id="190">
        <name>Contracting</name>
        <Employees/>
      </Department>
      <Department id="200">
        <name>Operations</name>
        <Employees/>
      </Department>
      <Department id="210">
        <name>IT Support</name>
        <Employees/>
      </Department>
      <Department id="220">
        <name>NOC</name>
        <Employees/>
      </Department>
      <Department id="230">
        <name>IT Helpdesk</name>
        <Employees/>
      </Department>
      <Department id="240">
        <name>Government Sales</name>
        <Employees/>
      </Department>
      <Department id="250">
        <name>Retail Sales</name>
        <Employees/>
      </Department>
      <Department id="260">
        <name>Recruiting</name>
        <Employees/>
      </Department>
      <Department id="270">
        <name>Payroll</name>
        <Employees/>
      </Department>
    </Departments>
  </Location>
  <Location id="1800">
    <address>147 Spadina Ave</address>
    <postalCode>M5V 2L7</postalCode>
    <city>Toronto</city>
    <stateProvince>Ontario</stateProvince>
    <countryId>CA</countryId>
    <Departments>
      <Department id="20">
        <name>Marketing</name>
        <managerId>201</managerId>
        <Employees>
          <Employee id="201">
            <firstName>Michael</firstName>
            <lastName>Hartstein</lastName>
            <email>MHARTSTE</email>
            <phoneNo>515.123.5555</phoneNo>
            <hireDate>2004-02-17</hireDate>
            <jobId>MK_MAN</jobId>
            <salary>13000</salary>
            <managerId>100</managerId>
          </Employee>
          <Employee id="202">
            <firstName>Pat</firstName>
            <lastName>Fay</lastName>
            <email>PFAY</email>
            <phoneNo>603.123.6666</phoneNo>
            <hireDate>2005-08-17</hireDate>
            <jobId>MK_REP</jobId>
            <salary>6000</salary>
            <managerId>201</managerId>
          </Employee>
        </Employees>
      </Department>
    </Departments>
  </Location>
  <Location id="1900">
    <address>6092 Boxwood St</address>
    <postalCode>YSW 9T2</postalCode>
    <city>Whitehorse</city>
    <stateProvince>Yukon</stateProvince>
    <countryId>CA</countryId>
    <Departments/>
  </Location>
  <Location id="2000">
    <address>40-5-12 Laogianggen</address>
    <postalCode>190518</postalCode>
    <city>Beijing</city>
    <countryId>CN</countryId>
    <Departments/>
  </Location>
  <Location id="2100">
    <address>1298 Vileparle (E)</address>
    <postalCode>490231</postalCode>
    <city>Bombay</city>
    <stateProvince>Maharashtra</stateProvince>
    <countryId>IN</countryId>
    <Departments/>
  </Location>
  <Location id="2200">
    <address>12-98 Victoria Street</address>
    <postalCode>2901</postalCode>
    <city>Sydney</city>
    <stateProvince>New South Wales</stateProvince>
    <countryId>AU</countryId>
    <Departments/>
  </Location>
  <Location id="2300">
    <address>198 Clementi North</address>
    <postalCode>540198</postalCode>
    <city>Singapore</city>
    <countryId>SG</countryId>
    <Departments/>
  </Location>
  <Location id="2400">
    <address>8204 Arthur St</address>
    <city>London</city>
    <countryId>UK</countryId>
    <Departments>
      <Department id="40">
        <name>Human Resources</name>
        <managerId>203</managerId>
        <Employees>
          <Employee id="203">
            <firstName>Susan</firstName>
            <lastName>Mavris</lastName>
            <email>SMAVRIS</email>
            <phoneNo>515.123.7777</phoneNo>
            <hireDate>2002-06-07</hireDate>
            <jobId>HR_REP</jobId>
            <salary>6500</salary>
            <managerId>101</managerId>
          </Employee>
        </Employees>
      </Department>
    </Departments>
  </Location>
  <Location id="2500">
    <address>Magdalen Centre, The Oxford Science Park</address>
    <postalCode>OX9 9ZB</postalCode>
    <city>Oxford</city>
    <stateProvince>Oxford</stateProvince>
    <countryId>UK</countryId>
    <Departments>
      <Department id="80">
        <name>Sales</name>
        <managerId>145</managerId>
        <Employees>
          <Employee id="145">
            <firstName>John</firstName>
            <lastName>Russell</lastName>
            <email>JRUSSEL</email>
            <phoneNo>011.44.1344.429268</phoneNo>
            <hireDate>2004-10-01</hireDate>
            <jobId>SA_MAN</jobId>
            <salary>14000</salary>
            <commPct>,4</commPct>
            <managerId>100</managerId>
          </Employee>
          <Employee id="146">
            <firstName>Karen</firstName>
            <lastName>Partners</lastName>
            <email>KPARTNER</email>
            <phoneNo>011.44.1344.467268</phoneNo>
            <hireDate>2005-01-05</hireDate>
            <jobId>SA_MAN</jobId>
            <salary>13500</salary>
            <commPct>,3</commPct>
            <managerId>100</managerId>
          </Employee>
          <Employee id="147">
            <firstName>Alberto</firstName>
            <lastName>Errazuriz</lastName>
            <email>AERRAZUR</email>
            <phoneNo>011.44.1344.429278</phoneNo>
            <hireDate>2005-03-10</hireDate>
            <jobId>SA_MAN</jobId>
            <salary>12000</salary>
            <commPct>,3</commPct>
            <managerId>100</managerId>
          </Employee>
          <Employee id="148">
            <firstName>Gerald</firstName>
            <lastName>Cambrault</lastName>
            <email>GCAMBRAU</email>
            <phoneNo>011.44.1344.619268</phoneNo>
            <hireDate>2007-10-15</hireDate>
            <jobId>SA_MAN</jobId>
            <salary>11000</salary>
            <commPct>,3</commPct>
            <managerId>100</managerId>
          </Employee>
          <Employee id="149">
            <firstName>Eleni</firstName>
            <lastName>Zlotkey</lastName>
            <email>EZLOTKEY</email>
            <phoneNo>011.44.1344.429018</phoneNo>
            <hireDate>2008-01-29</hireDate>
            <jobId>SA_MAN</jobId>
            <salary>10500</salary>
            <commPct>,2</commPct>
            <managerId>100</managerId>
          </Employee>
          <Employee id="150">
            <firstName>Peter</firstName>
            <lastName>Tucker</lastName>
            <email>PTUCKER</email>
            <phoneNo>011.44.1344.129268</phoneNo>
            <hireDate>2005-01-30</hireDate>
            <jobId>SA_REP</jobId>
            <salary>10000</salary>
            <commPct>,3</commPct>
            <managerId>145</managerId>
          </Employee>
          <Employee id="151">
            <firstName>David</firstName>
            <lastName>Bernstein</lastName>
            <email>DBERNSTE</email>
            <phoneNo>011.44.1344.345268</phoneNo>
            <hireDate>2005-03-24</hireDate>
            <jobId>SA_REP</jobId>
            <salary>9500</salary>
            <commPct>,25</commPct>
            <managerId>145</managerId>
          </Employee>
          <Employee id="152">
            <firstName>Peter</firstName>
            <lastName>Hall</lastName>
            <email>PHALL</email>
            <phoneNo>011.44.1344.478968</phoneNo>
            <hireDate>2005-08-20</hireDate>
            <jobId>SA_REP</jobId>
            <salary>9000</salary>
            <commPct>,25</commPct>
            <managerId>145</managerId>
          </Employee>
          <Employee id="153">
            <firstName>Christopher</firstName>
            <lastName>Olsen</lastName>
            <email>COLSEN</email>
            <phoneNo>011.44.1344.498718</phoneNo>
            <hireDate>2006-03-30</hireDate>
            <jobId>SA_REP</jobId>
            <salary>8000</salary>
            <commPct>,2</commPct>
            <managerId>145</managerId>
          </Employee>
          <Employee id="154">
            <firstName>Nanette</firstName>
            <lastName>Cambrault</lastName>
            <email>NCAMBRAU</email>
            <phoneNo>011.44.1344.987668</phoneNo>
            <hireDate>2006-12-09</hireDate>
            <jobId>SA_REP</jobId>
            <salary>7500</salary>
            <commPct>,2</commPct>
            <managerId>145</managerId>
          </Employee>
          <Employee id="155">
            <firstName>Oliver</firstName>
            <lastName>Tuvault</lastName>
            <email>OTUVAULT</email>
            <phoneNo>011.44.1344.486508</phoneNo>
            <hireDate>2007-11-23</hireDate>
            <jobId>SA_REP</jobId>
            <salary>7000</salary>
            <commPct>,15</commPct>
            <managerId>145</managerId>
          </Employee>
          <Employee id="156">
            <firstName>Janette</firstName>
            <lastName>King</lastName>
            <email>JKING</email>
            <phoneNo>011.44.1345.429268</phoneNo>
            <hireDate>2004-01-30</hireDate>
            <jobId>SA_REP</jobId>
            <salary>10000</salary>
            <commPct>,35</commPct>
            <managerId>146</managerId>
          </Employee>
          <Employee id="157">
            <firstName>Patrick</firstName>
            <lastName>Sully</lastName>
            <email>PSULLY</email>
            <phoneNo>011.44.1345.929268</phoneNo>
            <hireDate>2004-03-04</hireDate>
            <jobId>SA_REP</jobId>
            <salary>9500</salary>
            <commPct>,35</commPct>
            <managerId>146</managerId>
          </Employee>
          <Employee id="158">
            <firstName>Allan</firstName>
            <lastName>McEwen</lastName>
            <email>AMCEWEN</email>
            <phoneNo>011.44.1345.829268</phoneNo>
            <hireDate>2004-08-01</hireDate>
            <jobId>SA_REP</jobId>
            <salary>9000</salary>
            <commPct>,35</commPct>
            <managerId>146</managerId>
          </Employee>
          <Employee id="159">
            <firstName>Lindsey</firstName>
            <lastName>Smith</lastName>
            <email>LSMITH</email>
            <phoneNo>011.44.1345.729268</phoneNo>
            <hireDate>2005-03-10</hireDate>
            <jobId>SA_REP</jobId>
            <salary>8000</salary>
            <commPct>,3</commPct>
            <managerId>146</managerId>
          </Employee>
          <Employee id="160">
            <firstName>Louise</firstName>
            <lastName>Doran</lastName>
            <email>LDORAN</email>
            <phoneNo>011.44.1345.629268</phoneNo>
            <hireDate>2005-12-15</hireDate>
            <jobId>SA_REP</jobId>
            <salary>7500</salary>
            <commPct>,3</commPct>
            <managerId>146</managerId>
          </Employee>
          <Employee id="161">
            <firstName>Sarath</firstName>
            <lastName>Sewall</lastName>
            <email>SSEWALL</email>
            <phoneNo>011.44.1345.529268</phoneNo>
            <hireDate>2006-11-03</hireDate>
            <jobId>SA_REP</jobId>
            <salary>7000</salary>
            <commPct>,25</commPct>
            <managerId>146</managerId>
          </Employee>
          <Employee id="162">
            <firstName>Clara</firstName>
            <lastName>Vishney</lastName>
            <email>CVISHNEY</email>
            <phoneNo>011.44.1346.129268</phoneNo>
            <hireDate>2005-11-11</hireDate>
            <jobId>SA_REP</jobId>
            <salary>10500</salary>
            <commPct>,25</commPct>
            <managerId>147</managerId>
          </Employee>
          <Employee id="163">
            <firstName>Danielle</firstName>
            <lastName>Greene</lastName>
            <email>DGREENE</email>
            <phoneNo>011.44.1346.229268</phoneNo>
            <hireDate>2007-03-19</hireDate>
            <jobId>SA_REP</jobId>
            <salary>9500</salary>
            <commPct>,15</commPct>
            <managerId>147</managerId>
          </Employee>
          <Employee id="164">
            <firstName>Mattea</firstName>
            <lastName>Marvins</lastName>
            <email>MMARVINS</email>
            <phoneNo>011.44.1346.329268</phoneNo>
            <hireDate>2008-01-24</hireDate>
            <jobId>SA_REP</jobId>
            <salary>7200</salary>
            <commPct>,1</commPct>
            <managerId>147</managerId>
          </Employee>
          <Employee id="165">
            <firstName>David</firstName>
            <lastName>Lee</lastName>
            <email>DLEE</email>
            <phoneNo>011.44.1346.529268</phoneNo>
            <hireDate>2008-02-23</hireDate>
            <jobId>SA_REP</jobId>
            <salary>6800</salary>
            <commPct>,1</commPct>
            <managerId>147</managerId>
          </Employee>
          <Employee id="166">
            <firstName>Sundar</firstName>
            <lastName>Ande</lastName>
            <email>SANDE</email>
            <phoneNo>011.44.1346.629268</phoneNo>
            <hireDate>2008-03-24</hireDate>
            <jobId>SA_REP</jobId>
            <salary>6400</salary>
            <commPct>,1</commPct>
            <managerId>147</managerId>
          </Employee>
          <Employee id="167">
            <firstName>Amit</firstName>
            <lastName>Banda</lastName>
            <email>ABANDA</email>
            <phoneNo>011.44.1346.729268</phoneNo>
            <hireDate>2008-04-21</hireDate>
            <jobId>SA_REP</jobId>
            <salary>6200</salary>
            <commPct>,1</commPct>
            <managerId>147</managerId>
          </Employee>
          <Employee id="168">
            <firstName>Lisa</firstName>
            <lastName>Ozer</lastName>
            <email>LOZER</email>
            <phoneNo>011.44.1343.929268</phoneNo>
            <hireDate>2005-03-11</hireDate>
            <jobId>SA_REP</jobId>
            <salary>11500</salary>
            <commPct>,25</commPct>
            <managerId>148</managerId>
          </Employee>
          <Employee id="169">
            <firstName>Harrison</firstName>
            <lastName>Bloom</lastName>
            <email>HBLOOM</email>
            <phoneNo>011.44.1343.829268</phoneNo>
            <hireDate>2006-03-23</hireDate>
            <jobId>SA_REP</jobId>
            <salary>10000</salary>
            <commPct>,2</commPct>
            <managerId>148</managerId>
          </Employee>
          <Employee id="170">
            <firstName>Tayler</firstName>
            <lastName>Fox</lastName>
            <email>TFOX</email>
            <phoneNo>011.44.1343.729268</phoneNo>
            <hireDate>2006-01-24</hireDate>
            <jobId>SA_REP</jobId>
            <salary>9600</salary>
            <commPct>,2</commPct>
            <managerId>148</managerId>
          </Employee>
          <Employee id="171">
            <firstName>William</firstName>
            <lastName>Smith</lastName>
            <email>WSMITH</email>
            <phoneNo>011.44.1343.629268</phoneNo>
            <hireDate>2007-02-23</hireDate>
            <jobId>SA_REP</jobId>
            <salary>7400</salary>
            <commPct>,15</commPct>
            <managerId>148</managerId>
          </Employee>
          <Employee id="172">
            <firstName>Elizabeth</firstName>
            <lastName>Bates</lastName>
            <email>EBATES</email>
            <phoneNo>011.44.1343.529268</phoneNo>
            <hireDate>2007-03-24</hireDate>
            <jobId>SA_REP</jobId>
            <salary>7300</salary>
            <commPct>,15</commPct>
            <managerId>148</managerId>
          </Employee>
          <Employee id="173">
            <firstName>Sundita</firstName>
            <lastName>Kumar</lastName>
            <email>SKUMAR</email>
            <phoneNo>011.44.1343.329268</phoneNo>
            <hireDate>2008-04-21</hireDate>
            <jobId>SA_REP</jobId>
            <salary>6100</salary>
            <commPct>,1</commPct>
            <managerId>148</managerId>
          </Employee>
          <Employee id="174">
            <firstName>Ellen</firstName>
            <lastName>Abel</lastName>
            <email>EABEL</email>
            <phoneNo>011.44.1644.429267</phoneNo>
            <hireDate>2004-05-11</hireDate>
            <jobId>SA_REP</jobId>
            <salary>11000</salary>
            <commPct>,3</commPct>
            <managerId>149</managerId>
          </Employee>
          <Employee id="175">
            <firstName>Alyssa</firstName>
            <lastName>Hutton</lastName>
            <email>AHUTTON</email>
            <phoneNo>011.44.1644.429266</phoneNo>
            <hireDate>2005-03-19</hireDate>
            <jobId>SA_REP</jobId>
            <salary>8800</salary>
            <commPct>,25</commPct>
            <managerId>149</managerId>
          </Employee>
          <Employee id="176">
            <firstName>Jonathon</firstName>
            <lastName>Taylor</lastName>
            <email>JTAYLOR</email>
            <phoneNo>011.44.1644.429265</phoneNo>
            <hireDate>2006-03-24</hireDate>
            <jobId>SA_REP</jobId>
            <salary>8600</salary>
            <commPct>,2</commPct>
            <managerId>149</managerId>
          </Employee>
          <Employee id="177">
            <firstName>Jack</firstName>
            <lastName>Livingston</lastName>
            <email>JLIVINGS</email>
            <phoneNo>011.44.1644.429264</phoneNo>
            <hireDate>2006-04-23</hireDate>
            <jobId>SA_REP</jobId>
            <salary>8400</salary>
            <commPct>,2</commPct>
            <managerId>149</managerId>
          </Employee>
          <Employee id="179">
            <firstName>Charles</firstName>
            <lastName>Johnson</lastName>
            <email>CJOHNSON</email>
            <phoneNo>011.44.1644.429262</phoneNo>
            <hireDate>2008-01-04</hireDate>
            <jobId>SA_REP</jobId>
            <salary>6200</salary>
            <commPct>,1</commPct>
            <managerId>149</managerId>
          </Employee>
        </Employees>
      </Department>
    </Departments>
  </Location>
  <Location id="2600">
    <address>9702 Chester Road</address>
    <postalCode>09629850293</postalCode>
    <city>Stretford</city>
    <stateProvince>Manchester</stateProvince>
    <countryId>UK</countryId>
    <Departments/>
  </Location>
  <Location id="2700">
    <address>Schwanthalerstr. 7031</address>
    <postalCode>80925</postalCode>
    <city>Munich</city>
    <stateProvince>Bavaria</stateProvince>
    <countryId>DE</countryId>
    <Departments>
      <Department id="70">
        <name>Public Relations</name>
        <managerId>204</managerId>
        <Employees>
          <Employee id="204">
            <firstName>Hermann</firstName>
            <lastName>Baer</lastName>
            <email>HBAER</email>
            <phoneNo>515.123.8888</phoneNo>
            <hireDate>2002-06-07</hireDate>
            <jobId>PR_REP</jobId>
            <salary>10000</salary>
            <managerId>101</managerId>
          </Employee>
        </Employees>
      </Department>
    </Departments>
  </Location>
  <Location id="2800">
    <address>Rua Frei Caneca 1360 </address>
    <postalCode>01307-002</postalCode>
    <city>Sao Paulo</city>
    <stateProvince>Sao Paulo</stateProvince>
    <countryId>BR</countryId>
    <Departments/>
  </Location>
  <Location id="2900">
    <address>20 Rue des Corps-Saints</address>
    <postalCode>1730</postalCode>
    <city>Geneva</city>
    <stateProvince>Geneve</stateProvince>
    <countryId>CH</countryId>
    <Departments/>
  </Location>
  <Location id="3000">
    <address>Murtenstrasse 921</address>
    <postalCode>3095</postalCode>
    <city>Bern</city>
    <stateProvince>BE</stateProvince>
    <countryId>CH</countryId>
    <Departments/>
  </Location>
  <Location id="3100">
    <address>Pieter Breughelstraat 837</address>
    <postalCode>3029SK</postalCode>
    <city>Utrecht</city>
    <stateProvince>Utrecht</stateProvince>
    <countryId>NL</countryId>
    <Departments/>
  </Location>
  <Location id="3200">
    <address>Mariano Escobedo 9991</address>
    <postalCode>11932</postalCode>
    <city>Mexico City</city>
    <stateProvince>Distrito Federal,</stateProvince>
    <countryId>MX</countryId>
    <Departments/>
  </Location>
</Locations>

 

The idea is to load Location data into the LOCATIONS table, Department data into DEPARTMENTS and of course Employee data into EMPLOYEES table.

That can be achieved with a multitable INSERT statement from a flattened resultset built out of three XMLTables :

INSERT ALL
  WHEN ( employee_rno = 1 OR employee_rno IS NULL )
   AND ( department_rno = 1 OR department_rno IS NULL )
  THEN INTO locations (location_id, street_address, postal_code, city, state_province, country_id)
               VALUES (location_id, street_address, postal_code, city, state_province, country_id)
  WHEN ( employee_rno = 1 OR employee_rno IS NULL ) 
   AND department_rno IS NOT NULL
  THEN INTO departments (department_id, department_name, manager_id, location_id)
                 VALUES (department_id, department_name, dept_manager_id, location_id)
  WHEN employee_rno IS NOT NULL 
  THEN INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
               VALUES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
SELECT l.location_id
     , l.street_address
     , l.postal_code
     , l.city
     , l.state_province
     , l.country_id

     , d.department_id
     , d.department_name
     , d.dept_manager_id
     , d.department_rno
     
     , e.employee_id
     , e.first_name
     , e.last_name
     , e.email
     , e.phone_number
     , e.hire_date
     , e.job_id
     , e.salary
     , e.commission_pct
     , e.manager_id
     , e.employee_rno
FROM tmp_xml t
   , XMLTable('/Locations/Location'
       passing t.object_value
       columns 
         location_rno   for ordinality
       , location_id    number(4)    path '@id'
       , street_address varchar2(40) path 'address'
       , postal_code    varchar2(12) path 'postalCode'
       , city           varchar2(30) path 'city'
       , state_province varchar2(25) path 'stateProvince'
       , country_id     varchar2(2)  path 'countryId'
       , departments    xmltype      path 'Departments'
     ) l
   , XMLTable('/Departments/Department'
       passing l.departments
       columns
         department_rno  for ordinality
       , department_id   number(4)    path '@id'  
       , department_name varchar2(30) path 'name'
       , dept_manager_id number(6)    path 'managerId'
       , employees       xmltype      path 'Employees'
     ) (+) d
   , XMLTable('/Employees/Employee'
       passing d.employees
       columns
         employee_rno   for ordinality
       , employee_id    number(6)    path '@id'
       , first_name     varchar2(20) path 'firstName'
       , last_name      varchar2(25) path 'lastName'
       , email          varchar2(25) path 'email'
       , phone_number   varchar2(20) path 'phoneNo'
       , hire_date      date         path 'hireDate'
       , job_id         varchar2(10) path 'jobId'
       , salary         number(8,2)  path 'salary'
       , commission_pct number(2,2)  path 'commPct'
       , manager_id     number(6)    path 'managerId'
     ) (+) e
;

TMP_XML is a binary XMLType table where I first loaded the XML document in order to benefit from streaming evaluation.
Combined with the multitable INSERT, we have an efficient method to load multiple tables with a single pass on the data.
In particular, that’s the technique used internally by Oracle to build the content of the underlying relational table(s) supporting a structured XML index.

Advertisements
Categories: HowTo, Miscellaneous Tags: ,
  1. JD Singh
    June 27, 2012 at 10:22

    Hi Odie. This is great. I love your articles and the way you have been sharing your knowledge. I have a typicle requirement related to the above example. What if the XML that I get is not consistent everytime? What I mean here is that the 3rd party system only sends me the XML file with tags having values, where there is no value or is null, it just omits the tag from the XML file.
    Example, let’s consider the excerpt from your sample file above:
    Samuel
    McCain
    SMCCAIN
    650.501.3876
    2006-07-01

    If the email and phoneNo does not exist on the 3rd party systemfor an employee, the record for that empployee will come like below:
    Samuel
    McCain
    2006-07-01

    Whereas for other employees, if the same data exist, it will come. Hence the output is not consistent.

    Can you help me show some direction how to handle this dynamically.

    Thanks
    JD

    • June 27, 2012 at 13:24

      Hi JD,
      Actually, there’s nothing specific to do in this case. Missing elements will simply give NULL columns in the result set. Following up on your example, you’ll get NULL values in e.email and e.phone_number.
      Do you expect something else?

      • JD Singh
        June 28, 2012 at 03:20

        Yes Marc. I am expecting these nodes not to be coming at all if they have blank or null values. Can I use XMLExists to check if the node exists or not? Also I will have some 50 such nodes in the XML … is there some efficient way so i dont have to write XMLExists for each node?

  2. JD Singh
    June 28, 2012 at 11:12

    Let me explain my requirement in detail. I have a 3rd Party System that will send me an XML file, which I have to load into Oracle tables. Even though the XML will follow schema definition as per an XSD, the XML file will only have tags for the columns which have values. This makes it dynamically scanning/parsing the file for available tags and updating the table only for available tags, and the rest of the columns are defaulted to NULL values. My question is, is it possible in the first place, and if yes, then how?
    Thanks in advance Marc.

    • June 28, 2012 at 13:50

      Sorry, I don’t get it.
      Do you want to INSERT new rows or UPDATing existing ones based on the values from the XML file?
      If it’s INSERT only, then again there’s nothing specific to do : if the tags are not there, then column values will be NULL, simple as that.
      Could you post an example of what you mean in the XML DB forum? (sample XML, target tables etc.)
      https://forums.oracle.com/forums/forum.jspa?forumID=34
      Thanks.

  3. Johanna Cevallos
    September 28, 2012 at 16:44

    Hi Odie,
    Excuse me, Can you help me? Actually I have an application in Apex 4.1.1 with Oracle Database 11g Express Edition. This application generated an XML with master-detail relationship. It isn`t a simple XML. One of the table that I use for generate the XML has 34 columns with 1000 rows.

    The problem is: error on line 2 at column 1: expected ‘>’ when this XML is not generated when the code includes all the columns. Only the XML is generated when I work with few rows… exactly 14…

    Please, I need your comments and help.
    Thank you so much.

    Have a nice day
    Johanna

  4. venkat
    December 13, 2012 at 18:16

    Hi,

    From source we are not getting closed tag then how to handle the situation.

    • venkat
      December 13, 2012 at 18:18

      Hi,

      From source we are not getting missing node/ tag with department / closed tag then how to handle the situation.

  5. Partha Protim Paul
    December 17, 2013 at 11:32

    Hi,Odie,
    i got the oracle error.

    1 SELECT l.location_id
    2 , l.street_address
    3 , l.postal_code
    4 , l.city
    5 , l.state_province
    6 , l.country_id
    7 FROM tmp_xml t
    8 , XMLTable(‘/Locations/Location’
    9 passing t.object_value
    10 columns
    11 location_rno for ordinality
    12 , location_id number(4) path ‘@id’
    13 , street_address varchar2(40) path ‘address’
    14 , postal_code varchar2(12) path ‘postalCode’
    15 , city varchar2(30) path ‘city’
    16 , state_province varchar2(25) path ‘stateProvince’
    17 , country_id varchar2(2) path ‘countryId’
    18 , departments xmltype path ‘Departments’
    19* ) l
    SQL> /
    , XMLTable(‘/Locations/Location’
    *
    ERROR at line 8:
    ORA-00933: SQL command not properly ended

    Pls.advice me
    thanks
    Partha

  6. Hoek
    July 17, 2014 at 15:58

    Awesome stuff, Marc, thanks for sharing this, this approach fits exactly in my requirement.
    And thanks again for the help on OTN, you make XML DB fun! ;)

  7. Renji
    January 8, 2016 at 00:13

    Hi,

    Could you please guide me with below request.

    https://community.oracle.com/thread/3883492

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s