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: 11413

  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: 182947

  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