How to extract list of values into rows from XML using XQuery?

sql, sql-server-2008

Solution

Use this:

SELECT 
    node.value('.','varchar(100)') AS Val
FROM
    @x.nodes('/PartnerEmails/Email') AS PE(Node)

Since you have multiple nodes inside `<PartnerEmails>`, you need to use the `.nodes()` function to create an "inline" table of XML fragments - each "row" in that table contains one `<Email>` node which you can then query on (and extract the contents of the XML node).

Problem

I have an XQuery as under ``` DECLARE @x XML SELECT @x = '<PartnerEmails> <Email>a@xxxx.com</Email> <Email>b@xxxx.com</Email> </PartnerEmails>' SELECT @x.query('data(PartnerEmails/Email)').value('.','varchar(100)') AS Val ``` Actual Output: ``` Val a@xxxx.com b@xxxx.com ``` Expected Output ``` a@xxxx.com b@xxxx.com ``` i.e. In two different rows. How to do so?

Original source