How do I query an XML field in SQL Server 2005 so that it behaves like a table?

Question

I have a table that contains a user_id, and an XML field containing basically a series of values. I need to run a report against the data in the XML, but for the life of me I cannot get the XML out of the field in any useful fashion.

Here's the schema for the table:

|-----------------|
| type |   name   |
|-----------------|
|  int |  user_id |
|  XML |    crest |
|-----------------|

And here's a sample of the XML stored in the faves field:

<crest xmlns="SANITIZED" keyAttrib="slot" valueType="String">
    <badge slot="0">Money</badge>
    <badge slot="1">Independence</badge>
    <badge slot="2">Power</badge>
</crest>

In order to most easily generate the report I need, an "associative" table, listing the user_ids and each badge they earned, 1 badge per row, so each user would have up to 3 rows. I am trying to get the data into a form like this:

|---------------------|
|   type   |   name   |
|---------------------|
|      int |  user_id |
|  varchar |    badge |
|---------------------|

I have tried everything I can think of and scoured the Internet as well as the MSDN docs to no avail. No matter what I try, I cannot seem to get the data massaged into the form I want. I am sure it is possible, but I am at my wit's end as to how to get this to work. Any help, tips, or suggestions would be greatly appreciated!

Answer 1

You should be able to do something like this:

;WITH XMLNAMESPACES('http://schemas.test.com' AS ns)
    SELECT
        tbl.user_id,
        t.badges.value('@slot', 'int') AS 'Slot',
        t.badges.value('.', 'varchar(60)') AS 'Badge'
    FROM
        dbo.YourTable tbl
    CROSS APPLY
        tbl.Crest.nodes('(/ns:crest/ns:badge)') AS t(badges)

Basically join your base table "YourTable" with a collection of XML nodes (assuming your XML namespace would be "http://schemas.test.com"). From this list of nodes, you just pluck out the bits and pieces you need, as INT, VARCHAR or whatever type it might be.