SQL Server Querying An XML Field

Question

I have a table that contains some meta data in an XML field.

For example

<Meta>
    <From>[email protected]</From>
    <To>
        <Address>[email protected]</Address>
        <Address>[email protected]</Address>
    </To>
    <Subject>ESubject Goes Here</Subject>
</Meta>

I want to then be able to query this field to return the following results

From                 To                 Subject
[email protected]         [email protected]    Subject Goes Here
[email protected]         [email protected]            Subject Goes Here

I've written the following query

SELECT
    MetaData.query('data(/Meta/From)') AS [From],
    MetaData.query('data(/Meta/To/Address)') AS [To],
    MetaData.query('data(/Meta/Subject)') AS [Subject]
FROM 
    Documents 

However this only returns one record for that XML field. It combines both the 2 addresses into one result. Is it possible for split these on to separate records?

The result I'm getting is

From                 To                         Subject
[email protected]         [email protected] [email protected]    Subject Goes Here

Thanks

Gav

Answer 1

You need to return the XML and then parse it using something like the following code:

StringReader stream = new StringReader(stringFromSQL);
XmlReader reader = XmlReader.Create(stream);

while (reader.Read())
{
    // Do stuff
}

Where stringFromSQL is the whole string as read from your table.