Electronic – Why is the input reflection signal very weak

Here's what you assert (correctly): -

\$V_{OUT} = V_{IN} \dfrac{X_C}{\sqrt{R^2 + X_C^2}} = V_{IN}\dfrac{X_C}{Z}\$

Let's take the example of R and Xc being equal in magnitude. What does this make the denominator: -

\$\sqrt{R^2 + X_C^2} = \sqrt{2R^2}\$ (because Xc = R)

Therefore the denominator becomes \$\sqrt2\cdot R\$ and the R cancels with the R (or the Xc) in the numerator hence,

\$V_{OUT} = \dfrac{V_{IN}}{\sqrt2}\$

Pythagorous is the reason why the R term and Xc term are squared - an R and a C do not form a potential divider like an R and an R - the impedance of a capacitor is at right angles to the impedance of a resistor.

Your LCR resonator has losses, so it is a decaying sinusoid wave at 5 MHz.
Before it decays to zero, it is re-excited by the negative-going edge of your 1 MHz square wave. Then re-excited by the next positive-going edge of your 1 MHz square wave....and so on.
If you wish to see more of the exponentially-decaying envelope, try changing the frequency of your generator to 714.3 kHz so that you pick out the 7th harmonic. Or try 555555 Hz to see the 9th harmonic. These will be lower amplitude of course, since the excitation energy for each harmonic is smaller.
Be aware that your oscilloscope frequency display is incorrect. It is a difficult wave to measure because of its complex shape, so perhaps frequency error is understandable. The frequency content is exactly five times your source frequency.