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.
Categories: HowTo, Miscellaneous
multitable, XMLTable
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
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?
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?
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.
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.
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
Please follow up in this thread : https://forums.oracle.com/forums/thread.jspa?threadID=2447354
Hi,
From source we are not getting closed tag then how to handle the situation.
Hi,
From source we are not getting missing node/ tag with department / closed tag then how to handle the situation.