July 19, 2018 at 3:35 am
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
July 19, 2018 at 10:55 am
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
July 19, 2018 at 11:51 pm
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