Query XML Node Value by Node Attribute

Question

I'm trying to retrieve the value of a xml node that is stored as an xml datatype in a sql server column. For example my xml column data is:

<fields>
<field id="StudentName">John Smith</field>
<field id="StudentID">1310021003</field>
<field id="SchoolName">Little Kid Elementary School</field>
</fields>

I want to retrieve the StudentID. When I run the script below I get null.

select MyColumnName.value('(/fields/field/@StudentID)[1]', 'varchar(20)') as StudentId from MyTable

[bonus question] - I would also like to query the table by studentid if possible, for example: select * from MyTable where MyColumnName.value('(/fields/field/@StudentID)[1]', 'varchar(20)') = '1310021003'

Answer 1

First, this would be test environment:

declare @MyTable table (MyColumnName xml)

insert into @MyTable 
select '<fields>
<field id="StudentName">John Smith</field>
<field id="StudentID">1310021003</field>
<field id="SchoolName">Little Kid Elementary School</field>
</fields>' union all

select '<fields>
<field id="StudentName">John Smith</field>
<field id="StudentID">2343343434</field>
<field id="SchoolName">Little Kid Elementary School</field>
</fields>'

To fetch data from xml, use value() function:

select
    MyColumnName.value('(fields/field[@id="StudentID"]/text())[1]', 'nvarchar(max)') as StudentID
from @MyTable

output:

StudentID
-----------
1310021003
2343343434

To filter by xml data, use exist() function

select
    *
from @MyTable
where MyColumnName.exist('fields/field[@id="StudentID" and text()=1310021003]') = 1

output:

MyColumnName
-----------
<fields>
    <field id="StudentName">John Smith</field>
    <field id="StudentID">1310021003</field>
    <field id="SchoolName">Little Kid Elementary School</field>
</fields>