Query XML and convert the data into column

  • 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

  • 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/

  • This was removed by the editor as SPAM

  • 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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply