SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t (id int primary key identity, xhtml xml);
INSERT t(xhtml) VALUES
('<div></div><div><div><div><div><p>12345</p></div></div></div></div>
<div><div><span>45678<span></span></span></div></div>
<div><div>Test No.</div></div><div><div><div><div><p>4466</p></div></div></div></div>
<div>6789</div>
<p></p><p><i>As per the attachment, no provided because we were supposed to be onboarded.</i></p>');
1 rows affected

SELECT
  t.id,
  CAST(t.xhtml AS nvarchar(max)) AS xhtml,
  t.xhtml.query('//text()').value('text()[1]', 'nvarchar(max)') AS value
FROM t;


id
xhtml
value


1
<div/><div><div><div><div><p>12345</p></div></div></div></div><div><div><span>45678<span/></span></div></div><div><div>Test No.</div></div><div><div><div><div><p>4466</p></div></div></div></div><div>6789</div><p/><p><i>As per the attachment, no provided because we were supposed to be onboarded.</i></p>
1234545678Test No.44666789As per the attachment, no provided because we were supposed to be onboarded.