Monday, September 23, 2013

How to Retrieve data from XML field column in SQL server

I have a table with column of type xml. the column data it below:
<fields>
<field key="Public Email" />
<field key="Gender">Female</field>
<field key="Language">en-US</field>
<field key="First Name" />
<field key="Last Name" />
</fields>

QUERY

SELECT userid
,fields.value('(/fields/field[@key="First Name"])[1]', 'varchar(50)') AS FirstName
,fields.value('(/fields[@key="Last Name"])[1]', 'varchar(50)') AS LastName
,fields.value('(/fields/field[@key="Public Email"])[1]', 'nvarchar(100)') AS PublicEmail
,fields.value('(/fields/field[@key="Language"])[1]', 'varchar(20)') AS Language
FROM UserData