SQL Server Querying An XML Field

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

For example

<Meta>
    <From>tst@test.com</From>
    <To>
        <Address>testing@123.com</Address>
        <Address>2@2.com</Address>
    </To>
    <Subject>ESubject Goes Here</Subject>
</开发者_如何学JAVAMeta>

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

From                 To                 Subject
tst@test.com         testing@123.com    Subject Goes Here
tst@test.com         2@2.com            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
tst@test.com         testing@123.com 2@2.com    Subject Goes Here

Thanks

Gav

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.