In SQL Server, xml data are saved in a text column, how to query from this column?

Question

In my situation, xml data are saved in a text column, how to query this against this column? For example:

create table t1
(
    id INT IDENTITY(1, 1) PRIMARY KEY,
    content text
)


insert into t1(content) values ('<?xml version="1.0"?>
<people>
    <person>
        <firstName>ooo</firstName>
        <lastName>ppp</lastName>
    </person>
</people>
')
insert into t1(content) values ('<?xml version="1.0"?>
<people>
    <person>
        <firstName>mmm</firstName>
        <lastName>nnn</lastName>
    </person>
    <person>
        <firstName>aaa</firstName>
        <lastName>bbb</lastName>
    </person>
</people>
')
insert into t1(content) values ('<?xml version="1.0"?>
<people>
    <person>
        <firstName>aaa</firstName>
        <lastName>bbb</lastName>
    </person>
</people>
')

How to get all rows that have a person, whose first name is aaa and last name is bbb?

---

Edit:

I changed the insert statement a little, so that you can cast it to XML type directly.

Notes:

The content column is of type text, since it's an example to represent my actual problem. I'm working on a legacy project.

The second row and third row have a person whose first name is aaa and last name is bbb, I just need these rows.

Answer 1

The following should do:

SELECT
 *
FROM
(
    SELECT 
        CAST([content] AS XML) AS xmlcontent 
    FROM 
        t1
) det
WHERE 
xmlcontent.exist('//person[firstName[text()="aaa"] and lastName[text()="bbb"]]') = 1

I also added this entry to your set:

insert into t1(content) values ('<?xml version="1.0"?>
<people>
    <person>
        <firstName>ooo</firstName>
        <lastName>ppp</lastName>
    </person>
    <person>
        <firstName>aaa</firstName>
        <lastName>ppp</lastName>
    </person>
    <person>
        <firstName>ooo</firstName>
        <lastName>bbb</lastName>
    </person>
</people>
')

to show that if there is both a person matching the first name and a different person matching the last name, it does not count it as a match.