（4.41）sql server如何把xml转换成表格数据？

【0】基本案例

declare @temp xml;
set @temp='<root><note>
<to>George</to>
<from>John</from>
<heading>Reminder</heading>
<body>Dont forget the meeting!</body>
</note>
<note>
<to>tom</to>
<from>cat</from>
<heading>test</heading>
<body>test 123</body>
</note>
</root>';
select [to]=o.value('to[1]','nvarchar(500)'),
               [from]=o.value('from[1]','nvarchar(500)'),
               [heading]=o.value('heading[1]','nvarchar(500)'),
               [body]=o.value('body[1]','nvarchar(500)')               
             from (select x=@temp) a
             cross apply x.nodes('root/note') x(o)

【XML与表格的互相转换】

【1】xml测试数据

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

【2】xml转换成表格形式查看

with d as (
SELECT CONVERT(XML,event_data) AS data
from sys.Fn_xe_file_target_read_file(N'E:\dba_tools\eventlog\slow_query_0_132422809173040000.xel',NULL,NULL,NULL)
)
select
data.value('(/event/@timestamp)[1]','datetime') as record_time, --获取最上方标题行的内容
data.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
data.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
data.value('(/event/data[@name="row_count"]/value)[1]','int') as 'row_count',--获得 event=>data name=row_count 的 value
data.value('(/event/data[@name="result"]/value)[1]','int') as 'result_flag',--获得 event=>data name=result 的 value
data.value('(/event/data[@name="result"]/text)[1]','nvarchar(128)') as 'result_desc',--获得 event=>data name=result 的 text
data.value('(/event/data[@name="batch_text"]/value)[1]','nvarchar(max)') as 'batch_text',--获得 event=>data name=batch_text 的 text

data.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
data.value('(/event/action[@name="database_name"]/value)[1]','nvarchar(400)') as 'database_name',--获得 event=>action name=database_name 的 value
data.value('(/event/action[@name="transaction_id"]/value)[1]','nvarchar(400)') as 'transaction_id',--获得 event=>action name=transaction_id 的 value
data.value('(/event/action[@name="username"]/value)[1]','nvarchar(400)') as 'username',--获得 event=>action name=transaction_id 的 value
data.value('(/event/action[@name="nt_username"]/value)[1]','nvarchar(400)') as 'nt_username'--获得 event=>action name=transaction_id 的 value
from d

结果：

回到顶部

XML本身的5大操作

1.xml.exist
   输入为XQuery表达式，返回0，1或是Null。0表示不存在，1表示存在，Null表示输入为空
2.xml.value
   输入为XQuery表达式，返回一个SQL Server标量值
3.xml.query
   输入为XQuery表达式，返回一个SQL Server XML类型流
4.xml.nodes
   输入为XQuery表达式，返回一个XML格式文档的一列行集
5.xml.modify
   使用XQuery表达式对XML的节点进行insert , update 和 delete 操作。

下面通过例子对上面的五种操作进行说明：

测试数据

declare @XMLVar xml = '

<catalog> 

       <book category="ITPro">   

              <title>Windows Step By Step</title>   

              <author>Bill Zack</author>   

              <price>49.99</price> 

       </book> 

       <book category="Developer">   

              <title>Developing ADO .NET</title>   

              <author>Andrew Brust</author>   

              <price>39.93</price> 

       </book> 

       <book category="ITPro">   

              <title>Windows Cluster Server</title>   

              <author>Stephen Forte</author>   

              <price>59.99</price> 

       </book>

</catalog>'

1.xml.exist

   select @XMLVar.exist('/catalog/book')-----返回1  

   select @XMLVar.exist('/catalog/book/@category')-----返回1  

   select @XMLVar.exist('/catalog/book1')-----返回0  

   set @XMLVar = null  

   select @XMLVar.exist('/catalog/book')-----返回null

2.xml.value

   select @XMLVar.value('/catalog[1]/book[1]','varchar(MAX)')  

   select @XMLVar.value('/catalog[1]/book[2]/@category','varchar(MAX)')  

   select @XMLVar.value('/catalog[2]/book[1]','varchar(MAX)')

结果集为：

Windows Step By StepBill Zack49.99 Developer NULL

3.xml.query

 select @XMLVar.query('/catalog[1]/book')   

   select @XMLVar.query('/catalog[1]/book[1]')  

   select @XMLVar.query('/catalog[1]/book[2]/author')

   结果集分别为： 
<book category="ITPro"> 
  <title>Windows Step By Step</title> 
  <author>Bill Zack</author> 
  <price>49.99</price> 
</book> 
<book category="Developer"> 
  <title>Developing ADO .NET</title> 
  <author>Andrew Brust</author> 
  <price>39.93</price> 
</book> 
<book category="ITPro"> 
  <title>Windows Cluster Server</title> 
  <author>Stephen Forte</author> 
  <price>59.99</price> 
</book>


<book category="ITPro"> 
  <title>Windows Step By Step</title> 
  <author>Bill Zack</author> 
  <price>49.99</price> 
</book>


<author>Andrew Brust</author>

4.xml.nodes

   select T.c.query('.') as result from @XMLVar.nodes('/catalog/book') as T(c)  

   select T.c.query('title') as result from @XMLVar.nodes('/catalog/book') as T(c)

结果集分别为：

<book category="ITPro"><title>Windows Step By Step</title><author>Bill …………

<book category="Developer"><title>Developing ADO .NET</title><author>Andrew …………
<book category="ITPro"><title>Windows Cluster Server</title><author>Stephen …………


<title>Windows Step By Step</title>
<title>Developing ADO .NET</title>
<title>Windows Cluster Server</title>

5.xml.modify

SQL Server 2005/2008增加了对XML数据的支持，同时也新增了几种操作XML的方法，本文主要以SQL Server 2008为例介绍如何对XML数据进行insert、update、delete。

SQL Server中新增加了XML.Modify()方法，分别为xml.modify(insert),xml.modify(delete),xml.modify(replace)对应XML的插入，删除和修改操作。

本文以下面XML为例，对三种DML进行说明：

declare @XMLVar xml = '
<catalog>
  <book category="ITPro">
    <title>Windows Step By Step</title>
    <author>Bill Zack</author>
    <price>49.99</price>
  </book>
  <book category="Developer">
    <title>Developing ADO .NET</title>
    <author>Andrew Brust</author>
    <price>39.93</price>
  </book>
  <book category="ITPro">
    <title>Windows Cluster Server</title>
    <author>Stephen Forte</author>
    <price>59.99</price>
  </book>
</catalog>
'

回到顶部

深入 XML.Modify

回到顶部

1.XML.Modify(Insert)语句介绍

A.利用as first,at last,before,after 四个参数将元素插入指定的位置

set @XMLVar.modify(
    'insert <first name="at first" /> as first into (/catalog[1]/book[1])')
set @XMLVar.modify(
    'insert <last name="at last"/> as last into (/catalog[1]/book[1])')
set @XMLVar.modify(
    'insert <before name="before"/> before (/catalog[1]/book[1]/author[1])')
set @XMLVar.modify(
    'insert <after name="after"/> after (/catalog[1]/book[1]/author[1])')
SELECT @XMLVar.query('/catalog[1]/book[1]');

结果集为：

   1:  <book category="ITPro">
   2:    <first name="at first" />
   3:    <title>Windows Step By Step</title>
   4:    <before name="before" />
   5:    <author>Bill Zack</author>
   6:    <after name="after" />
   7:    <price>49.99</price>
   8:    <last name="at last" />
   9:  </book>

B.将多个元素插入文档中

--方法一：利用变量进行插入

DECLARE @newFeatures xml;
SET @newFeatures = N';        
<first>one element</first>          
<second>second element</second>'
SET @XMLVar.modify('           )      
insert sql:variable("@newFeatures")           
into (/catalog[1]/book[1])'

--方法二：直接插入

set @XMLVar.modify(')
insert (<first>one element</first>,<second>second element</second>) 
into (/catalog[1]/book[1]/author[1])'
SELECT @XMLVar.query('/catalog[1]/book[1]');

结果集为：

  1:  <book category="ITPro">
   2:    <title>Windows Step By Step</title>
   3:    <author>Bill Zack
   4:      <first>one element</first>
   5:      <second>second element</second>
   6:    </author>
   7:    <price>49.99</price>
   8:    <first>one element</first>
   9:    <second>second element</second>
  10:  </book>

C.将属性插入文档中

--使用变量插入

declare @var nvarchar(10) = '变量插入'
set @XMLVar.modify(
'insert (attribute var {sql:variable("@var")}))
into (/catalog[1]/book[1])'

--直接插入

set @XMLVar.modify(
'insert (attribute name {"直接插入"}))
into (/catalog[1]/book[1]/title[1])'

--多值插入

set @XMLVar.modify(
'insert (attribute Id {"多值插入1"},attribute name {"多值插入2"}) )
into (/catalog[1]/book[1]/author[1])'
SELECT @XMLVar.query('/catalog[1]/book[1]');

结果集为：

   1:  <book category="ITPro" var="变量插入">
   2:    <title name="直接插入">Windows Step By Step</title>
   3:    <author Id="多值插入1" name="多值插入2">Bill Zack</author>
   4:    <price>49.99</price>
   5:  </book>

D.插入文本节点

set @XMLVar.modify(
'insert text{"at first"} as first)
into (/catalog[1]/book[1])'
SELECT @XMLVar.query('/catalog[1]/book[1]');

结果集为：

 1:  <book category="ITPro">
   2:    at first
   3:    <title>Windows Step By Step</title>
   4:    <author>Bill Zack</author>
   5:    <price>49.99</price>
   6:  </book>

注意：插入本文同样具体 as first，as last，before，after四种选项，可以参考A中的使用方法

E.插入注释节点

set @XMLVar.modify(
'insert <!--插入评论-->)
before (/catalog[1]/book[1]/title[1])'
SELECT @XMLVar.query('/catalog[1]/book[1]');

结果集为：

 1:  <book category="ITPro">
   2:    <!--插入评论-->
   3:    <title>Windows Step By Step</title>
   4:    <author>Bill Zack</author>
   5:    <price>49.99</price>
   6:  </book>

注意插入注释节点同样具体 as first，as last，before，after四种选项，可以参考A中的使用方法

F.插入处理指令

set @XMLVar.modify(
'insert <?Program "Instructions.exe" ?>)
before (/catalog[1]/book[1]/title[1])'
SELECT @XMLVar.query('/catalog[1]/book[1]');

结果集为：
   1:  <book category="ITPro">
   2:    <?Program "Instructions.exe" ?>
   3:    <title>Windows Step By Step</title>
   4:    <author>Bill Zack</author>
   5:    <price>49.99</price>
   6:  </book>

注意插入处理指令同样具体 as first，as last，before，after四种选项，可以参考A中的使用方法

G.根据 if 条件语句进行插入

set @XMLVar.modify(
'insert )
if (/catalog[1]/book[1]/title[2]) then
    text{"this is a 1 step"}
else ( text{"this is a 2 step"} )
    into (/catalog[1]/book[1]/price[1])'
SELECT @XMLVar.query('/catalog[1]/book[1]');

结果集为：

   1:  <book category="ITPro">
   2:    <title>Windows Step By Step</title>
   3:    <author>Bill Zack</author>
   4:    <price>49.99this is a 2 step</price>
   5:  </book>

回到顶部

2.XML.Modify(delete)语句介绍

--删除属性

set @XMLVar.modify('delete /catalog[1]/book[1]/@category')

--删除节点

set @XMLVar.modify('delete /catalog[1]/book[1]/title[1]')

--删除内容

set @XMLVar.modify('delete /catalog[1]/book[1]/author[1]/text()')

--全部删除

set @XMLVar.modify('delete /catalog[1]/book[2]')

SELECT @XMLVar.query('/catalog[1]');

结果集为：

   1:  <catalog>

   2:    <book>

   3:      <author />

   4:      <price>49.99</price>

   5:    </book>

   6:    <book category="ITPro">

   7:      <title>Windows Cluster Server</title>

   8:      <author>Stephen Forte</author>

   9:      <price>59.99</price>

  10:    </book>

  11:  </catalog>

回到顶部

3.XML.Modify(replace)语句介绍

--替换属性

set @XMLVar.modify('replace value of(/catalog[1]/book[1]/@category))

 with ("替换属性")'

--替换内容

set @XMLVar.modify('replace value of(/catalog[1]/book[1]/author[1]/text()[1]))

 with("替换内容")'

--条件替换

set @XMLVar.modify('replace value of (/catalog[1]/book[2]/@category))

with(

if(count(/catalog[1]/book)>4) then

    "条件替换1"

else

    "条件替换2")'

SELECT @XMLVar.query('/catalog[1]');

结果集为：

   1:  <catalog>

   2:    <book category="替换属性">

   3:      <title>Windows Step By Step</title>

   4:      <author>替换内容</author>

   5:      <price>49.99</price>

   6:    </book>

   7:    <book category="条件替换2">

   8:      <title>Developing ADO .NET</title>

   9:      <author>Andrew Brust</author>

  10:      <price>39.93</price>

  11:    </book>

  12:    <book category="ITPro">

  13:      <title>Windows Cluster Server</title>

  14:      <author>Stephen Forte</author>

  15:      <price>59.99</price>

  16:    </book>

  17:  </catalog>

posted @ 2020-04-17 16:17 郭大侠1 阅读(1284) 评论(0) 编辑收藏举报

刷新页面返回顶部

（4.41）sql server如何把xml转换成表格数据？

【0】基本案例

【XML与表格的互相转换】

【1】xml测试数据

【2】xml转换成表格形式查看

XML本身的5大操作

测试数据

1.xml.exist

2.xml.value

3.xml.query

4.xml.nodes

5.xml.modify

深入 XML.Modify

1.XML.Modify(Insert)语句介绍

B.将多个元素插入文档中

C.将属性插入文档中

D.插入文本节点

E.插入注释节点

F.插入处理指令

G.根据 if 条件语句进行插入

2.XML.Modify(delete)语句介绍

3.XML.Modify(replace)语句介绍

公告