CODEWITHSUNDEEP
sqlsql-server-2008
user246252
user246252

Reputation:

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

I have an XQuery as under

DECLARE @x XML
SELECT @x = '<PartnerEmails>
<Email>[email protected]</Email>
<Email>[email protected]</Email>
</PartnerEmails>'
SELECT @x.query('data(PartnerEmails/Email)').value('.','varchar(100)') AS Val

Actual Output:

Val
[email protected] [email protected]

Expected Output

[email protected]
[email protected]

i.e. In two different rows.

How to do so?

Upvotes: 3

Views: 2726

Answers (2)

Asif
Asif

Reputation: 2677

DECLARE @x XML
SELECT @x = '<PartnerEmails>
<Email>[email protected]</Email>
<Email>[email protected]</Email>
</PartnerEmails>'

SELECT   ColumnValue.value('.','varchar(1000)')  as Val            
FROM @x.nodes('/PartnerEmails/Email') as Table1(ColumnValue)

Upvotes: 1

marc_s
marc_s

Reputation: 754268

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).

Upvotes: 5

Related Questions