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