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