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>');
5 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>
12345

2
<div><div><span>45678<span/></span></div></div>
45678

3
<div><div>Test No.</div></div><div><div><div><div><p>4466</p></div></div></div></div>
Test No.4466

4
<div>6789</div>
6789

5
<p/><p><i>As per the attachment, no provided because we were supposed to be onboarded.</i></p>
As per the attachment, no provided because we were supposed to be onboarded.

id	xhtml	value
1	<div/><div><div><div><div><p>12345</p></div></div></div></div>	12345
2	<div><div><span>45678<span/></span></div></div>	45678
3	<div><div>Test No.</div></div><div><div><div><div><p>4466</p></div></div></div></div>	Test No.4466
4	<div>6789</div>	6789
5	<p/><p><i>As per the attachment, no provided because we were supposed to be onboarded.</i></p>	As per the attachment, no provided because we were supposed to be onboarded.