Retrieving Multiple XML Rows from a single row with an XML Column Type in SQL Server

Overview
I’m attempting to pull rows from a table with an XML Field Type.  I’m having issues getting multiple values from a single row.
Setup
Run the following query to set up the table:

CREATE TABLE CourseData (

 

CourseId int IDENTITY(1,1) PRIMARY KEY,

 

XmlData XML

 

)

 

INSERT INTO CourseData VALUES

 

(

 

‘<Course xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://someuri.local/CourseRecord.xsd">

 

<SchemaVersion>1.1</SchemaVersion>

 

<CourseData>

 

<CourseCode>AAA999</CourseCode>

 

[…]
Blog Post by: Michael Gerety