SQL Server 2005 XML 操作总结（转）

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

?

?

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>

            ?