I want to read xml data in the xml file.
I have a table column consist with the xml data. 
if i click on the xml file it will open in the Sql server Management studio.
xml file format shown below.
I want to read only NTDomainName, DatabaseName and ServerName and write that data in the another Table. Table format shown below
NTDomainName | DatabaseName | ServerName
----------
ABC | TestCube1 | SERXYZ
Try this:
declare @xml xml
set @xml = '<event><data name="NTUserName"><value>MyName</value></data><data name="NTDomainName"><value>DomainName</value></data><data name="ServerName"><value>ServerName</value></data></event>'
select [NTDomainName], [DatabaseName], [ServerName] from
(
select [name],[value] from (
select c.value('./@name', 'varchar(100)') [name], c.value('(./value)[1]', 'varchar(100)') [value]
from @xml.nodes('/event/data') as t(c)
) a where [name] in ('NTDomainName', 'DatabaseName', 'ServerName')
) x
pivot (
max(value) for [name] in ([NTDomainName], [DatabaseName], [ServerName])
) as [pivot_Name]
The most inner query will retrieve information from XML, one row for every name attribute value, that you want to retrieve. So, output of this query needs to be pivoted.
i think you look for this:
SELECT * FROM (
SELECT
CAST(f.x.query('data(@name)') as varchar(150)) as data_name,
CAST(f.x.query('data(value)') as varchar(150)) as data_value
FROM @xml.nodes('/event') as t(n)
CROSS APPLY t.n.nodes('data') as f(x)) X
PIVOT (MAX(data_value) FOR data_name IN (NTDomainName, DatabaseName, ServerName)) as pvt
If you do not want to use PIVOT:
DECLARE @DataSource TABLE
(
[ID] TINYINT IDENTITY(1,1)
,[XML] XML
);
INSERT INTO @DataSource ([XML])
VALUES ('<event><data name="SessionID">S1</data><data name="NTUserName">User1</data><data name="DatabaseName">DB1</data><data name="ServerName">SN1</data></event>')
,('<event><data name="SessionID">S1</data><data name="NTUserName">User2</data><data name="DatabaseName">DB2</data><data name="ServerName">SN2</data></event>');
SELECT [ID]
,MAX(CASE wHEN C.value('(./@name)[1]', 'varchar(50)') = 'NTUserName' THEN C.value('(.)[1]', 'varchar(50)') END) AS [NTUserName]
,MAX(CASE wHEN C.value('(./@name)[1]', 'varchar(50)') = 'DatabaseName' THEN C.value('(.)[1]', 'varchar(50)') END) AS [DatabaseName]
,MAX(CASE wHEN C.value('(./@name)[1]', 'varchar(50)') = 'ServerName' THEN C.value('(.)[1]', 'varchar(50)') END) AS [ServerName]
FROM @DataSource
CROSS APPLY [XML].nodes('event/data[@name = "NTUserName" or @name = "DatabaseName" or @name = "ServerName"]') T(c)
GROUP BY [ID];
