SqlServer XML操作示例

示例1：

declare @doc xml
set @doc ='
<event name="sql_batch_completed" package="sqlserver" timestamp="2020-08-19T06:41:42.542Z">
  <data name="cpu_time">
    <value>141000</value>
  </data>
  <data name="duration">
    <value>2947856</value>
  </data>
  <data name="physical_reads">
    <value>12517</value>
  </data>
  <data name="logical_reads">
    <value>10411</value>
  </data>
  <data name="writes">
    <value>0</value>
  </data>
  <data name="row_count">
    <value>0</value>
  </data>
  <data name="result">
    <value>2</value>
    <text>Abort</text>
  </data>
  <data name="batch_text">
    <value>SELECT  * FROM match_nndouble </value>
  </data>
  <action name="task_time" package="sqlos">
    <value>28573288</value>
  </action>
  <action name="database_name" package="sqlserver">
    <value>test</value>
  </action>
  <action name="nt_username" package="sqlserver">
    <value>WIN-OFM2A36CRMD\admin</value>
  </action>
  <action name="sql_text" package="sqlserver">
    <value>SELECT  * FROM match_nndouble </value>
  </action>
  <action name="transaction_id" package="sqlserver">
    <value>0</value>
  </action>
  <action name="username" package="sqlserver">
    <value>WIN-OFM2A36CRMD\admin</value>
  </action>
</event>'
declare @xmlhandle int
EXEC sp_xml_preparedocument @xmlhandle OUTPUT, @doc;
select
@doc.value('(/event/@timestamp)[1]','datetime') as record_time, --获取最上方标题行的内容
@doc.value('(/event/@name)[1]','nvarchar(128)') as operation_name, --获取最上方标题行的内容
--data.value('(/event/data[@name="cpu_time"]/value)[1]','int')/1000 as 'cpu_time(ms)',--获得 event=>data name=cpu_time 的 value
@doc.value('(/event/data[@name="duration"]/value)[1]','int')/1000 as 'exec_time(ms)',--获得 event=>data name=duration 的 value
--data.value('(/event/data[@name="physical_reads"]/value)[1]','int') as 'physical_reads',--获得 event=>data name=physical_reads 的 value
--data.value('(/event/data[@name="logical_reads"]/value)[1]','int') as 'logical_reads',--获得 event=>data name=logical_reads 的 value
--data.value('(/event/data[@name="writes"]/value)[1]','int') as 'writes',--获得 event=>data name=writes 的 value
@doc.value('(/event/data[@name="row_count"]/value)[1]','int') as 'row_count',--获得 event=>data name=row_count 的 value
@doc.value('(/event/data[@name="result"]/value)[1]','int') as 'result_flag',--获得 event=>data name=result 的 value
@doc.value('(/event/data[@name="result"]/text)[1]','nvarchar(128)') as 'result_desc',--获得 event=>data name=result 的 text
@doc.value('(/event/data[@name="batch_text"]/value)[1]','nvarchar(max)') as 'batch_text',--获得 event=>data name=batch_text 的 text

@doc.value('(/event/action[@name="sql_text"]/value)[1]','nvarchar(4000)') as 'current_sql',--获得 event=>action name=sql_text 的 value
--data.value('(/event/action[@name="task_time"]/value)[1]','int')/1000 as 'task_time(ms)',--获得 event=>action name=task_time 的 value
@doc.value('(/event/action[@name="database_name"]/value)[1]','nvarchar(400)') as 'database_name',--获得 event=>action name=database_name 的 value
@doc.value('(/event/action[@name="transaction_id"]/value)[1]','nvarchar(400)') as 'transaction_id',--获得 event=>action name=transaction_id 的 value
@doc.value('(/event/action[@name="username"]/value)[1]','nvarchar(400)') as 'username',--获得 event=>action name=transaction_id 的 value
@doc.value('(/event/action[@name="nt_username"]/value)[1]','nvarchar(400)') as 'nt_username'--获得 event=>action name=transaction_id 的 value
from (select *  from openxml(@xmlhandle,'/event',1)) tt

执行结果：

 

 

 

 

 

示例2:

declare @ids xml,@doc1 int
set @ids = '<doc><id>1</id><id>2</id><id>3</id></doc>'
EXEC sp_xml_preparedocument @doc1 OUTPUT, @ids;
select BillNos.value('(text())[1]','int') as ID from @ids.nodes('/doc/id') V(BillNos)