Convert XML column to Table Data in MS SQL Query.
If your XML column is stored as type XML, then you can use an XPath query to get each column. Like this:
SELECT TheKey,
TheData.value('(/Group1/Title/Name)[1]', 'varchar(100)') AS Name,
TheData.value('(/Group1/Title/Phone)[1]', 'varchar(100)') AS Phone,
TheData.value('(/Group1/Title/Email)[1]', 'varchar(250)') AS Email,
TheData.value('(/Group1/Title/State)[1]', 'varchar(100)') AS [State]
FROM MyTable
If the column MyData
is varchar instead of XML, then you can cast it during the query. Like this:
SELECT TheKey,
Cast(TheData AS XML).value('(/Group1/Title/Name)[1]', 'varchar(100)') AS Name,
Cast(TheData AS XML).value('(/Group1/Title/Phone)[1]', 'varchar(100)') AS Phone,
Cast(TheData AS XML).value('(/Group1/Title/Email)[1]', 'varchar(250)') AS Email,
Cast(TheData AS XML).value('(/Group1/Title/State)[1]', 'varchar(100)') AS [State]
FROM MyTable
Btw, I got this information from this other SO article. (vote it up to make it easier for others to find it) How to query xml column in tsql