Google Git
Sign in
apache / spark-website / asf-site / . / site / docs / 4.1.0-preview1 / generated-xml-funcs-examples.html
blob: e1cd509715f79f183ac5ab8a9ecc6276561f7e16 [file] [log] [blame]
<div class="codehilite"><pre><span></span><code><span class="c1">-- from_xml</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">from_xml</span><span class="p">(</span><span class="s1">&#39;&lt;p&gt;&lt;a&gt;1&lt;/a&gt;&lt;b&gt;0.8&lt;/b&gt;&lt;/p&gt;&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;a INT, b DOUBLE&#39;</span><span class="p">);</span>
<span class="o">+</span><span class="c1">-----------------------------------+</span>
<span class="o">|</span><span class="n">from_xml</span><span class="p">(</span><span class="o">&lt;</span><span class="n">p</span><span class="o">&gt;&lt;</span><span class="n">a</span><span class="o">&gt;</span><span class="mi">1</span><span class="o">&lt;/</span><span class="n">a</span><span class="o">&gt;&lt;</span><span class="n">b</span><span class="o">&gt;</span><span class="mi">0</span><span class="p">.</span><span class="mi">8</span><span class="o">&lt;/</span><span class="n">b</span><span class="o">&gt;&lt;/</span><span class="n">p</span><span class="o">&gt;</span><span class="p">)</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="err">{</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="mi">0</span><span class="p">.</span><span class="mi">8</span><span class="err">}</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------------------+</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">from_xml</span><span class="p">(</span><span class="s1">&#39;&lt;p&gt;&lt;time&gt;26/08/2015&lt;/time&gt;&lt;/p&gt;&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;time Timestamp&#39;</span><span class="p">,</span><span class="w"> </span><span class="k">map</span><span class="p">(</span><span class="s1">&#39;timestampFormat&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;dd/MM/yyyy&#39;</span><span class="p">));</span>
<span class="o">+</span><span class="c1">----------------------------------------+</span>
<span class="o">|</span><span class="n">from_xml</span><span class="p">(</span><span class="o">&lt;</span><span class="n">p</span><span class="o">&gt;&lt;</span><span class="k">time</span><span class="o">&gt;</span><span class="mi">26</span><span class="o">/</span><span class="mi">08</span><span class="o">/</span><span class="mi">2015</span><span class="o">&lt;/</span><span class="k">time</span><span class="o">&gt;&lt;/</span><span class="n">p</span><span class="o">&gt;</span><span class="p">)</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="err">{</span><span class="mi">2015</span><span class="o">-</span><span class="mi">08</span><span class="o">-</span><span class="mi">26</span><span class="w"> </span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span><span class="p">...</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------------------------------+</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">from_xml</span><span class="p">(</span><span class="s1">&#39;&lt;p&gt;&lt;teacher&gt;Alice&lt;/teacher&gt;&lt;student&gt;&lt;name&gt;Bob&lt;/name&gt;&lt;rank&gt;1&lt;/rank&gt;&lt;/student&gt;&lt;student&gt;&lt;name&gt;Charlie&lt;/name&gt;&lt;rank&gt;2&lt;/rank&gt;&lt;/student&gt;&lt;/p&gt;&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;STRUCT&lt;teacher: STRING, student: ARRAY&lt;STRUCT&lt;name: STRING, rank: INT&gt;&gt;&gt;&#39;</span><span class="p">);</span>
<span class="o">+</span><span class="c1">-----------------------------------------------------------------------------------------------------------------------------------------------+</span>
<span class="o">|</span><span class="n">from_xml</span><span class="p">(</span><span class="o">&lt;</span><span class="n">p</span><span class="o">&gt;&lt;</span><span class="n">teacher</span><span class="o">&gt;</span><span class="n">Alice</span><span class="o">&lt;/</span><span class="n">teacher</span><span class="o">&gt;&lt;</span><span class="n">student</span><span class="o">&gt;&lt;</span><span class="n">name</span><span class="o">&gt;</span><span class="n">Bob</span><span class="o">&lt;/</span><span class="n">name</span><span class="o">&gt;&lt;</span><span class="n">rank</span><span class="o">&gt;</span><span class="mi">1</span><span class="o">&lt;/</span><span class="n">rank</span><span class="o">&gt;&lt;/</span><span class="n">student</span><span class="o">&gt;&lt;</span><span class="n">student</span><span class="o">&gt;&lt;</span><span class="n">name</span><span class="o">&gt;</span><span class="n">Charlie</span><span class="o">&lt;/</span><span class="n">name</span><span class="o">&gt;&lt;</span><span class="n">rank</span><span class="o">&gt;</span><span class="mi">2</span><span class="o">&lt;/</span><span class="n">rank</span><span class="o">&gt;&lt;/</span><span class="n">student</span><span class="o">&gt;&lt;/</span><span class="n">p</span><span class="o">&gt;</span><span class="p">)</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------------------------------------------------------------------------------------------------------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="err">{</span><span class="n">Alice</span><span class="p">,</span><span class="w"> </span><span class="p">[</span><span class="err">{</span><span class="n">Bob</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="err">}</span><span class="p">...</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------------------------------------------------------------------------------------------------------------------------------+</span>
<span class="c1">-- schema_of_xml</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">schema_of_xml</span><span class="p">(</span><span class="s1">&#39;&lt;p&gt;&lt;a&gt;1&lt;/a&gt;&lt;/p&gt;&#39;</span><span class="p">);</span>
<span class="o">+</span><span class="c1">------------------------------+</span>
<span class="o">|</span><span class="n">schema_of_xml</span><span class="p">(</span><span class="o">&lt;</span><span class="n">p</span><span class="o">&gt;&lt;</span><span class="n">a</span><span class="o">&gt;</span><span class="mi">1</span><span class="o">&lt;/</span><span class="n">a</span><span class="o">&gt;&lt;/</span><span class="n">p</span><span class="o">&gt;</span><span class="p">)</span><span class="o">|</span>
<span class="o">+</span><span class="c1">------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="n">STRUCT</span><span class="o">&lt;</span><span class="n">a</span><span class="p">:</span><span class="w"> </span><span class="nb">BIGINT</span><span class="o">&gt;|</span>
<span class="o">+</span><span class="c1">------------------------------+</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">schema_of_xml</span><span class="p">(</span><span class="s1">&#39;&lt;p&gt;&lt;a attr=&quot;2&quot;&gt;1&lt;/a&gt;&lt;a&gt;3&lt;/a&gt;&lt;/p&gt;&#39;</span><span class="p">,</span><span class="w"> </span><span class="k">map</span><span class="p">(</span><span class="s1">&#39;excludeAttribute&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;true&#39;</span><span class="p">));</span>
<span class="o">+</span><span class="c1">-----------------------------------------------+</span>
<span class="o">|</span><span class="n">schema_of_xml</span><span class="p">(</span><span class="o">&lt;</span><span class="n">p</span><span class="o">&gt;&lt;</span><span class="n">a</span><span class="w"> </span><span class="n">attr</span><span class="o">=</span><span class="ss">&quot;2&quot;</span><span class="o">&gt;</span><span class="mi">1</span><span class="o">&lt;/</span><span class="n">a</span><span class="o">&gt;&lt;</span><span class="n">a</span><span class="o">&gt;</span><span class="mi">3</span><span class="o">&lt;/</span><span class="n">a</span><span class="o">&gt;&lt;/</span><span class="n">p</span><span class="o">&gt;</span><span class="p">)</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="n">STRUCT</span><span class="o">&lt;</span><span class="n">a</span><span class="p">:</span><span class="w"> </span><span class="nb">ARRAY</span><span class="o">&lt;</span><span class="n">B</span><span class="p">...</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------------------------------+</span>
<span class="c1">-- to_xml</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">to_xml</span><span class="p">(</span><span class="n">named_struct</span><span class="p">(</span><span class="s1">&#39;a&#39;</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;b&#39;</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">));</span>
<span class="o">+</span><span class="c1">--------------------------------+</span>
<span class="o">|</span><span class="n">to_xml</span><span class="p">(</span><span class="n">named_struct</span><span class="p">(</span><span class="n">a</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="n">b</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">))</span><span class="o">|</span>
<span class="o">+</span><span class="c1">--------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="o">&lt;</span><span class="k">ROW</span><span class="o">&gt;</span><span class="err">\</span><span class="n">n</span><span class="w"> </span><span class="o">&lt;</span><span class="n">a</span><span class="o">&gt;</span><span class="mi">1</span><span class="o">&lt;/</span><span class="p">...</span><span class="o">|</span>
<span class="o">+</span><span class="c1">--------------------------------+</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">to_xml</span><span class="p">(</span><span class="n">named_struct</span><span class="p">(</span><span class="s1">&#39;time&#39;</span><span class="p">,</span><span class="w"> </span><span class="n">to_timestamp</span><span class="p">(</span><span class="s1">&#39;2015-08-26&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;yyyy-MM-dd&#39;</span><span class="p">)),</span><span class="w"> </span><span class="k">map</span><span class="p">(</span><span class="s1">&#39;timestampFormat&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;dd/MM/yyyy&#39;</span><span class="p">));</span>
<span class="o">+</span><span class="c1">----------------------------------------------------------------+</span>
<span class="o">|</span><span class="n">to_xml</span><span class="p">(</span><span class="n">named_struct</span><span class="p">(</span><span class="k">time</span><span class="p">,</span><span class="w"> </span><span class="n">to_timestamp</span><span class="p">(</span><span class="mi">2015</span><span class="o">-</span><span class="mi">08</span><span class="o">-</span><span class="mi">26</span><span class="p">,</span><span class="w"> </span><span class="n">yyyy</span><span class="o">-</span><span class="n">MM</span><span class="o">-</span><span class="n">dd</span><span class="p">)))</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------------------------------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="o">&lt;</span><span class="k">ROW</span><span class="o">&gt;</span><span class="err">\</span><span class="n">n</span><span class="w"> </span><span class="o">&lt;</span><span class="k">time</span><span class="o">&gt;</span><span class="p">...</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------------------------------------------------------+</span>
<span class="c1">-- xpath</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">xpath</span><span class="p">(</span><span class="s1">&#39;&lt;a&gt;&lt;b&gt;b1&lt;/b&gt;&lt;b&gt;b2&lt;/b&gt;&lt;b&gt;b3&lt;/b&gt;&lt;c&gt;c1&lt;/c&gt;&lt;c&gt;c2&lt;/c&gt;&lt;/a&gt;&#39;</span><span class="p">,</span><span class="s1">&#39;a/b/text()&#39;</span><span class="p">);</span>
<span class="o">+</span><span class="c1">-----------------------------------------------------------------------+</span>
<span class="o">|</span><span class="n">xpath</span><span class="p">(</span><span class="o">&lt;</span><span class="n">a</span><span class="o">&gt;&lt;</span><span class="n">b</span><span class="o">&gt;</span><span class="n">b1</span><span class="o">&lt;/</span><span class="n">b</span><span class="o">&gt;&lt;</span><span class="n">b</span><span class="o">&gt;</span><span class="n">b2</span><span class="o">&lt;/</span><span class="n">b</span><span class="o">&gt;&lt;</span><span class="n">b</span><span class="o">&gt;</span><span class="n">b3</span><span class="o">&lt;/</span><span class="n">b</span><span class="o">&gt;&lt;</span><span class="k">c</span><span class="o">&gt;</span><span class="n">c1</span><span class="o">&lt;/</span><span class="k">c</span><span class="o">&gt;&lt;</span><span class="k">c</span><span class="o">&gt;</span><span class="n">c2</span><span class="o">&lt;/</span><span class="k">c</span><span class="o">&gt;&lt;/</span><span class="n">a</span><span class="o">&gt;</span><span class="p">,</span><span class="w"> </span><span class="n">a</span><span class="o">/</span><span class="n">b</span><span class="o">/</span><span class="nb">text</span><span class="p">())</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------------------------------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="p">[</span><span class="n">b1</span><span class="p">,</span><span class="w"> </span><span class="n">b2</span><span class="p">,</span><span class="w"> </span><span class="n">b3</span><span class="p">]</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------------------------------------------------------+</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">xpath</span><span class="p">(</span><span class="s1">&#39;&lt;a&gt;&lt;b&gt;b1&lt;/b&gt;&lt;b&gt;b2&lt;/b&gt;&lt;b&gt;b3&lt;/b&gt;&lt;c&gt;c1&lt;/c&gt;&lt;c&gt;c2&lt;/c&gt;&lt;/a&gt;&#39;</span><span class="p">,</span><span class="s1">&#39;a/b&#39;</span><span class="p">);</span>
<span class="o">+</span><span class="c1">----------------------------------------------------------------+</span>
<span class="o">|</span><span class="n">xpath</span><span class="p">(</span><span class="o">&lt;</span><span class="n">a</span><span class="o">&gt;&lt;</span><span class="n">b</span><span class="o">&gt;</span><span class="n">b1</span><span class="o">&lt;/</span><span class="n">b</span><span class="o">&gt;&lt;</span><span class="n">b</span><span class="o">&gt;</span><span class="n">b2</span><span class="o">&lt;/</span><span class="n">b</span><span class="o">&gt;&lt;</span><span class="n">b</span><span class="o">&gt;</span><span class="n">b3</span><span class="o">&lt;/</span><span class="n">b</span><span class="o">&gt;&lt;</span><span class="k">c</span><span class="o">&gt;</span><span class="n">c1</span><span class="o">&lt;/</span><span class="k">c</span><span class="o">&gt;&lt;</span><span class="k">c</span><span class="o">&gt;</span><span class="n">c2</span><span class="o">&lt;/</span><span class="k">c</span><span class="o">&gt;&lt;/</span><span class="n">a</span><span class="o">&gt;</span><span class="p">,</span><span class="w"> </span><span class="n">a</span><span class="o">/</span><span class="n">b</span><span class="p">)</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------------------------------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="p">[</span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="k">NULL</span><span class="p">]</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------------------------------------------------------+</span>
<span class="c1">-- xpath_boolean</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">xpath_boolean</span><span class="p">(</span><span class="s1">&#39;&lt;a&gt;&lt;b&gt;1&lt;/b&gt;&lt;/a&gt;&#39;</span><span class="p">,</span><span class="s1">&#39;a/b&#39;</span><span class="p">);</span>
<span class="o">+</span><span class="c1">-----------------------------------+</span>
<span class="o">|</span><span class="n">xpath_boolean</span><span class="p">(</span><span class="o">&lt;</span><span class="n">a</span><span class="o">&gt;&lt;</span><span class="n">b</span><span class="o">&gt;</span><span class="mi">1</span><span class="o">&lt;/</span><span class="n">b</span><span class="o">&gt;&lt;/</span><span class="n">a</span><span class="o">&gt;</span><span class="p">,</span><span class="w"> </span><span class="n">a</span><span class="o">/</span><span class="n">b</span><span class="p">)</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="k">true</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------------------+</span>
<span class="c1">-- xpath_double</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">xpath_double</span><span class="p">(</span><span class="s1">&#39;&lt;a&gt;&lt;b&gt;1&lt;/b&gt;&lt;b&gt;2&lt;/b&gt;&lt;/a&gt;&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;sum(a/b)&#39;</span><span class="p">);</span>
<span class="o">+</span><span class="c1">-----------------------------------------------+</span>
<span class="o">|</span><span class="n">xpath_double</span><span class="p">(</span><span class="o">&lt;</span><span class="n">a</span><span class="o">&gt;&lt;</span><span class="n">b</span><span class="o">&gt;</span><span class="mi">1</span><span class="o">&lt;/</span><span class="n">b</span><span class="o">&gt;&lt;</span><span class="n">b</span><span class="o">&gt;</span><span class="mi">2</span><span class="o">&lt;/</span><span class="n">b</span><span class="o">&gt;&lt;/</span><span class="n">a</span><span class="o">&gt;</span><span class="p">,</span><span class="w"> </span><span class="k">sum</span><span class="p">(</span><span class="n">a</span><span class="o">/</span><span class="n">b</span><span class="p">))</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="mi">3</span><span class="p">.</span><span class="mi">0</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------------------------------+</span>
<span class="c1">-- xpath_float</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">xpath_float</span><span class="p">(</span><span class="s1">&#39;&lt;a&gt;&lt;b&gt;1&lt;/b&gt;&lt;b&gt;2&lt;/b&gt;&lt;/a&gt;&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;sum(a/b)&#39;</span><span class="p">);</span>
<span class="o">+</span><span class="c1">----------------------------------------------+</span>
<span class="o">|</span><span class="n">xpath_float</span><span class="p">(</span><span class="o">&lt;</span><span class="n">a</span><span class="o">&gt;&lt;</span><span class="n">b</span><span class="o">&gt;</span><span class="mi">1</span><span class="o">&lt;/</span><span class="n">b</span><span class="o">&gt;&lt;</span><span class="n">b</span><span class="o">&gt;</span><span class="mi">2</span><span class="o">&lt;/</span><span class="n">b</span><span class="o">&gt;&lt;/</span><span class="n">a</span><span class="o">&gt;</span><span class="p">,</span><span class="w"> </span><span class="k">sum</span><span class="p">(</span><span class="n">a</span><span class="o">/</span><span class="n">b</span><span class="p">))</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="mi">3</span><span class="p">.</span><span class="mi">0</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------------------------------------+</span>
<span class="c1">-- xpath_int</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">xpath_int</span><span class="p">(</span><span class="s1">&#39;&lt;a&gt;&lt;b&gt;1&lt;/b&gt;&lt;b&gt;2&lt;/b&gt;&lt;/a&gt;&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;sum(a/b)&#39;</span><span class="p">);</span>
<span class="o">+</span><span class="c1">--------------------------------------------+</span>
<span class="o">|</span><span class="n">xpath_int</span><span class="p">(</span><span class="o">&lt;</span><span class="n">a</span><span class="o">&gt;&lt;</span><span class="n">b</span><span class="o">&gt;</span><span class="mi">1</span><span class="o">&lt;/</span><span class="n">b</span><span class="o">&gt;&lt;</span><span class="n">b</span><span class="o">&gt;</span><span class="mi">2</span><span class="o">&lt;/</span><span class="n">b</span><span class="o">&gt;&lt;/</span><span class="n">a</span><span class="o">&gt;</span><span class="p">,</span><span class="w"> </span><span class="k">sum</span><span class="p">(</span><span class="n">a</span><span class="o">/</span><span class="n">b</span><span class="p">))</span><span class="o">|</span>
<span class="o">+</span><span class="c1">--------------------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="mi">3</span><span class="o">|</span>
<span class="o">+</span><span class="c1">--------------------------------------------+</span>
<span class="c1">-- xpath_long</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">xpath_long</span><span class="p">(</span><span class="s1">&#39;&lt;a&gt;&lt;b&gt;1&lt;/b&gt;&lt;b&gt;2&lt;/b&gt;&lt;/a&gt;&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;sum(a/b)&#39;</span><span class="p">);</span>
<span class="o">+</span><span class="c1">---------------------------------------------+</span>
<span class="o">|</span><span class="n">xpath_long</span><span class="p">(</span><span class="o">&lt;</span><span class="n">a</span><span class="o">&gt;&lt;</span><span class="n">b</span><span class="o">&gt;</span><span class="mi">1</span><span class="o">&lt;/</span><span class="n">b</span><span class="o">&gt;&lt;</span><span class="n">b</span><span class="o">&gt;</span><span class="mi">2</span><span class="o">&lt;/</span><span class="n">b</span><span class="o">&gt;&lt;/</span><span class="n">a</span><span class="o">&gt;</span><span class="p">,</span><span class="w"> </span><span class="k">sum</span><span class="p">(</span><span class="n">a</span><span class="o">/</span><span class="n">b</span><span class="p">))</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---------------------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="mi">3</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---------------------------------------------+</span>
<span class="c1">-- xpath_number</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">xpath_number</span><span class="p">(</span><span class="s1">&#39;&lt;a&gt;&lt;b&gt;1&lt;/b&gt;&lt;b&gt;2&lt;/b&gt;&lt;/a&gt;&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;sum(a/b)&#39;</span><span class="p">);</span>
<span class="o">+</span><span class="c1">-----------------------------------------------+</span>
<span class="o">|</span><span class="n">xpath_number</span><span class="p">(</span><span class="o">&lt;</span><span class="n">a</span><span class="o">&gt;&lt;</span><span class="n">b</span><span class="o">&gt;</span><span class="mi">1</span><span class="o">&lt;/</span><span class="n">b</span><span class="o">&gt;&lt;</span><span class="n">b</span><span class="o">&gt;</span><span class="mi">2</span><span class="o">&lt;/</span><span class="n">b</span><span class="o">&gt;&lt;/</span><span class="n">a</span><span class="o">&gt;</span><span class="p">,</span><span class="w"> </span><span class="k">sum</span><span class="p">(</span><span class="n">a</span><span class="o">/</span><span class="n">b</span><span class="p">))</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="mi">3</span><span class="p">.</span><span class="mi">0</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------------------------------+</span>
<span class="c1">-- xpath_short</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">xpath_short</span><span class="p">(</span><span class="s1">&#39;&lt;a&gt;&lt;b&gt;1&lt;/b&gt;&lt;b&gt;2&lt;/b&gt;&lt;/a&gt;&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;sum(a/b)&#39;</span><span class="p">);</span>
<span class="o">+</span><span class="c1">----------------------------------------------+</span>
<span class="o">|</span><span class="n">xpath_short</span><span class="p">(</span><span class="o">&lt;</span><span class="n">a</span><span class="o">&gt;&lt;</span><span class="n">b</span><span class="o">&gt;</span><span class="mi">1</span><span class="o">&lt;/</span><span class="n">b</span><span class="o">&gt;&lt;</span><span class="n">b</span><span class="o">&gt;</span><span class="mi">2</span><span class="o">&lt;/</span><span class="n">b</span><span class="o">&gt;&lt;/</span><span class="n">a</span><span class="o">&gt;</span><span class="p">,</span><span class="w"> </span><span class="k">sum</span><span class="p">(</span><span class="n">a</span><span class="o">/</span><span class="n">b</span><span class="p">))</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="mi">3</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------------------------------------+</span>
<span class="c1">-- xpath_string</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">xpath_string</span><span class="p">(</span><span class="s1">&#39;&lt;a&gt;&lt;b&gt;b&lt;/b&gt;&lt;c&gt;cc&lt;/c&gt;&lt;/a&gt;&#39;</span><span class="p">,</span><span class="s1">&#39;a/c&#39;</span><span class="p">);</span>
<span class="o">+</span><span class="c1">-------------------------------------------+</span>
<span class="o">|</span><span class="n">xpath_string</span><span class="p">(</span><span class="o">&lt;</span><span class="n">a</span><span class="o">&gt;&lt;</span><span class="n">b</span><span class="o">&gt;</span><span class="n">b</span><span class="o">&lt;/</span><span class="n">b</span><span class="o">&gt;&lt;</span><span class="k">c</span><span class="o">&gt;</span><span class="n">cc</span><span class="o">&lt;/</span><span class="k">c</span><span class="o">&gt;&lt;/</span><span class="n">a</span><span class="o">&gt;</span><span class="p">,</span><span class="w"> </span><span class="n">a</span><span class="o">/</span><span class="k">c</span><span class="p">)</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="n">cc</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------------------------------------+</span>
</code></pre></div>