<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="load-xml">
      </a>
      15.2.10 LOAD XML Statement
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045181441984">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa71343391"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">LOAD</span> <span class="token keyword">XML</span>
    <span class="token punctuation">[</span><span class="token keyword">LOW_PRIORITY</span> <span class="token operator">|</span> <span class="token keyword">CONCURRENT</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">LOCAL</span><span class="token punctuation">]</span>
    <span class="token keyword">INFILE</span> <span class="token string">'<em class="replaceable">file_name</em>'</span>
    <span class="token punctuation">[</span><span class="token keyword">REPLACE</span> <span class="token operator">|</span> <span class="token keyword">IGNORE</span><span class="token punctuation">]</span>
    <span class="token keyword">INTO</span> <span class="token keyword">TABLE</span> <span class="token punctuation">[</span><em class="replaceable">db_name</em><span class="token punctuation">.</span><span class="token punctuation">]</span><em class="replaceable">tbl_name</em>
    <span class="token punctuation">[</span><span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <em class="replaceable">charset_name</em><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">ROWS</span> <span class="token keyword">IDENTIFIED</span> <span class="token keyword">BY</span> <span class="token string">'&lt;<em class="replaceable">tagname</em>&gt;'</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">IGNORE</span> <span class="token keyword"><em class="replaceable">number</em></span> {<span class="token keyword">LINES</span> <span class="token operator">|</span> <span class="token keyword">ROWS</span>}<span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token punctuation">(</span><em class="replaceable">field_name_or_user_var</em>
        <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">field_name_or_user_var</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">SET</span> <em class="replaceable">col_name</em><span class="token operator">=</span>{<em class="replaceable">expr</em> <span class="token operator">|</span> <span class="token keyword">DEFAULT</span>}
        <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">col_name</em><span class="token operator">=</span>{<em class="replaceable">expr</em> <span class="token operator">|</span> <span class="token keyword">DEFAULT</span>}<span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span></code></pre>
  </div>
  <p>
   The
   <a class="link" href="load-xml.html" title="15.2.10 LOAD XML Statement">
    <code class="literal">
     LOAD XML
    </code>
   </a>
   statement reads data
      from an XML file into a table. The
   <em class="replaceable">
    <code>
     file_name
    </code>
   </em>
   must be given as a literal
      string. The
   <em class="replaceable">
    <code>
     tagname
    </code>
   </em>
   in the optional
   <code class="literal">
    ROWS IDENTIFIED BY
   </code>
   clause must also be given as
      a literal string, and must be surrounded by angle brackets
      (
   <code class="literal">
    &lt;
   </code>
   and
   <code class="literal">
    &gt;
   </code>
   ).
  </p>
  <p>
   <a class="link" href="load-xml.html" title="15.2.10 LOAD XML Statement">
    <code class="literal">
     LOAD XML
    </code>
   </a>
   acts as the complement of
      running the
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   client in XML output mode
      (that is, starting the client with the
   <a class="link" href="mysql-command-options.html#option_mysql_xml">
    <code class="option">
     --xml
    </code>
   </a>
   option). To write data from a
      table to an XML file, you can invoke the
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   client with the
   <a class="link" href="mysql-command-options.html#option_mysql_xml">
    <code class="option">
     --xml
    </code>
   </a>
   and
   <a class="link" href="mysql-command-options.html#option_mysql_execute">
    <code class="option">
     -e
    </code>
   </a>
   options from
      the system shell, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa34585956"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-terminal"><span class="token prompt">$&gt; </span><span class="token command">mysql</span> <span class="token property">--xml</span> <span class="token property">-e</span> <span class="token atrule">'SELECT * FROM mydb.mytable'</span> &gt; file<span class="token punctuation">.</span>xml</code></pre>
  </div>
  <p>
   To read the file back into a table, use
   <a class="link" href="load-xml.html" title="15.2.10 LOAD XML Statement">
    <code class="literal">
     LOAD
      XML
    </code>
   </a>
   . By default, the
   <code class="literal">
    &lt;row&gt;
   </code>
   element is considered to be the equivalent of a database table
      row; this can be changed using the
   <code class="literal">
    ROWS IDENTIFIED
      BY
   </code>
   clause.
  </p>
  <p>
   This statement supports three different XML formats:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Column names as attributes and column values as attribute
          values:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-xml"><div class="docs-select-all right" id="sa49049230"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-xml"><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span><em class="replaceable">row</em></span> <span class="token attr-name"><em class="replaceable">column1</em></span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span><em class="replaceable">value1</em><span class="token punctuation">"</span></span> <span class="token attr-name"><em class="replaceable">column2</em></span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span><em class="replaceable">value2</em><span class="token punctuation">"</span></span> <span class="token attr-name">...</span><span class="token punctuation">/&gt;</span></span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Column names as tags and column values as the content of these
          tags:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-xml"><div class="docs-select-all right" id="sa79424560"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-xml"><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span><em class="replaceable">row</em></span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span><em class="replaceable">column1</em></span><span class="token punctuation">&gt;</span></span><em class="replaceable">value1</em><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span><em class="replaceable">column1</em></span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span><em class="replaceable">column2</em></span><span class="token punctuation">&gt;</span></span><em class="replaceable">value2</em><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span><em class="replaceable">column2</em></span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span><em class="replaceable">row</em></span><span class="token punctuation">&gt;</span></span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Column names are the
      <code class="literal">
       name
      </code>
      attributes of
      <code class="literal">
       &lt;field&gt;
      </code>
      tags, and values are the
          contents of these tags:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-xml"><div class="docs-select-all right" id="sa84057789"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-xml"><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>row</span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>field</span> <span class="token attr-name">name</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">'</span><em class="replaceable">column1</em><span class="token punctuation">'</span></span><span class="token punctuation">&gt;</span></span><em class="replaceable">value1</em><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>field</span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>field</span> <span class="token attr-name">name</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">'</span><em class="replaceable">column2</em><span class="token punctuation">'</span></span><span class="token punctuation">&gt;</span></span><em class="replaceable">value2</em><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>field</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>row</span><span class="token punctuation">&gt;</span></span></code></pre>
     </div>
     <p>
      This is the format used by other MySQL tools, such as
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   All three formats can be used in the same XML file; the import
      routine automatically detects the format for each row and
      interprets it correctly. Tags are matched based on the tag or
      attribute name and the column name.
  </p>
  <p>
   The following clauses work essentially the same way for
   <a class="link" href="load-xml.html" title="15.2.10 LOAD XML Statement">
    <code class="literal">
     LOAD XML
    </code>
   </a>
   as they do for
   <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
    <code class="literal">
     LOAD DATA
    </code>
   </a>
   :
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       LOW_PRIORITY
      </code>
      or
      <code class="literal">
       CONCURRENT
      </code>
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       LOCAL
      </code>
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       REPLACE
      </code>
      or
      <code class="literal">
       IGNORE
      </code>
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       CHARACTER SET
      </code>
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SET
      </code>
     </p>
    </li>
   </ul>
  </div>
  <p>
   See
   <a class="xref" href="load-data.html" title="15.2.9 LOAD DATA Statement">
    Section 15.2.9, “LOAD DATA Statement”
   </a>
   , for more information about these
      clauses.
  </p>
  <p>
   <code class="literal">
    (
    <em class="replaceable">
     <code>
      field_name_or_user_var
     </code>
    </em>
    ,
      ...)
   </code>
   is a list of one or more comma-separated XML fields
      or user variables. The name of a user variable used for this
      purpose must match the name of a field from the XML file, prefixed
      with
   <code class="literal">
    @
   </code>
   . You can use field names to select only
      desired fields. User variables can be employed to store the
      corresponding field values for subsequent re-use.
  </p>
  <p>
   The
   <code class="literal">
    IGNORE
    <em class="replaceable">
     <code>
      number
     </code>
    </em>
    LINES
   </code>
   or
   <code class="literal">
    IGNORE
    <em class="replaceable">
     <code>
      number
     </code>
    </em>
    ROWS
   </code>
   clause causes the
      first
   <em class="replaceable">
    <code>
     number
    </code>
   </em>
   rows in the XML file to be
      skipped. It is analogous to the
   <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
    <code class="literal">
     LOAD
      DATA
    </code>
   </a>
   statement's
   <code class="literal">
    IGNORE ... LINES
   </code>
   clause.
  </p>
  <p>
   Suppose that we have a table named
   <code class="literal">
    person
   </code>
   ,
      created as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa53385062"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">USE</span> test<span class="token punctuation">;</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> person <span class="token punctuation">(</span>
    person_id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
    fname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">40</span><span class="token punctuation">)</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    lname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">40</span><span class="token punctuation">)</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    created <span class="token datatype">TIMESTAMP</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Suppose further that this table is initially empty.
  </p>
  <p>
   Now suppose that we have a simple XML file
   <code class="filename">
    person.xml
   </code>
   , whose contents are as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-xml"><div class="docs-select-all right" id="sa67689216"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-xml"><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>list</span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>person</span> <span class="token attr-name">person_id</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>1<span class="token punctuation">"</span></span> <span class="token attr-name">fname</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>Kapek<span class="token punctuation">"</span></span> <span class="token attr-name">lname</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>Sainnouine<span class="token punctuation">"</span></span><span class="token punctuation">/&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>person</span> <span class="token attr-name">person_id</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>2<span class="token punctuation">"</span></span> <span class="token attr-name">fname</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>Sajon<span class="token punctuation">"</span></span> <span class="token attr-name">lname</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>Rondela<span class="token punctuation">"</span></span><span class="token punctuation">/&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>person</span> <span class="token attr-name">person_id</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>3<span class="token punctuation">"</span></span><span class="token punctuation">&gt;</span></span><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>fname</span><span class="token punctuation">&gt;</span></span>Likame<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>fname</span><span class="token punctuation">&gt;</span></span><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>lname</span><span class="token punctuation">&gt;</span></span>Örrtmons<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>lname</span><span class="token punctuation">&gt;</span></span><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>person</span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>person</span> <span class="token attr-name">person_id</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>4<span class="token punctuation">"</span></span><span class="token punctuation">&gt;</span></span><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>fname</span><span class="token punctuation">&gt;</span></span>Slar<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>fname</span><span class="token punctuation">&gt;</span></span><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>lname</span><span class="token punctuation">&gt;</span></span>Manlanth<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>lname</span><span class="token punctuation">&gt;</span></span><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>person</span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>person</span><span class="token punctuation">&gt;</span></span><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>field</span> <span class="token attr-name">name</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>person_id<span class="token punctuation">"</span></span><span class="token punctuation">&gt;</span></span>5<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>field</span><span class="token punctuation">&gt;</span></span><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>field</span> <span class="token attr-name">name</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>fname<span class="token punctuation">"</span></span><span class="token punctuation">&gt;</span></span>Stoma<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>field</span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>field</span> <span class="token attr-name">name</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>lname<span class="token punctuation">"</span></span><span class="token punctuation">&gt;</span></span>Milu<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>field</span><span class="token punctuation">&gt;</span></span><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>person</span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>person</span><span class="token punctuation">&gt;</span></span><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>field</span> <span class="token attr-name">name</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>person_id<span class="token punctuation">"</span></span><span class="token punctuation">&gt;</span></span>6<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>field</span><span class="token punctuation">&gt;</span></span><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>field</span> <span class="token attr-name">name</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>fname<span class="token punctuation">"</span></span><span class="token punctuation">&gt;</span></span>Nirtam<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>field</span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>field</span> <span class="token attr-name">name</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>lname<span class="token punctuation">"</span></span><span class="token punctuation">&gt;</span></span>Sklöd<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>field</span><span class="token punctuation">&gt;</span></span><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>person</span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>person</span> <span class="token attr-name">person_id</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>7<span class="token punctuation">"</span></span><span class="token punctuation">&gt;</span></span><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>fname</span><span class="token punctuation">&gt;</span></span>Sungam<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>fname</span><span class="token punctuation">&gt;</span></span><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>lname</span><span class="token punctuation">&gt;</span></span>Dulbåd<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>lname</span><span class="token punctuation">&gt;</span></span><span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>person</span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>person</span> <span class="token attr-name">person_id</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>8<span class="token punctuation">"</span></span> <span class="token attr-name">fname</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>Sraref<span class="token punctuation">"</span></span> <span class="token attr-name">lname</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>Encmelt<span class="token punctuation">"</span></span><span class="token punctuation">/&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>list</span><span class="token punctuation">&gt;</span></span></code></pre>
  </div>
  <p>
   Each of the permissible XML formats discussed previously is
      represented in this example file.
  </p>
  <p>
   To import the data in
   <code class="filename">
    person.xml
   </code>
   into the
   <code class="literal">
    person
   </code>
   table, you can use this statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa86770523"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">LOAD</span> <span class="token keyword">XML</span> <span class="token keyword">LOCAL</span> <span class="token keyword">INFILE</span> <span class="token string">'person.xml'</span>
<span class="token prompt">    -&gt;</span>   <span class="token keyword">INTO</span> <span class="token keyword">TABLE</span> person
<span class="token prompt">    -&gt;</span>   <span class="token keyword">ROWS</span> <span class="token keyword">IDENTIFIED</span> <span class="token keyword">BY</span> <span class="token string">'&lt;person&gt;'</span><span class="token punctuation">;</span>

<span class="token output">Query OK, 8 rows affected (0.00 sec)</span>
<span class="token output">Records: 8  Deleted: 0  Skipped: 0  Warnings: 0</span></code></pre>
  </div>
  <p>
   Here, we assume that
   <code class="filename">
    person.xml
   </code>
   is located in
      the MySQL data directory. If the file cannot be found, the
      following error results:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa25318220"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">ERROR 2 (HY000): File '/person.xml' not found (Errcode: 2)</code></pre>
  </div>
  <p>
   The
   <code class="literal">
    ROWS IDENTIFIED BY '&lt;person&gt;'
   </code>
   clause
      means that each
   <code class="literal">
    &lt;person&gt;
   </code>
   element in the
      XML file is considered equivalent to a row in the table into which
      the data is to be imported. In this case, this is the
   <code class="literal">
    person
   </code>
   table in the
   <code class="literal">
    test
   </code>
   database.
  </p>
  <p>
   As can be seen by the response from the server, 8 rows were
      imported into the
   <code class="literal">
    test.person
   </code>
   table. This can
      be verified by a simple
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa14653835"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> person<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> person_id <span class="token punctuation">|</span> fname  <span class="token punctuation">|</span> lname      <span class="token punctuation">|</span> created             <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>         1 <span class="token punctuation">|</span> Kapek  <span class="token punctuation">|</span> Sainnouine <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>13 16:18:47 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>         2 <span class="token punctuation">|</span> Sajon  <span class="token punctuation">|</span> Rondela    <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>13 16:18:47 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>         3 <span class="token punctuation">|</span> Likame <span class="token punctuation">|</span> Örrtmons   <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>13 16:18:47 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>         4 <span class="token punctuation">|</span> Slar   <span class="token punctuation">|</span> Manlanth   <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>13 16:18:47 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>         5 <span class="token punctuation">|</span> Stoma  <span class="token punctuation">|</span> Nilu       <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>13 16:18:47 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>         6 <span class="token punctuation">|</span> Nirtam <span class="token punctuation">|</span> Sklöd      <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>13 16:18:47 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>         7 <span class="token punctuation">|</span> Sungam <span class="token punctuation">|</span> Dulbåd     <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>13 16:18:47 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>         8 <span class="token punctuation">|</span> Sreraf <span class="token punctuation">|</span> Encmelt    <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>13 16:18:47 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">8 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   This shows, as stated earlier in this section, that any or all of
      the 3 permitted XML formats may appear in a single file and be
      read using
   <a class="link" href="load-xml.html" title="15.2.10 LOAD XML Statement">
    <code class="literal">
     LOAD XML
    </code>
   </a>
   .
  </p>
  <p>
   The inverse of the import operation just shown—that is,
      dumping MySQL table data into an XML file—can be
      accomplished using the
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   client from the
      system shell, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa23174815"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-terminal"><span class="token prompt">$&gt; </span><span class="token command">mysql</span> <span class="token property">--xml</span> <span class="token property">-e</span> <span class="token atrule">"SELECT * FROM test.person"</span> &gt; person-dump<span class="token punctuation">.</span>xml
<span class="token prompt">$&gt; </span><span class="token command">cat</span> person-dump<span class="token punctuation">.</span>xml
&lt;?xml version=<span class="token atrule">"1.0"</span>?&gt;

&lt;resultset statement=<span class="token atrule">"SELECT * FROM test.person"</span> xmlns<span class="token punctuation">:</span>xsi=<span class="token atrule">"http://www.w3.org/2001/XMLSchema-instance"</span>&gt;
  &lt;row&gt;
	&lt;field name=<span class="token atrule">"person_id"</span>&gt;1&lt;/field&gt;
	&lt;field name=<span class="token atrule">"fname"</span>&gt;Kapek&lt;/field&gt;
	&lt;field name=<span class="token atrule">"lname"</span>&gt;Sainnouine&lt;/field&gt;
  &lt;/row&gt;

  &lt;row&gt;
	&lt;field name=<span class="token atrule">"person_id"</span>&gt;2&lt;/field&gt;
	&lt;field name=<span class="token atrule">"fname"</span>&gt;Sajon&lt;/field&gt;
	&lt;field name=<span class="token atrule">"lname"</span>&gt;Rondela&lt;/field&gt;
  &lt;/row&gt;

  &lt;row&gt;
	&lt;field name=<span class="token atrule">"person_id"</span>&gt;3&lt;/field&gt;
	&lt;field name=<span class="token atrule">"fname"</span>&gt;Likema&lt;/field&gt;
	&lt;field name=<span class="token atrule">"lname"</span>&gt;Örrtmons&lt;/field&gt;
  &lt;/row&gt;

  &lt;row&gt;
	&lt;field name=<span class="token atrule">"person_id"</span>&gt;4&lt;/field&gt;
	&lt;field name=<span class="token atrule">"fname"</span>&gt;Slar&lt;/field&gt;
	&lt;field name=<span class="token atrule">"lname"</span>&gt;Manlanth&lt;/field&gt;
  &lt;/row&gt;

  &lt;row&gt;
	&lt;field name=<span class="token atrule">"person_id"</span>&gt;5&lt;/field&gt;
	&lt;field name=<span class="token atrule">"fname"</span>&gt;Stoma&lt;/field&gt;
	&lt;field name=<span class="token atrule">"lname"</span>&gt;Nilu&lt;/field&gt;
  &lt;/row&gt;

  &lt;row&gt;
	&lt;field name=<span class="token atrule">"person_id"</span>&gt;6&lt;/field&gt;
	&lt;field name=<span class="token atrule">"fname"</span>&gt;Nirtam&lt;/field&gt;
	&lt;field name=<span class="token atrule">"lname"</span>&gt;Sklöd&lt;/field&gt;
  &lt;/row&gt;

  &lt;row&gt;
	&lt;field name=<span class="token atrule">"person_id"</span>&gt;7&lt;/field&gt;
	&lt;field name=<span class="token atrule">"fname"</span>&gt;Sungam&lt;/field&gt;
	&lt;field name=<span class="token atrule">"lname"</span>&gt;Dulbåd&lt;/field&gt;
  &lt;/row&gt;

  &lt;row&gt;
	&lt;field name=<span class="token atrule">"person_id"</span>&gt;8&lt;/field&gt;
	&lt;field name=<span class="token atrule">"fname"</span>&gt;Sreraf&lt;/field&gt;
	&lt;field name=<span class="token atrule">"lname"</span>&gt;Encmelt&lt;/field&gt;
  &lt;/row&gt;
&lt;/resultset&gt;</code></pre>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    The
    <a class="link" href="mysql-command-options.html#option_mysql_xml">
     <code class="option">
      --xml
     </code>
    </a>
    option causes the
    <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
     <span class="command">
      <strong>
       mysql
      </strong>
     </span>
    </a>
    client to use XML formatting for its
        output; the
    <a class="link" href="mysql-command-options.html#option_mysql_execute">
     <code class="option">
      -e
     </code>
    </a>
    option causes the client to execute the SQL statement
        immediately following the option. See
    <a class="xref" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
     Section 6.5.1, “mysql — The MySQL Command-Line Client”
    </a>
    .
   </p>
  </div>
  <p>
   You can verify that the dump is valid by creating a copy of the
   <code class="literal">
    person
   </code>
   table and importing the dump file into
      the new table, like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa424614"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">USE</span> test<span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> person2 <span class="token operator">LIKE</span> person<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">LOAD</span> <span class="token keyword">XML</span> <span class="token keyword">LOCAL</span> <span class="token keyword">INFILE</span> <span class="token string">'person-dump.xml'</span>
<span class="token prompt">    -&gt;</span>   <span class="token keyword">INTO</span> <span class="token keyword">TABLE</span> person2<span class="token punctuation">;</span>
<span class="token output">Query OK, 8 rows affected (0.01 sec)</span>
<span class="token output">Records: 8  Deleted: 0  Skipped: 0  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> person2<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> person_id <span class="token punctuation">|</span> fname  <span class="token punctuation">|</span> lname      <span class="token punctuation">|</span> created             <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>         1 <span class="token punctuation">|</span> Kapek  <span class="token punctuation">|</span> Sainnouine <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>13 16:18:47 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>         2 <span class="token punctuation">|</span> Sajon  <span class="token punctuation">|</span> Rondela    <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>13 16:18:47 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>         3 <span class="token punctuation">|</span> Likema <span class="token punctuation">|</span> Örrtmons   <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>13 16:18:47 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>         4 <span class="token punctuation">|</span> Slar   <span class="token punctuation">|</span> Manlanth   <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>13 16:18:47 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>         5 <span class="token punctuation">|</span> Stoma  <span class="token punctuation">|</span> Nilu       <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>13 16:18:47 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>         6 <span class="token punctuation">|</span> Nirtam <span class="token punctuation">|</span> Sklöd      <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>13 16:18:47 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>         7 <span class="token punctuation">|</span> Sungam <span class="token punctuation">|</span> Dulbåd     <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>13 16:18:47 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>         8 <span class="token punctuation">|</span> Sreraf <span class="token punctuation">|</span> Encmelt    <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>13 16:18:47 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">8 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   There is no requirement that every field in the XML file be
      matched with a column in the corresponding table. Fields which
      have no corresponding columns are skipped. You can see this by
      first emptying the
   <code class="literal">
    person2
   </code>
   table and dropping
      the
   <code class="literal">
    created
   </code>
   column, then using the same
   <a class="link" href="load-xml.html" title="15.2.10 LOAD XML Statement">
    <code class="literal">
     LOAD XML
    </code>
   </a>
   statement we just employed
      previously, like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa40410738"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">TRUNCATE</span> person2<span class="token punctuation">;</span>
<span class="token output">Query OK, 8 rows affected (0.26 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> person2 <span class="token keyword">DROP</span> <span class="token keyword">COLUMN</span> created<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.52 sec)</span>
<span class="token output">Records: 0  Duplicates: 0  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> person2\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
       Table<span class="token punctuation">:</span> person2
Create Table<span class="token punctuation">:</span> CREATE TABLE `person2` (
  `person_id` int NOT NULL,
  `fname` varchar(40) DEFAULT NULL,
  `lname` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
</span><span class="token output">1 row in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">LOAD</span> <span class="token keyword">XML</span> <span class="token keyword">LOCAL</span> <span class="token keyword">INFILE</span> <span class="token string">'person-dump.xml'</span>
<span class="token prompt">    -&gt;</span>   <span class="token keyword">INTO</span> <span class="token keyword">TABLE</span> person2<span class="token punctuation">;</span>
<span class="token output">Query OK, 8 rows affected (0.01 sec)</span>
<span class="token output">Records: 8  Deleted: 0  Skipped: 0  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> person2<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> person_id <span class="token punctuation">|</span> fname  <span class="token punctuation">|</span> lname      <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>         1 <span class="token punctuation">|</span> Kapek  <span class="token punctuation">|</span> Sainnouine <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>         2 <span class="token punctuation">|</span> Sajon  <span class="token punctuation">|</span> Rondela    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>         3 <span class="token punctuation">|</span> Likema <span class="token punctuation">|</span> Örrtmons   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>         4 <span class="token punctuation">|</span> Slar   <span class="token punctuation">|</span> Manlanth   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>         5 <span class="token punctuation">|</span> Stoma  <span class="token punctuation">|</span> Nilu       <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>         6 <span class="token punctuation">|</span> Nirtam <span class="token punctuation">|</span> Sklöd      <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>         7 <span class="token punctuation">|</span> Sungam <span class="token punctuation">|</span> Dulbåd     <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>         8 <span class="token punctuation">|</span> Sreraf <span class="token punctuation">|</span> Encmelt    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">8 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   The order in which the fields are given within each row of the XML
      file does not affect the operation of
   <a class="link" href="load-xml.html" title="15.2.10 LOAD XML Statement">
    <code class="literal">
     LOAD
      XML
    </code>
   </a>
   ; the field order can vary from row to row, and is
      not required to be in the same order as the corresponding columns
      in the table.
  </p>
  <p>
   As mentioned previously, you can use a
   <code class="literal">
    (
    <em class="replaceable">
     <code>
      field_name_or_user_var
     </code>
    </em>
    ,
      ...)
   </code>
   list of one or more XML fields (to select desired
      fields only) or user variables (to store the corresponding field
      values for later use). User variables can be especially useful
      when you want to insert data from an XML file into table columns
      whose names do not match those of the XML fields. To see how this
      works, we first create a table named
   <code class="literal">
    individual
   </code>
   whose structure matches that of the
   <code class="literal">
    person
   </code>
   table, but whose columns are named differently:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa90374703"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> individual <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     individual_id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     name1 <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">40</span><span class="token punctuation">)</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     name2 <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">40</span><span class="token punctuation">)</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     made <span class="token datatype">TIMESTAMP</span>
<span class="token prompt">    -&gt;</span> <span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.42 sec)</span></code></pre>
  </div>
  <p>
   In this case, you cannot simply load the XML file directly into
      the table, because the field and column names do not match:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa35901223"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">LOAD</span> <span class="token keyword">XML</span> <span class="token keyword">INFILE</span> <span class="token string">'../bin/person-dump.xml'</span> <span class="token keyword">INTO</span> <span class="token keyword">TABLE</span> test<span class="token punctuation">.</span>individual<span class="token punctuation">;</span>
<span class="token output">ERROR 1263 (22004)<span class="token punctuation">:</span> Column set to default value; NULL supplied to NOT NULL column 'individual_id' at row 1</span></code></pre>
  </div>
  <p>
   This happens because the MySQL server looks for field names
      matching the column names of the target table. You can work around
      this problem by selecting the field values into user variables,
      then setting the target table's columns equal to the values
      of those variables using
   <code class="literal">
    SET
   </code>
   . You can perform
      both of these operations in a single statement, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa26590574"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">LOAD</span> <span class="token keyword">XML</span> <span class="token keyword">INFILE</span> <span class="token string">'../bin/person-dump.xml'</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">INTO</span> <span class="token keyword">TABLE</span> test<span class="token punctuation">.</span>individual <span class="token punctuation">(</span><span class="token variable">@person_id</span><span class="token punctuation">,</span> <span class="token variable">@fname</span><span class="token punctuation">,</span> <span class="token variable">@lname</span><span class="token punctuation">,</span> <span class="token variable">@created</span><span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">SET</span> individual_id<span class="token operator">=</span><span class="token variable">@person_id</span><span class="token punctuation">,</span> name1<span class="token operator">=</span><span class="token variable">@fname</span><span class="token punctuation">,</span> name2<span class="token operator">=</span><span class="token variable">@lname</span><span class="token punctuation">,</span> made<span class="token operator">=</span><span class="token variable">@created</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 8 rows affected (0.05 sec)</span>
<span class="token output">Records: 8  Deleted: 0  Skipped: 0  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> individual<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> individual_id <span class="token punctuation">|</span> name1  <span class="token punctuation">|</span> name2      <span class="token punctuation">|</span> made                <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>             1 <span class="token punctuation">|</span> Kapek  <span class="token punctuation">|</span> Sainnouine <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>13 16:18:47 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>             2 <span class="token punctuation">|</span> Sajon  <span class="token punctuation">|</span> Rondela    <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>13 16:18:47 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>             3 <span class="token punctuation">|</span> Likema <span class="token punctuation">|</span> Örrtmons   <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>13 16:18:47 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>             4 <span class="token punctuation">|</span> Slar   <span class="token punctuation">|</span> Manlanth   <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>13 16:18:47 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>             5 <span class="token punctuation">|</span> Stoma  <span class="token punctuation">|</span> Nilu       <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>13 16:18:47 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>             6 <span class="token punctuation">|</span> Nirtam <span class="token punctuation">|</span> Sklöd      <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>13 16:18:47 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>             7 <span class="token punctuation">|</span> Sungam <span class="token punctuation">|</span> Dulbåd     <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>13 16:18:47 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>             8 <span class="token punctuation">|</span> Srraf  <span class="token punctuation">|</span> Encmelt    <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>13 16:18:47 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">8 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   The names of the user variables
   <span class="emphasis">
    <em>
     must
    </em>
   </span>
   match
      those of the corresponding fields from the XML file, with the
      addition of the required
   <code class="literal">
    @
   </code>
   prefix to indicate
      that they are variables. The user variables need not be listed or
      assigned in the same order as the corresponding fields.
  </p>
  <p>
   Using a
   <code class="literal">
    ROWS IDENTIFIED BY
      '&lt;
    <em class="replaceable">
     <code>
      tagname
     </code>
    </em>
    &gt;'
   </code>
   clause, it
      is possible to import data from the same XML file into database
      tables with different definitions. For this example, suppose that
      you have a file named
   <code class="filename">
    address.xml
   </code>
   which
      contains the following XML:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-xml"><div class="docs-select-all right" id="sa36190955"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-xml"><span class="token prolog">&lt;?xml version="1.0"?&gt;</span>

<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>list</span><span class="token punctuation">&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>person</span> <span class="token attr-name">person_id</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>1<span class="token punctuation">"</span></span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>fname</span><span class="token punctuation">&gt;</span></span>Robert<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>fname</span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>lname</span><span class="token punctuation">&gt;</span></span>Jones<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>lname</span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>address</span> <span class="token attr-name">address_id</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>1<span class="token punctuation">"</span></span> <span class="token attr-name">street</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>Mill Creek Road<span class="token punctuation">"</span></span> <span class="token attr-name">zip</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>45365<span class="token punctuation">"</span></span> <span class="token attr-name">city</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>Sidney<span class="token punctuation">"</span></span><span class="token punctuation">/&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>address</span> <span class="token attr-name">address_id</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>2<span class="token punctuation">"</span></span> <span class="token attr-name">street</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>Main Street<span class="token punctuation">"</span></span> <span class="token attr-name">zip</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>28681<span class="token punctuation">"</span></span> <span class="token attr-name">city</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>Taylorsville<span class="token punctuation">"</span></span><span class="token punctuation">/&gt;</span></span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>person</span><span class="token punctuation">&gt;</span></span>

  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>person</span> <span class="token attr-name">person_id</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>2<span class="token punctuation">"</span></span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>fname</span><span class="token punctuation">&gt;</span></span>Mary<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>fname</span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>lname</span><span class="token punctuation">&gt;</span></span>Smith<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>lname</span><span class="token punctuation">&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>address</span> <span class="token attr-name">address_id</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>3<span class="token punctuation">"</span></span> <span class="token attr-name">street</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>River Road<span class="token punctuation">"</span></span> <span class="token attr-name">zip</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>80239<span class="token punctuation">"</span></span> <span class="token attr-name">city</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>Denver<span class="token punctuation">"</span></span><span class="token punctuation">/&gt;</span></span>
    <span class="token comment" spellcheck="true">&lt;!-- &lt;address address_id="4" street="North Street" zip="37920" city="Knoxville"/&gt; --&gt;</span>
  <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>person</span><span class="token punctuation">&gt;</span></span>

<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>list</span><span class="token punctuation">&gt;</span></span></code></pre>
  </div>
  <p>
   You can again use the
   <code class="literal">
    test.person
   </code>
   table as
      defined previously in this section, after clearing all the
      existing records from the table and then showing its structure as
      shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa38738650"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql">mysql<span class="token operator">&lt;</span> <span class="token keyword">TRUNCATE</span> person<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.04 sec)</span>

mysql<span class="token operator">&lt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> person\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
       Table<span class="token punctuation">:</span> person
Create Table<span class="token punctuation">:</span> CREATE TABLE `person` (
  `person_id` int(11) NOT NULL,
  `fname` varchar(40) DEFAULT NULL,
  `lname` varchar(40) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
</span><span class="token output">1 row in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   Now create an
   <code class="literal">
    address
   </code>
   table in the
   <code class="literal">
    test
   </code>
   database using the following
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa25858576"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> address <span class="token punctuation">(</span>
    address_id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
    person_id <span class="token datatype">INT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    street <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">40</span><span class="token punctuation">)</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    zip <span class="token datatype">INT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    city <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">40</span><span class="token punctuation">)</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    created <span class="token datatype">TIMESTAMP</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To import the data from the XML file into the
   <code class="literal">
    person
   </code>
   table, execute the following
   <a class="link" href="load-xml.html" title="15.2.10 LOAD XML Statement">
    <code class="literal">
     LOAD XML
    </code>
   </a>
   statement, which specifies
      that rows are to be specified by the
   <code class="literal">
    &lt;person&gt;
   </code>
   element, as shown here;
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa89207969"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">LOAD</span> <span class="token keyword">XML</span> <span class="token keyword">LOCAL</span> <span class="token keyword">INFILE</span> <span class="token string">'address.xml'</span>
<span class="token prompt">    -&gt;</span>   <span class="token keyword">INTO</span> <span class="token keyword">TABLE</span> person
<span class="token prompt">    -&gt;</span>   <span class="token keyword">ROWS</span> <span class="token keyword">IDENTIFIED</span> <span class="token keyword">BY</span> <span class="token string">'&lt;person&gt;'</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 2 rows affected (0.00 sec)</span>
<span class="token output">Records: 2  Deleted: 0  Skipped: 0  Warnings: 0</span></code></pre>
  </div>
  <p>
   You can verify that the records were imported using a
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa12803835"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> person<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> person_id <span class="token punctuation">|</span> fname  <span class="token punctuation">|</span> lname <span class="token punctuation">|</span> created             <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>         1 <span class="token punctuation">|</span> Robert <span class="token punctuation">|</span> Jones <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>24 17:37:06 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>         2 <span class="token punctuation">|</span> Mary   <span class="token punctuation">|</span> Smith <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>24 17:37:06 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">2 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   Since the
   <code class="literal">
    &lt;address&gt;
   </code>
   elements in the XML
      file have no corresponding columns in the
   <code class="literal">
    person
   </code>
   table, they are skipped.
  </p>
  <p>
   To import the data from the
   <code class="literal">
    &lt;address&gt;
   </code>
   elements into the
   <code class="literal">
    address
   </code>
   table, use the
   <a class="link" href="load-xml.html" title="15.2.10 LOAD XML Statement">
    <code class="literal">
     LOAD XML
    </code>
   </a>
   statement shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa47748309"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">LOAD</span> <span class="token keyword">XML</span> <span class="token keyword">LOCAL</span> <span class="token keyword">INFILE</span> <span class="token string">'address.xml'</span>
<span class="token prompt">    -&gt;</span>   <span class="token keyword">INTO</span> <span class="token keyword">TABLE</span> address
<span class="token prompt">    -&gt;</span>   <span class="token keyword">ROWS</span> <span class="token keyword">IDENTIFIED</span> <span class="token keyword">BY</span> <span class="token string">'&lt;address&gt;'</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 3 rows affected (0.00 sec)</span>
<span class="token output">Records: 3  Deleted: 0  Skipped: 0  Warnings: 0</span></code></pre>
  </div>
  <p>
   You can see that the data was imported using a
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statement such as this one:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa75491937"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> address<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> address_id <span class="token punctuation">|</span> person_id <span class="token punctuation">|</span> street          <span class="token punctuation">|</span> zip   <span class="token punctuation">|</span> city         <span class="token punctuation">|</span> created             <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>          1 <span class="token punctuation">|</span>         1 <span class="token punctuation">|</span> Mill Creek Road <span class="token punctuation">|</span> 45365 <span class="token punctuation">|</span> Sidney       <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>24 17:37:37 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>          2 <span class="token punctuation">|</span>         1 <span class="token punctuation">|</span> Main Street     <span class="token punctuation">|</span> 28681 <span class="token punctuation">|</span> Taylorsville <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>24 17:37:37 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>          3 <span class="token punctuation">|</span>         2 <span class="token punctuation">|</span> River Road      <span class="token punctuation">|</span> 80239 <span class="token punctuation">|</span> Denver       <span class="token punctuation">|</span> 2007<span class="token punctuation">-</span>07<span class="token punctuation">-</span>24 17:37:37 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">3 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   The data from the
   <code class="literal">
    &lt;address&gt;
   </code>
   element that
      is enclosed in XML comments is not imported. However, since there
      is a
   <code class="literal">
    person_id
   </code>
   column in the
   <code class="literal">
    address
   </code>
   table, the value of the
   <code class="literal">
    person_id
   </code>
   attribute from the parent
   <code class="literal">
    &lt;person&gt;
   </code>
   element for each
   <code class="literal">
    &lt;address&gt;
   </code>
   <span class="emphasis">
    <em>
     is
    </em>
   </span>
   imported into the
   <code class="literal">
    address
   </code>
   table.
  </p>
  <p>
   <b>
    Security Considerations.
   </b>
   As with the
   <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
    <code class="literal">
     LOAD DATA
    </code>
   </a>
   statement,
        the transfer of the XML file from the client host to the server
        host is initiated by the MySQL server. In theory, a patched
        server could be built that would tell the client program to
        transfer a file of the server's choosing rather than the file
        named by the client in the
   <a class="link" href="load-xml.html" title="15.2.10 LOAD XML Statement">
    <code class="literal">
     LOAD
        XML
    </code>
   </a>
   statement. Such a server could access any file on
        the client host to which the client user has read access.
  </p>
  <p>
   In a Web environment, clients usually connect to MySQL from a Web
      server. A user that can run any command against the MySQL server
      can use
   <a class="link" href="load-xml.html" title="15.2.10 LOAD XML Statement">
    <code class="literal">
     LOAD XML
      LOCAL
    </code>
   </a>
   to read any files to which the Web server process
      has read access. In this environment, the client with respect to
      the MySQL server is actually the Web server, not the remote
      program being run by the user who connects to the Web server.
  </p>
  <p>
   You can disable loading of XML files from clients by starting the
      server with
   <a class="link" href="server-system-variables.html#sysvar_local_infile">
    <code class="literal">
     --local-infile=0
    </code>
   </a>
   or
   <a class="link" href="server-system-variables.html#sysvar_local_infile">
    <code class="literal">
     --local-infile=OFF
    </code>
   </a>
   . This option
      can also be used when starting the
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   client
      to disable
   <a class="link" href="load-xml.html" title="15.2.10 LOAD XML Statement">
    <code class="literal">
     LOAD XML
    </code>
   </a>
   for the
      duration of the client session.
  </p>
  <p>
   To prevent a client from loading XML files from the server, do not
      grant the
   <a class="link" href="privileges-provided.html#priv_file">
    <code class="literal">
     FILE
    </code>
   </a>
   privilege to the
      corresponding MySQL user account, or revoke this privilege if the
      client user account already has it.
  </p>
  <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Important
   </div>
   <p>
    Revoking the
    <a class="link" href="privileges-provided.html#priv_file">
     <code class="literal">
      FILE
     </code>
    </a>
    privilege (or
        not granting it in the first place) keeps the user only from
        executing the
    <a class="link" href="load-xml.html" title="15.2.10 LOAD XML Statement">
     <code class="literal">
      LOAD XML
     </code>
    </a>
    statement
        (as well as the
    <a class="link" href="string-functions.html#function_load-file">
     <code class="literal">
      LOAD_FILE()
     </code>
    </a>
    function; it does
    <span class="emphasis">
     <em>
      not
     </em>
    </span>
    prevent the user from
        executing
    <a class="link" href="load-xml.html" title="15.2.10 LOAD XML Statement">
     <code class="literal">
      LOAD XML
        LOCAL
     </code>
    </a>
    . To disallow this statement, you must start the
        server or the client with
    <code class="option">
     --local-infile=OFF
    </code>
    .
   </p>
   <p>
    In other words, the
    <a class="link" href="privileges-provided.html#priv_file">
     <code class="literal">
      FILE
     </code>
    </a>
    privilege affects only whether the client can read files on the
        server; it has no bearing on whether the client can read files
        on the local file system.
   </p>
  </div>
 </div>
 <br/>
</div>
