Extracting values from XML nodes and present in comma separated values using XQuery in SQL Server

Niladri.Biswas
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 13643 red flag

In this article we will look into some methods as how to extract values from XML nodes and present in comma separated values using XQuery


 Download source code for Extracting values from XML nodes and present in comma separated values using XQuery in SQL Server

Introduction

In a recent requirement of mine, there has been a problem being stated as under

There are many employees into the system and they can have multiple emails.The emails are being stored into xml nodes as under

<EmployeeEmails> 
<Email>FirstEmail@employee.com</Email>
<Email>SecondEmail@employee.com</Email>
<Email>ThirdEmail@employee.com</Email>
</EmployeeEmails>

What we need to generate is a comma separated list as given below.

Emails
------
FirstEmail@employee.com,SecondEmail@employee.com,ThirdEmail@employee.com

Solution 1:Using Node function

Let us first try to extract the node values by using the .nodes() function as shown under

DECLARE @InputData XML = '<EmployeeEmails> 
<Email>FirstEmail@employee.com</Email>
<Email>SecondEmail@employee.com</Email>
<Email>ThirdEmail@employee.com</Email>
</EmployeeEmails>'

SELECT EmailColumn.value('.','varchar(100)') AS EmailData
FROM @InputData.nodes('/EmployeeEmails/Email') AS EmailTable(EmailColumn)

/*
EmailData
-------------------------
FirstEmail@employee.com
SecondEmail@employee.com
ThirdEmail@employee.com
*/

The .nodes() function creates an "inline" table of XML fragments for each "row" in the table that contains one <Email> node. We can then query on and extract the contents of the XML node.

Once we get the row-wise records,next we can apply FOR XML PATH to get the rows transformed into columns with comma seperated list as shown under

DECLARE @InputData XML ='<EmployeeEmails> 
<Email>FirstEmail@employee.com</Email>
<Email>SecondEmail@employee.com</Email>
<Email>ThirdEmail@employee.com</Email>
</EmployeeEmails>'
;WITH EmailCTE AS
(
SELECT EmailColumn.value('.','varchar(100)') AS EmailData
FROM @InputData.nodes('/EmployeeEmails/Email') AS EmailTable(EmailColumn)
)

SELECT ',' + CAST(EmailData AS VARCHAR(MAX))
FROM EmailCTE
FOR XML PATH('')

/*
XML_F52E2B61-18A1-11d1-B105-00805F49916B
-------------------------------------------------------------------------
,FirstEmail@employee.com,SecondEmail@employee.com,ThirdEmail@employee.com
*/

We can make out that the data is not formatted properly as there is a comma in the before any data.Just to give a final touch to the solution, we can employ Stuff function inorder to extract the extra comma at the beginning.The full query with desired output is as under

DECLARE @InputData XML = '<EmployeeEmails> 
<Email>FirstEmail@employee.com</Email>
<Email>SecondEmail@employee.com</Email>
<Email>ThirdEmail@employee.com</Email>
</EmployeeEmails>'
;WITH EmailCTE AS
(
SELECT EmailColumn.value('.','varchar(100)') AS EmailData
FROM @InputData.nodes('/EmployeeEmails/Email') AS EmailTable(EmailColumn)
)
SELECT Emails = STUFF(EmailList,1,1,'') from
(
SELECT ',' + CAST(EmailData AS VARCHAR(MAX))
FROM EmailCTE
FOR XML PATH('')
)X(EmailList)

/*
Emails
-------------------------------------------------------------------------
FirstEmail@employee.com,SecondEmail@employee.com,ThirdEmail@employee.com
*/

Solution 2:Using Query function

Let us first execute the below code

DECLARE @InputData XML = '<EmployeeEmails> 
<Email>FirstEmail@employee.com</Email>
<Email>SecondEmail@employee.com</Email>
<Email>ThirdEmail@employee.com</Email>
</EmployeeEmails>'

SELECT @InputData.query('data(EmployeeEmails/Email)') As Emails

/*
Emails
------------------------------------------------------------------------
FirstEmail@employee.com SecondEmail@employee.com ThirdEmail@employee.com

*/

The above query returns a space separated list with the values stored in the XML fragment.To fulfill our objective we can use Replace function to replace the spaces with comma's. But the query() method returns the data of XML type which will hinder to do so.Henceforth, first we need to convert the XML data type to varchar and then we can use the Replace function.

The below query will,however, convert the XML result to string result

DECLARE @InputData XML = '<EmployeeEmails> 
<Email>FirstEmail@employee.com</Email>
<Email>SecondEmail@employee.com</Email>
<Email>ThirdEmail@employee.com</Email>
</EmployeeEmails>'
SELECT @InputData.query('data(EmployeeEmails/Email)').value('.','varchar(100)') As Emails

Finally, we can use the replace function to accomplish the goal

DECLARE @InputData XML = '<EmployeeEmails> 
<Email>FirstEmail@employee.com</Email>
<Email>SecondEmail@employee.com</Email>
<Email>ThirdEmail@employee.com</Email>
</EmployeeEmails>'
SELECT REPLACE
(
@InputData.query('data(EmployeeEmails/Email)').value('.','varchar(100)')

,' ' -- replace the white spaces

,',' -- replace the white spaces with commas
) AS Emails

/*
Emails
-------------------------------------------------------------------------
FirstEmail@employee.com,SecondEmail@employee.com,ThirdEmail@employee.com

*/

Conclusion

So in this short article, we have seen as how to parse the XML nodes inorder to obtain the data by using Nodes() and Query() functions.Hope this will be helpful to others.Zipped file is attached.

Page copy protected against web site content infringement by Copyscape

About the Author

Niladri.Biswas
Full Name: Niladri Biswas
Member Level: Platinum
Member Status: Member
Member Since: 10/25/2010 11:04:24 AM
Country: India
Best Regards, Niladri Biswas
http://www.dotnetfunda.com
Technical Lead at HCL Technologies

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)