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

Question

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?

marc_s · Accepted Answer · 2012-06-08 12:53:13Z

5

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

answered Jun 8, 2012 at 12:53

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Asif · Accepted Answer · 2012-06-08 13:04:57Z

1

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)

answered Jun 8, 2012 at 13:04

Asif

2,67721 silver badges25 bronze badges

1 Comment

Curious Watcher Over a year ago

Great answer, as it provides both input data definition as well as recipe. @Asif, shall we update answer one and get rid of this one? It is nearly a duplicate, plus the first one has an explanation.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related