Query XML and convert the data into column

• akash_singh

  Ten Centuries

  Points: 1089

  More actions

  July 19, 2018 at 3:35 am

  #373666

  Hello,

  How can i convert below XML data in columns.

  
<event name="QueryEnd" package="AS" timestamp="2018-07-19T09:23:34.611Z">
<data name="CurrentTime">
  <value>2018-07-19T09:23:34.603Z</value>
</data>
<data name="StartTime">
  <value>2018-07-19T09:23:34.462Z</value>
</data>
<data name="EndTime">
  <value>2018-07-19T09:23:34.603Z</value>
</data>
<data name="EventClass">
  <value>10</value>
  <text>QUERY_END</text>
</data>
<data name="EventSubclass">
  <value>3</value>
</data>
<data name="Severity">
  <value>0</value>
</data>
<data name="Success">
  <value>1</value>
</data>
</event>


  I tried to run below SQL query but is there a better way of doing it? #t table contains xml column event_data that has the xml values.
  It is a trace data of .xel file of SQL Server analysis services extended event for tabular model.

  
Select
eventName = event_data.value(N'(event/@name)[1]', N'Varchar(100)'),
EventTimeStamp = event_data.value(N'(event/@timestamp)[1]', N'DateTime'),
CurrentTime = event_data.value(N'(event/data/value)[1]', N'DateTime'),
StartTime = event_data.value(N'(event/data/value)[2]', N'DateTime'),
EventClass = event_data.value(N'(event/data/text)[1]', N'Varchar(100)')
FROM #t


  Regards,
  Akash

• Mike01

  SSChampion

  Points: 11395

  More actions

  July 19, 2018 at 10:55 am

  #1998251

  If the xml is the same, then you could use a dynamic pivot

  
Use TempDB

  drop table if exists test

  create table test (id int identity(1,1),
                 x xml)

  insert into test (x)
values
('<event name="QueryEnd" package="AS" timestamp="2018-07-19T09:23:34.611Z">
<data name="CurrentTime">
<value>2018-07-19T09:23:34.603Z</value>
</data>
<data name="StartTime">
<value>2018-07-19T09:23:34.462Z</value>
</data>
<data name="EndTime">
<value>2018-07-19T09:23:34.603Z</value>
</data>
<data name="EventClass">
<value>10</value>
<text>QUERY_END</text>
</data>
<data name="EventSubclass">
<value>3</value>
</data>
<data name="Severity">
<value>0</value>
</data>
<data name="Success">
<value>1</value>
</data>
</event>
'),
('<event name="QueryEnd" package="AS" timestamp="2018-07-19T09:23:34.611Z">
<data name="CurrentTime">
<value>2018-07-20T09:23:34.603Z</value>
</data>
<data name="StartTime">
<value>2018-07-20T09:23:34.462Z</value>
</data>
<data name="EndTime">
<value>2018-07-20T09:23:34.603Z</value>
</data>
<data name="EventClass">
<value>20</value>
<text>QUERY_END</text>
</data>
<data name="EventSubclass">
<value>2</value>
</data>
<data name="Severity">
<value>2</value>
</data>
<data name="Success">
<value>0</value>
</data>
</event>
')

  DECLARE @cols AS NVARCHAR(MAX),
   @query AS NVARCHAR(MAX)

  SET @cols = STUFF((SELECT distinct ',' + fields.value('@name[1]', 'varchar(30)')
    FROM test
                cross apply x.nodes('//event/data') as xmldata(fields)
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
   ,1,1,'')

  set @query = 'SELECT id,' + @cols + ' from
    (
      select id,
                     fields.value(''@name[1]'', ''varchar(30)'') name,
                     fields.value(''value[1]'', ''varchar(30)'') value
    FROM test
                cross apply x.nodes(''//event/data'') as xmldata(fields)
    ) x
    pivot
    (
      max(value)
      for name in (' + @cols + ')
    ) p '

  exec sp_executeSQL @Query

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• July 19, 2018 at 11:09 am

  This was removed by the editor as SPAM

• Eirikur Eiriksson

  SSC Guru

  Points: 182916

  More actions

  July 19, 2018 at 11:51 pm

  #1998330

  Mike01, you should add the text() function to your code, simplifies the execution plan by bypassing the reconstruct of the XML for the output.
  😎

  The code with the text() function in place.
  
SET @cols = STUFF((SELECT distinct ',' + fields.value('@name[1]', 'varchar(30)')
  FROM test
      cross apply x.nodes('//event/data') as xmldata(fields)
  FOR XML PATH(''), TYPE
  ).value('(./text())[1]', 'NVARCHAR(MAX)')
  ,1,1,'')

  set @query = 'SELECT id,' + @cols + ' from
  (
  select id,
       fields.value(''@name[1]'', ''varchar(30)'') name,
        fields.value(''(value/text())[1]'', ''varchar(30)'') value
  FROM test
      cross apply x.nodes(''//event/data'') as xmldata(fields)
  ) x
  pivot
  (
   max(value)
  for name in (' + @cols + ')
  ) p '

  exec sp_executeSQL @Query