At https://stats.stackexchange.com/a/86503/919 I have argued that to study the right tail of a distribution $F,$ you should analyze its survival function $1-F$ for large values of the argument. Because such questions often compare tail behavior to some form of exponential, it is convenient to look at the logarithm of the survival function.
Background and definitions
The Generalized Pareto Distribution is a family of distributions. Their shapes depend on one real parameter $\xi.$ The other two parameters allow you to select any unit of measurement you like by choosing its origin $\mu$ and scale $\sigma,$ and therefore do not affect asymptotic tail behavior. Consequently we may set $\mu=0$ and $\sigma=1$ without losing any generality.
For $\xi\ne 0,$ the log survival function is
$$\log(1 - F_\xi(z)) = -\frac{\log(1 + \xi z)}{\xi}\tag{1}$$
on the domain $[0, \infty)$ for positive $\xi$ and the domain $[0, -1/\xi)$ for negative $\xi.$
The case of negative $\xi$ is relatively uninteresting because there is no probability of values exceeding $-1/\xi:$ the right tail ultimately is zero. Let's focus henceforth on non-negative $\xi.$
The "edge case" is the limit as $\xi\to 0.$ L'Hopital's Rule enables us to find this limit as
$$\lim_{\xi\to 0} \log\left(1-F_\xi(z)\right) = - \lim_{\xi\to 0} \frac{\frac{d}{d\xi}(\log(1+\xi z))}{\frac{d}{d\xi}\xi} = -\lim_{\xi\to 0} \frac{z}{1+\xi z} = -z.$$
Thus, it makes sense to include the distribution with log survival function $-z$ in this family. That distribution is the Exponential distribution.
Analysis
Now we fix $\xi\ge 0$ and study the distribution for large $z.$ In this case $(1)$ is asymptotically a power tail because the Taylor expansion of the logarithm around $1$ shows
$$\eqalign{-\frac{1}{\xi}\log(1+\xi z) &= -\frac{1}{\xi}\left(\log(\xi) + \log(z) + \log\left(1 + \frac{1}{\xi z}\right)\right) \\
&= -\frac{1}{\xi} \log(z) + C_\xi - \frac{1}{\xi^2}z^{-1} + O(z^{-2})
}$$
where $C_\xi = -(\log(\xi))/\xi$ is just a normalizing constant. All the terms eventually become arbitrarily small compared to the first, which is a constant times $\log(z):$ when exponentiated, this shows the survival function asymptotically is $z$ to the $-1/\xi$ power, as claimed.
Consequently, the members of the Generalized Pareto Distribution have three kinds of right tail behavior:
The survival function eventually is zero for negative $\xi.$
The survival function is asymptotic to the $-1/\xi$ power for positive $\xi.$
The survival function is exponential in the sense that it is exactly proportional to $e^{-z}$ when $\xi=0.$
This does not cover the gamut of possible asymptotic tail behaviors of arbitrary distributions. For instance, the log survival function of any Normal distribution is asymptotic to $-z^2,$ which decreases faster than any power or the exponential. Thus, the Generalized Pareto Distribution cannot model just any distribution: it can only model certain distributions with "long" or "heavy" tails. See Example of heavy-tailed distribution that is not long-tailed for more on these concepts.
By definition, a heavy-tailed distribution $F$ is one with a tail that decays slower than any exponential in the sense that for all positive constants $t,$
$$\int_{\mathbb R} e^{tz} \mathrm{d}F(z) = \infty.$$
When $\xi=0$ and $0\lt t \lt 1,$
$$\int_{\mathbb R} e^{tz} \mathrm{d}F_\xi(z) =\int_0^\infty e^{tz} e^{-z} \mathrm{d}z = \frac{1}{1-t}\ne\infty$$
shows that
The Exponential distribution ($\xi=0$) is not heavy-tailed. However, all Generalized Pareto Distributions with $\xi\gt 0$ are heavy-tailed.
In this sense the case $\xi=0$ is a boundary between the heavy power tailed Pareto distributions and their limiting non-heavy Exponential distribution. This is the basis for the intuition that this family, for $\xi\ge 0,$ is a good one for modeling just the heavy-tailed distributions.
Appendix: Technicalities
If these claims about the tails aren't clear, the following is a rigorous demonstration. Note that for any positive numbers $x,$ $\xi,$ and $t$ for which $x \gt 2(1+t)/(\xi t^2),$ the number $u = \xi t x - 2 = 2/t$ is positive. Bounding the exponential $e^u$ below by the first three terms of its Taylor series $1+u+u^2/2!+\cdots$ yields
$$\eqalign{
\exp(\xi t x-2) &\gt 1 + \xi t x - 2 + \frac{(\xi t x - 2)^2}{2!} \\
&= 1 + \xi x(-t + \xi t^2 x/2) \\
&\gt 1 + \xi x(-t + (1+t)) \\
&= 1 + \xi x.
}$$
Upon taking logs and dividing by $\xi$ we obtain
$$t x \gt \frac{2}{\xi} + \frac{1}{\xi} \log(1 + \xi x).$$
For notational convenience write $a = \max(0, 2(1-t)/(\xi t^2)).$ Integration by parts, simple bounding arguments, and judicious introduction of the preceding inequality show
$$\eqalign{
\int_{\mathbb R} e^{tz} \mathrm{d}F_\xi(z) &\ge \int_0^\infty e^{tz} \mathrm{d}F_\xi(z) \\
&= \int_\infty^0 e^{tz}\mathrm{d}(1-F_\xi(z)) \\
&= \lim_{z\to\infty}\left[(e^{tz} (1-F_\xi(z)))\left|_z^0\right. + t\int_0^z e^{tx}(1-F_\xi(x))\mathrm{d}x\right] \\
&\ge t \lim_{z\to\infty}\int_0^z e^{tx}(1-F_\xi(x))\mathrm{d}x \\
&= t \lim_{z\to\infty}\int_0^z \exp\left(tx -\frac{1}{\xi}\log(1+\xi x)\right)\mathrm{d}x \\
&\ge t \lim_{z\to\infty}\int_a^z \exp\left(tx -\frac{1}{\xi}\log(1+\xi x)\right)\mathrm{d}x \\
&\gt t \lim_{z\to\infty}\int_a^z \exp\left(\frac{2}{\xi}\right)\mathrm{d}x \\
&= t \exp\left(\frac{2}{\xi}\right) \lim_{z\to\infty} \int_a^z \mathrm{d}x,
}$$
which diverges, QED.