parse xml data field with sql query

select x.c.value(‘(action[@name=”username”]/value)[1]’, ‘varchar(100)’) AS username,
x.c.value(‘(action[@name=”database_name”]/value)[1]’, ‘varchar(100)’) AS dbname,
x.c.value(‘(data[@name=”statement”]/value)[1]’, ‘varchar(max)’) AS sqlstatement,
x.c.value(‘(action[@name=”collect_system_time”]/value)[1]’, ‘datetime’) AS date
from xmltable as c
outer apply c.event_data_XML.nodes(‘event’) as x(c)

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.