SQL Server 2005 XML 操作总结（转）

于 2012-04-17 15:18:57 发布
阅读量171 收藏
点赞数
分类专栏： sqlserver
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/li_hy2002/article/details/84187979
版权

转载网上的，方便以后查找

 

后续文章中的操作全部都针对该示例

 

 
 
 
  declare 
    
  @data 
   xml
 
  set 
    
  @data 
  = 
  ' 
  
<bookstore>
<book category="COOKING" >
<title lang="en">Everyday Italian</title>
<author>Giada De Laurentiis</author>
<year>2005</year>
<price>30.00</price>
</book>
<book category="CHILDREN">
<title lang="jp">Harry Potter</title>
<author>J K. Rowling</author>
<year>2005</year>
<price>29.99</price>
</book>
<book category="WEB">
<title lang="en">XQuery Kick Start</title>
<author>James McGovern</author>
<author>Per Bothner</author>
<author>Kurt Cagle</author>
<author>James Linn</author>
<author>Vaidyanathan Nagarajan</author>
<year>2003</year>
<price>49.99</price>
</book>
<book category="WEB">
<title lang="cn">Learning XML</title>
<author>Erik T. Ray</author>
<year>2003</year>
<price>39.95</price>
</book>
</bookstore>
 
  ' 
 
 

 

 
 

节点查询操作
 

 
 

  •  一般查询
 

 
 
 
 
 
  -- 
  文档 
  
 
  select 
    
  @data 
  
 
  /* 
  output:
<bookstore>
<book category="COOKING">
......
</book>
</bookstore>
 
  */ 
  
 
  -- 
  任意级别是否存在price节点 
  
 
  select 
    
  @data 
  .exist( 
  ' 
  //price 
  ' 
  )
 
  /* 
  output:
1
 
  */ 
   
 
  -- 
  获取所有book节点 
  
 
  select 
    
  @data 
  .query( 
  ' 
  //book 
  ' 
  )
 
  /* 
  output:
<book category="COOKING">
......
</book>
<book category="CHILDREN">
......
</book>
<book category="WEB">
......
</book>
<book category="WEB">
......
</book> 
  */ 
   
 
  -- 
  获取第一个book节点 
  
 
  select 
    
  @data 
  .query( 
  ' 
  //book[1] 
  ' 
  )
 
  /* 
  output:
<book category="COOKING">
......
</book>
 
  */ 
  
 
  -- 
  获取前两个book节点 
  
 
  select 
    
  @data 
  .query( 
  ' 
  //book[position()<=2] 
  ' 
  )
 
  /* 
  output:
<book category="COOKING">
......
</book>
<book category="CHILDREN">
......
</book>
 
  */ 
  
 
  -- 
  获取最后一个book节点 
  
 
  select 
    
  @data 
  .query( 
  ' 
  //book[last()] 
  ' 
  )
 
  /* 
  output:
<book category="WEB">
......
</book> 
  */ 
   
 
  -- 
  获取price>35的所有book节点 
  
 
  select 
    
  @data 
  .query( 
  ' 
  //book[price>35] 
  ' 
  )
 
  /* 
  output:
<book category="WEB">
<title lang="en">XQuery Kick Start</title>
……
</book>
<book category="WEB">
<title lang="cn">Learning XML</title>
……
</book>
 
  */ 
  
 
  -- 
  获取category="WEB"的所有book节点 
  
 
  select 
    
  @data 
  .query( 
  ' 
  //book[@category="WEB"] 
  ' 
  )
 
  /* 
  output:
<book category="WEB">
<title lang="en">XQuery Kick Start</title>
……
</book>
<book category="WEB">
<title lang="cn">Learning XML</title>
……
</book>
 
  */ 
  
 
  -- 
  获取含category属性的所有book节点 
  
 
  select 
    
  @data 
  .query( 
  ' 
  /bookstore/book[@category] 
  ' 
  )
 
  /* 
  output:
<book category="COOKING">
......
</book>
<book category="CHILDREN">
......
</book>
<book category="WEB">
......
</book>
<book category="WEB">
......
</book> 
  */ 
   
 
  -- 
  获取book节点下title的lang="en"的所有title节点 
  
 
  select 
    
  @data 
  .query( 
  ' 
  //book/title[@lang="en"] 
  ' 
  )
 
  /* 
  output:
<title lang="en">Everyday Italian</title>
<title lang="en">XQuery Kick Start</title>
 
  */ 
  
 
  -- 
  获取title的lang="en"且 price>35的所有book节点 
  
 
  select 
    
  @data 
  .query( 
  ' 
  //book[./title[@lang="en"] or price>35 ] 
  ' 
  )
 
  /* 
  output:
<book category="COOKING">
......
</book>
<book category="WEB">
......
</book>
<book category="WEB">
......
</book> 
  */ 
   
 
  -- 
  获取第一个book元素的title元素的值 
  
 
  select 
    
  @data 
  .value( 
  ' 
  (/bookstore/book/title)[1] 
  ' 
  ,  
  ' 
  nvarchar(max) 
  ' 
  )
 
  /* 
  output:
Everyday Italian
 
  */ 
  
 
  -- 
  获取title的lang="en"且 price>35的第一book的(第一个)title 
  
 
  select 
    
  @data 
  .query( 
  ' 
  //book[./title[@lang="en"] and price>35 ] 
  ' 
  ) 
 
 
 
 
  　　　　　　　　　　　　　　. 
  value( 
  ' 
  (book/title)[1] 
  ' 
  , 
  ' 
  varchar(max) 
  ' 
  )
 
  /* 
  output:
XQuery Kick Start
 
  */ 
  
 
  -- 
  等价于“获取title的lang="en"且 price>35的第一book的(第一个)title” 
  
 
  select 
    
  @data 
  .value( 
  ' 
  (//book[./title[@lang="en"] and price>35 ]/title)[1] 
  ' 
  , 
  ' 
  varchar(max) 
  ' 
  )
 
  /* 
  output:
XQuery Kick Start
 
  */ 
 
 

 

  • 根据节点名称查询
 

 
 
 
 
 
  -- 
  查找第一个book节点的第一个名为title的节点的值 
  
 
  DECLARE 
    
  @ElementName 
    
  VARCHAR 
  ( 
  20 
  )
 
  SELECT 
    
  @ElementName 
    
  = 
    
  ' 
  title 
  ' 
  
 
  select 
    
  @data 
  .value( 
  ' 
  (/bookstore/book/*[local-name()= 
 
 
 
 
  　　　　　　　　　　　　sql:variable("@ElementName")])[1] 
  ' 
  , 
  ' 
  varchar(30) 
  ' 
  )
 
  /* 
  output:
Everyday Italian
 
  */ 
 
 

 

  • 映射为表结构查询
 

 
 
--将book元素映射到数据表book列
select Tab.Col.query('.') as book from @data.nodes('/bookstore/book')Tab(Col);
 
 
--查询price元素的位置和值
SELECT a.number as position,b.price
FROM master.dbo.spt_values A 
CROSS APPLY (
SELECT C.value('price[1]','float') as price
FROM @data.nodes('/bookstore/book[position()=sql:column("number")]')T(C)) b
WHERE A.type='P'
 
 
 
 
 

 

  • 模糊查询
 

 
 
 
 
 
  -- 
  获取所有包含lang属性的节点 
  
 
  select 
    
  @data 
  .query( 
  ' 
  //*[@lang] 
  ' 
  ) 
 
  /* 
  output:
<title lang="en">Everyday Italian</title>
<title lang="jp">Harry Potter</title>
<title lang="en">XQuery Kick Start</title>
<title lang="cn">Learning XML</title> 
  */ 
  
 
  -- 
  获取所有book节点 
  
 
  select 
    
  @data 
  .query( 
  ' 
  /bookstore/* 
  ' 
  )
 
  /* 
  output:
<book category="COOKING">
......
</book>
<book category="CHILDREN">
......
</book>
<book category="WEB">
......
</book>
<book category="WEB">
......
</book> 
  */ 
  
 
  -- 
  获取所有节点 
  
 
  select 
    
  @data 
  .query( 
  ' 
  //* 
  ' 
  )
 
  /* 
  output:
<bookstore>
<book category="COOKING">
......
</book>
<book category="CHILDREN">
......
</book>
<book category="WEB">
......
</book>
<book category="WEB">
......
</book>
</bookstore>
 
  */ 
  
 
  -- 
  获取所有包含属性的title节点 
  
 
  select 
    
  @data 
  .query( 
  ' 
  //title[@*] 
  ' 
  ) 
 
  /* 
  output:
<title lang="en">Everyday Italian</title>
<title lang="jp">Harry Potter</title>
<title lang="en">XQuery Kick Start</title>
<title lang="cn">Learning XML</title> 
  */ 
  
 
  -- 
  读取根节点的名称 
  
 
  SELECT 
    
  @data 
  .value( 
  ' 
  local-name(/*[1]) 
  ' 
  , 
  ' 
  VARCHAR(20) 
  ' 
  )  
  AS 
   ElementName
 
  /* 
  output
bookstore
 
  */ 
  
 
  -- 
  读取第三级上第一个节点的名称和值 
  
 
  SELECT 
    
  @data 
  .value( 
  ' 
  local-name((/*/*/*)[1]) 
  ' 
  , 
  ' 
  VARCHAR(20) 
  ' 
  )  
  AS 
   ElementName,
 
  @data 
  .value( 
  ' 
  (/*/*/*/text())[1] 
  ' 
  , 
  ' 
  VARCHAR(20) 
  ' 
  )  
  AS 
   ElementValue
 
  /* 
  output
title Everyday Italian
 
  */ 
  
 
  -- 
  读取第二级上第一个节点下属节点的名称和值 
  
 
  SELECT 
  
C.value( 
  ' 
  local-name(.) 
  ' 
  , 
  ' 
  VARCHAR(200) 
  ' 
  )  
  AS 
   ElementName,
C.value( 
  ' 
  . 
  ' 
  , 
  ' 
  VARCHAR(200) 
  ' 
  )  
  AS 
   ElementValue
 
  FROM 
    
  @data 
  .nodes( 
  ' 
  /*/*[1]/* 
  ' 
  ) T(C) 
 
 
 
 
    
 
 
 
 

 

  •  相邻节点查询
 

 
 
 
 
 
  -- 
  获取第一个category=“WEB”的book节点的前一个book节点 
  
 
  select 
    
  @data 
  .query( 
  ' 
  (/bookstore/book[. << (/bookstore/book[@category="WEB"])[1]])[last()] 
  ' 
  )
 
  /* 
  output:
<book>
<title lang="jp">Harry Potter</title>
<author>J K. Rowling</author>
<year>2005</year>
<price>29.99</price>
</book> 
  */ 
  
 
  -- 
  获取第一个category=“WEB”的book节点的前一个book节点 
  
 
  select 
    
  @data 
  .query( 
  ' 
  (/bookstore/book[. >>
(/bookstore/book[@category="WEB"])[1]])[1] 
  ' 
  )
 
  /* 
  output:
<book category="WEB">
<title lang="cn">Learning XML</title>
<author>Erik T. Ray</author>
<year>2003</year>
<price>39.95</price>
</book> 
  */ 
 
 

 

  • 节点数目查询
 

 
 
 
 
 
  -- 
  获得book节点的数量 
  
 
  SELECT 
    
  @data 
  .value( 
  ' 
  count(/bookstore/book) 
  ' 
  , 
  ' 
  INT 
  ' 
  )
 
  /* 
  output:
4
 
  */ 
 
 

 

 
 

属性查询操作
 

 
 

 
 

 
 
 
  
 
  
 
   ========一般查询=========
 
   -- 
   获得第一个book节点的category属性值 
   
 
   select 
     
   @data 
   .value( 
   ' 
   (/bookstore/book/@category)[1] 
   ' 
   ,  
   ' 
   nvarchar(max) 
   ' 
   )
 
   /* 
   output:
COOKING
 
   */ 
   
 
   -- 
   获取title的lang="en"且 price>35的第一book的(第一个)title的lang属性 
   
 
   select 
     
   @data 
   .value( 
   ' 
   ((//book[@category="WEB" and 
price>35 ]/title)[1]/@lang)[1] 
   ' 
   , 
   ' 
   varchar(max) 
   ' 
   )
 
   /* 
   output:
en
 
   */ 
   
 
   -- 
   获取第一本书的title 
   
 
   select 
     
   @data 
   .value( 
   ' 
   (/bookstore/book[position()=1]/title)[1] 
   ' 
   , 
   ' 
   varchar(max) 
   ' 
   ) 
 
   /* 
   output:
Everyday Italian
 
   */ 
   
 
  
 
  
 
   ========根据属性名称查询======== 
   
 
   -- 
   获取第一个book节点的名为category的属性值 
   
 
   DECLARE 
     
   @att 
     
   VARCHAR 
   ( 
   20 
   )
 
   SELECT 
     
   @att 
     
   = 
     
   ' 
   category 
   ' 
   
 
   select 
     
   @data 
   .value( 
   ' 
   (/bookstore/book/@*[local-name()=sql:variable("@att")])[1] 
   ' 
   , 
   ' 
   VARCHAR(20) 
   ' 
   )
 
   /* 
   output:
COOKING
 
   */ 
  
 
   
  
 
   ========映射为表结构查询========

-获取每本书的第一个author
select Tab.Col.value('author[1]','varchar(max)') as author
    from @data.nodes('//book')as Tab(Col) 
  
 
  
 
    
  
 
  
 
    


 
   -- 
   获取所有book的所有信息 
   
 
   select 
   
T.C.value( 
   ' 
   title[1] 
   ' 
   , 
   ' 
   varchar(max) 
   ' 
   )  
   as 
    title,
T.C.value( 
   ' 
   year[1] 
   ' 
   , 
   ' 
   int 
   ' 
   )  
   as 
     
   year 
   ,
T.C.value( 
   ' 
   price[1] 
   ' 
   , 
   ' 
   float 
   ' 
   )  
   as 
    price,
T.C.value( 
   ' 
   author[1] 
   ' 
   , 
   ' 
   varchar(max) 
   ' 
   )  
   as 
    author1,
T.C.value( 
   ' 
   author[2] 
   ' 
   , 
   ' 
   varchar(max) 
   ' 
   )  
   as 
    author2,
T.C.value( 
   ' 
   author[3] 
   ' 
   , 
   ' 
   varchar(max) 
   ' 
   )  
   as 
    author3,
T.C.value( 
   ' 
   author[4] 
   ' 
   , 
   ' 
   varchar(max) 
   ' 
   )  
   as 
    author4
 
   from 
     
   @data 
   .nodes( 
   ' 
   //book 
   ' 
   )  
   as 
    T(C) 
  
 
  
 
   


 
   -- 
   获取不是日语(lang!="jp")且价格大于35的书的所有信息 
   
 
   select 
   
T.C.value( 
   ' 
   title[1] 
   ' 
   , 
   ' 
   varchar(max) 
   ' 
   )  
   as 
    title,
T.C.value( 
   ' 
   year[1] 
   ' 
   , 
   ' 
   int 
   ' 
   )  
   as 
     
   year 
   ,
T.C.value( 
   ' 
   price[1] 
   ' 
   , 
   ' 
   float 
   ' 
   )  
   as 
    price,
T.C.value( 
   ' 
   author[1] 
   ' 
   , 
   ' 
   varchar(max) 
   ' 
   )  
   as 
    author1,
T.C.value( 
   ' 
   author[2] 
   ' 
   , 
   ' 
   varchar(max) 
   ' 
   )  
   as 
    author2,
T.C.value( 
   ' 
   author[3] 
   ' 
   , 
   ' 
   varchar(max) 
   ' 
   )  
   as 
    author3,
T.C.value( 
   ' 
   author[4] 
   ' 
   , 
   ' 
   varchar(max) 
   ' 
   )  
   as 
    author4
 
   from 
     
   @data 
   .nodes( 
   ' 
   //book[./title[@lang!="jp"] and price>35 ] 
   ' 
   )  
   as 
    T(C) 
  
 
  
 
   
 
     
  
 
    
  
 
   ========模糊查询========= 
  
 
  
 
   
 
   
 
    -- 
    查询第一个book节点的第一个属性值 
    
 
    SELECT 
      
    @data 
    .value( 
    ' 
    (/bookstore/book[1]/@*[position()=1])[1] 
    ' 
    , 
    ' 
    VARCHAR(20) 
    ' 
    )
 
    /* 
    output:
COOKING
 
    */ 
   
 
   
 
      
    
 
    =========查询属性数量========= 
    
 
    -- 
    查询第一个book节点的属性数量 
    
 
    SELECT 
      
    @data 
    .value( 
    ' 
    count(/bookstore/book[1]/@*) 
    ' 
    , 
    ' 
    INT 
    ' 
    )
 
    /* 
    output:
1 
    */ 
   
 
  
 
  
 
 
 

 

 
 

修改、删除、移动、遍历
 

 
 

========修改操作======

--将category="WEB"的第一个book节点的year值改为2000
set @data.modify('replace value of
(/bookstore/book[@category="WEB"]/year/text())[1] with "2000"')
/*output:
<book category="WEB">
<title lang="cn">Learning XML</title>
<author>Erik T. Ray</author>
<year>2000</year>
<price>39.95</price>
</book>
*/
--替换第一个book节点的author的内容为“替换内容”
set @data.modify('replace value of(/bookstore/book[1]/author[1]/text())[1] with ("替换内容")')
/*output:
<book category="COOKING">
<title lang="en">Everyday Italian</title>
<author>替换内容</author>
<year>2005</year>
<price>30.00</price>
</book>*/
 

 ========删除操作========

--删除title的@lang="en"的所有book节点
set @data.modify('delete /bookstore/book[./title[@lang="en"]]')
/*output:
<bookstore>
  <book>
    <title lang="jp">Harry Potter</title>
    <author>J K. Rowling</author>
    <year>2005</year>
    <price>29.99</price>
  </book>
  <book category="WEB">
    <title lang="cn">Learning XML</title>
    <author>Erik T. Ray</author>
    <year>2003</year>
    <price>39.95</price>
  </book>
</bookstore>
*/
--删除第一个book节点的author的内容
set @data.modify('delete /bookstore[1]/book[1]/author[1]/text()')
/*output:
  <book category="COOKING">
    <title lang="en">Everyday Italian</title>
    <author />
    <year>2005</year>
    <price>30.00</price>
  </book>
*/
========移动操作=========

--title="Harry Potter"的book节点在同级中上移一层
set @data.modify('insert (/bookstore/book[title="Harry Potter"]) 
before (/bookstore/book[. << (/bookstore/book[title="Harry Potter"])[1]])
[last()]')
SET @data.modify ('delete /bookstore/book[title="Harry Potter"] 
[. is (/bookstore/book[title="Harry Potter"])[last()]]')
/*output:
<book category="CHILDREN">
......
</book>
<book category="COOKING">
......
</book>
<book category="WEB">
......
</book>
<book category="WEB">
......
</book>*/ 
--title="Harry Potter"的book节点在同级中下移一层
set @data.modify('insert (/bookstore/book[title="Harry Potter"]) 
before (/bookstore/book[. >> (/bookstore/book[title="Harry Potter"])[1]])
[last()]')
SET @data.modify ('delete /bookstore/book[title="Harry Potter"][1] ')
/*output:
<book category="COOKING">
......
</book>
<book category="WEB">
......
</book>
<book category="CHILDREN">
......
</book>
<book category="WEB">
......
</book>*/ 
--title="Harry Potter"的book节点移到category为COOKING的book节点前
set @data.modify('insert (/bookstore/book[title="Harry Potter"]) 
before (/bookstore/book[@category="COOKING"])[1]')
SET @data.modify ('delete /bookstore/book[title="Harry Potter"] [2]')
/*output:
<book category="CHILDREN">
......
</book>
<book category="COOKING">
......
</book>
<book category="WEB">
......
</book>
<book category="WEB">
......
</book>*/ 
--title="Harry Potter"的book节点移到categroy为WEB的第一个book节点后
set @data.modify('insert (/bookstore/book[title="Harry Potter"]) 
after (/bookstore/book[@category="WEB"])[1] ')
SET @data.modify ('delete /bookstore/book[title="Harry Potter"][1] ')
/*output:
<book category="COOKING">
......
</book>
<book category="WEB">
......
</book>
<book category="CHILDREN">
......
</book>
<book category="WEB">
......
</book>*/ 

 

 

========循环遍历所有元素=========

--循环所有book节点
DECLARE 
    @cnt INT, 
    @totCnt INT,
    @child XML
-- counter variables
SELECT 
    @cnt = 1,
    @totCnt = @data.value('count(/bookstore/book)','INT')
-- loop
WHILE @cnt <= @totCnt BEGIN
    SELECT
        @child = @data.query('/bookstore/book[position()=sql:variable("@cnt")]')
    PRINT 'Processing Child Element: ' + CAST(@cnt AS VARCHAR)
    PRINT 'Child element:  ' + CAST(@child AS VARCHAR(max))
    PRINT ''
    -- incremet the counter variable
    SELECT @cnt = @cnt + 1
END
/*output
Processing Child Element: 1
Child element:  <book category="COOKING">......</book>

Processing Child Element: 2
Child element:  <book><title lang="jp">......</book>


Processing Child Element: 3
Child element:  <book category="WEB">......</book>

Processing Child Element: 4
Child element:  <book category="WEB">......</book>
 

 

                

        




    

  



    


            

  



          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值