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

Question

Hi,

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

Try this:

SELECT * FROM t1
WHERE content.value('(//firstName)[1]', 'varchar(MAX)') = 'aaa'

Answer 2

Stealing Dusty's answer, then the following works, if you eliminate the new lines between the opening quotes and the xml declarations in your insert statements:

SELECT * FROM t1
WHERE CONVERT(xml,content).value('(//firstName)[1]', 'varchar(8000)') = 'aaa'

Answer 3

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.

Answer 4

Here's a solution which uses variables for searching the content, so that you don't have to concatenate the exist query.

DECLARE @t1 table (
    id INT IDENTITY(1, 1) PRIMARY KEY,
    content xml
);
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>');

DECLARE @firstName nvarchar(50);
DECLARE @lastName nvarchar(50);
SELECT @firstName = 'aaa', @lastName = 'bbb';
SELECT * FROM @t1 t WHERE t.[content].[exist]('//person[firstName=sql:variable("@firstName") and lastName=sql:variable("@lastName")]')=1;