Decomposing an XML document into relational tables, rows and columns is commonly called "shredding", and you can find various articles on this, although I have found some of them to be a bit confusing myself.

If you have the XML in a variable, as you indicated, then one method that worked for me is described in the vocabulary example in this article, although that was tested in SQL Server 2008 R2, not 2005. The example covers splitting the data into two related tables, which may be a more typical use case than a single table.