Read XML data in SQL Server 2012

Question

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

score 2 · Answer 1 · answered Mar 02 '18 at 07:01

2

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.

answered Mar 02 '18 at 07:01

Michał Turczyn

32,028
14
47
69

Hi, thanks for your reply. event_data_XML column has 4 rows containing with xml files. How to read one by one and display in the one select statement. – Gayan Hemachandra Mar 02 '18 at 10:29
@GayanHemachandra If my answer satisfied your erquirements, please accept the answer :) – Michał Turczyn Mar 02 '18 at 10:33
Hi, thanks for your reply. event_data_XML column has 4 rows containing with xml files. How to read one by one and display in the one select statement. – Gayan Hemachandra Mar 02 '18 at 10:38
@GayanHemachandra That wasn't your original question. You asked how to pull data from XML. Try finishing on your own, not asking for entire solution. This isn't free code providing service. – Michał Turczyn Mar 02 '18 at 11:02

score 1 · Answer 2 · answered Mar 02 '18 at 07:04

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

score 0 · Answer 3 · answered Mar 02 '18 at 07:12

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

Hi, thanks for your reply. event_data_XML column has 4 rows containing with xml files. How to read one by one and insert into the @DataSource table — Gayan Hemachandra, Mar 02 '18 at 10:46
@GayanHemachandra Not sure, I am understanding the issue. Could you provided sample data and desire output? — gotqn, Mar 02 '18 at 10:49

Read XML data in SQL Server 2012

3 Answers3