Need a Query to Convert XML Column to Table values

Question

Guys,
I have a below table
UserId
Name
CompanyInfo
1
Johny
1017412
PO Box 19150
Agent City
2
Harry
1017413
PO Box 19151
Agent City1
Can u help me in building a Query from the source Table(above), so that I can insert the values in the target table(below) as below.
UserId
User Name
AgentNumber
AgentAdress
AgentCity
1
Johny
1017412
PO Box 19150
Agent City
2
Harry
1017413
PO Box 19151
Agent City1

Farhan Ahmed · Answer

Example of XML data                     John Doe             555-3421             catman@gmail.com            (+more)              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

UserId	User Name	AgentNumber	AgentAdress	AgentCity
1	Johny	1017412	PO Box 19150	Agent City
2	Harry	1017413	PO Box 19151	Agent City1