代码改变世界

温故而知新——积累了N久的代码！

2013-01-06 13:07  AceYue  阅读(5704)  评论(36)  编辑  收藏  举报

1， 获取当前电脑上所使用的域名

View Code 
        /// <summary>
        /// 获取当前电脑上所使用的域名
        /// </summary>
        /// <returns></returns>
        private static string GetDomainName()
        {
            SelectQuery query = new SelectQuery("Win32_ComputerSystem");//使用SelectQuery类需引用System.Management空间及System.Management.dll程序集
            using (ManagementObjectSearcher searcher = new ManagementObjectSearcher(query))
            {
                foreach (ManagementObject mo in searcher.Get())
                {
                    if((bool)(mo["partofdomain"]))
                    {
                        return mo["domain"].ToString();
                    }
                }
            }
            return null;
        }

2, SQL游标案例：

View Code 
 1 alter procedure usp_FrontEnd_CandidateResume_SaveHistory_V2
 2 (  
 3  @AccountID int,  
 4  @EntityID int,  
 5  @CandidateID int,  
 6  @TypeID int,  
 7  @JobID int ,  
 8  @TemplateID nvarchar(1000)  
 9 )  
10 As  
11  SET NOCOUNT ON  
12  declare @HistoryID int  
13  declare @error_var int  
14  declare @Source int 
15   
16  Declare @CursorID int
17  Declare UserCursor Cursor for --定义游标
18  select convert(int,item) from UDF_Common_Split(@TemplateID,',')  ---游标后必须跟select 一个表
19  open UserCursor  --打开游标
20  Fetch next from UserCursor into @CursorID   --默认第一行
21  while(@@FETCH_STATUS = 0)  ----循环
22  begin
23   
24      Exec usp_sys_genglobalid @AccountID, 'HistoryID', @HistoryID output  
25      select @error_var = @@ERROR  
26      if @error_var <> 0 goto Local_Error  
27      Insert into Resume_History with (rowlock)  
28      (AccountID, EntityID, CandidateID, HistoryID, TypeID, JobID, TemplateID, LastModifiedDate)  
29      select  @AccountID,@EntityID, @CandidateID, @HistoryID, @TypeID, @JobID, @CursorID , GETUTCDATE()
30       
31     Fetch next from UserCursor into @CursorID  ----必须指定向下循环
32  end
33  close UserCursor  ---关闭游标
34  Deallocate UserCursor -----删除游标
35  
36  select @error_var = @@ERROR  
37  if @error_var <> 0 goto Local_Error  
38  return  
39  Local_Error:  
40   return @error_var

 3 , SQL编写一个UDF实现字符串按符号分割成表 如：‘1,,2,5,8’实现分割返回表结构 。使用方法 ： select item from UDF_Common_Split('1,2,3',',')

View Code 
 1 CREATE   FUNCTION [dbo].[UDF_Common_Split] 
 2 (
 3 @inPattern As nvarchar(4000),
 4 @inDelimiter As char(1)
 5 )  
 6 RETURNS @SplitResult TABLE  
 7 (  
 8  item sql_variant  
 9 )  
10 AS  
11 BEGIN   
12 Declare @startPos As int  
13 Declare @endPos As int  
14 Declare @tempPattern As nvarchar(4000)  
15 Declare @result As nvarchar(4000)  
16 Set @startPos = 1  
17 Set @endPos = 1  
18 Set @result = ''  
19 While @startPos <= Len(@inPattern)  
20 Begin  
21  Set @endPos = CharINDEX (@inDelimiter, @inPattern, @startPos)  
22  if @endPos = 0  
23   begin  
24    Set @tempPattern = SubString(@inPattern, @startPos, Len(@inPattern))  
25    Set @tempPattern = LTrim(RTrim(@tempPattern))  
26    Insert into @SplitResult values (@tempPattern)  
27    break  
28   end  
29  else  
30   begin  
31    Set @tempPattern = SubString(@inPattern, @startPos, (@endPos - @startPos + 1))  
32    Set @tempPattern = REPLACE (@tempPattern , @inDelimiter , '' )  
33    Set @tempPattern = LTrim(RTrim(@tempPattern))  
34    Insert into @SplitResult values (@tempPattern)  
35    Set @startPos = @endPos + 1  
36   end  
37 END  
38 return  
39 end  
40   
41

 4 , 牛人写的将javascript代码压缩为png图片的代码，php下压缩：

View Code 
 1 <?
 2 
 3 $filename = "prototype-1.6.0.2.js";
 4 
 5 
 6 if (file_exists($filename)) {
 7 
 8     $iFileSize = filesize($filename);
 9     
10     $iWidth = ceil(sqrt($iFileSize / 1));
11     $iHeight = $iWidth;
12 
13     $im = imagecreatetruecolor($iWidth, $iHeight);
14 
15     $fs = fopen($filename, "r");
16     $data = fread($fs, $iFileSize);
17     fclose($fs);
18 
19     $i = 0;
20 
21     for ($y=0;$y<$iHeight;$y++) {
22         for ($x=0;$x<$iWidth;$x++) {
23             $ord = ord($data[$i]);
24             imagesetpixel($im, 
25                 $x, $y,
26                 imagecolorallocate($im,
27                     $ord,
28                     $ord,
29                     $ord
30                 )
31             );
32             $i++;
33         }
34     }
35 
36     header("Content-Type: image/png");
37     imagepng($im);
38     imagedestroy($im);
39 }
40 
41 ?>

javascript 解码：

View Code 
 1 function loadPNGData(strFilename, fncCallback) {
 2     // test for canvas and getImageData
 3     var bCanvas = false;
 4     var oCanvas = document.createElement("canvas");
 5     if (oCanvas.getContext) {
 6         var oCtx = oCanvas.getContext("2d");
 7         if (oCtx.getImageData) {
 8             bCanvas = true;
 9         }
10     }
11     if (bCanvas) {
12         var oImg = new Image();
13         oImg.style.position = "absolute";
14         oImg.style.left = "-10000px";
15         document.body.appendChild(oImg);
16         oImg.onload = function() {
17             var iWidth = this.offsetWidth;
18             var iHeight = this.offsetHeight;
19             oCanvas.width = iWidth;
20             oCanvas.height = iHeight;
21             oCanvas.style.width = iWidth+"px";
22             oCanvas.style.height = iHeight+"px";
23             var oText = document.getElementById("output");
24             oCtx.drawImage(this,0,0);
25             var oData = oCtx.getImageData(0,0,iWidth,iHeight).data;
26             var a = [];
27             var len = oData.length;
28             var p = -1;
29             for (var i=0;i<len;i+=4) {
30                 if (oData[i] > 0)
31                     a[++p] = String.fromCharCode(oData[i]);
32             };
33             var strData = a.join("");
34             if (fncCallback) {
35                 fncCallback(strData);
36             }
37             document.body.removeChild(oImg);
38         }
39         oImg.src = strFilename;
40         return true;
41     } else {
42         return false;
43     }
44 }

 5，关于xslt的特殊操作

（1）在xslt中使用c#方法：

View Code 
 1 <![CDATA[xslt中使用C#方法的属性设置  ]]>
 2 <?xml version="1.0" encoding="utf-8"?>
 3 <xsl:stylesheet
 4     version="1.0"
 5     xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 6     xmlns:msxml="urn:schemas-microsoft-com:xslt"
 7     xmlns:msxsl="urn:schemas-microsoft-com:xslt"
 8     xmlns:umbraco.library="urn:umbraco.library"
 9     xmlns:scripts="urn:scripts.this"
10     exclude-result-prefixes="msxml msxsl umbraco.library scripts">
11 <![CDATA[C#方法的定义    ]]>
12     <msxsl:script language="C#" implements-prefix="scripts">
13 <![CDATA[
14     public string replace(string s0)
15     {
16         return "<b>sdf</b>";
17     }
18     ]]>
19 
20   </msxsl:script>
21 <![CDATA[使用方法    ]]>
22 <td>
23             <xsl:value-of select="@JobTitle" />
24             <xsl:value-of select="scripts:replace(@JobTitle)"/>
25           </td>

（2）xslt系统替换函数的使用：

View Code 
1 <xsl:value-of select="translate($pageTitle,$apostrophe,'')" />

（3）xslt中使用js自定义函数：

View Code 
 1 <![CDATA[定义]]>
 2 <xsl:template match="Report" mode="HtmlHeader" >
 3 
 4         <head>
 5             <title>JobsDB Dimension</title>
 6             <style>
 7                 h1 { font-size:18pt;font-family:Verdana;font-weight:bold;text-align:center;vertical-align:middle;width:100%; }
 8                 td { font-family: Arial; font-size: 9pt; }
 9                 .HeaderText { font-family: Arial;font-size: 8pt; font-weight: normal; text-align:left; width: 200px; }
10                 .HeaderData { font-family: Arial;font-size: 8pt; font-weight: normal; text-align:left; }
11                 .xlsText{mso-style-parent:style0;mso-number-format:"\@";}
12 
13             </style>
14             <script>
15             <![CDATA[
16             function replaceHTML(desc, pos){
17             document.getElementById("td_"+ pos).innerHTML=desc;
18             }
19             ]]>
20            </script>
21             <meta HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=utf-8" />
22         </head>
23 
24     </xsl:template>
25 
26 <![CDATA[使用方法]]>
27 <td dataformattas="html">
28                  <xsl:attribute name="id">td_<xsl:value-of select="position()"/></xsl:attribute>
29                  <script>replaceHTML('<xsl:value-of select="@JobDes"/>', <xsl:value-of select="position()"/>)</script>
30                  </td>

6，将sql查询结果自动传化成xml：

View Code 
1 Select * from @Information Job For Xml Auto,Type

成功的结果：

View Code 
1 <Job AccountID="12100387" EntityCode="Aceyue" JobID="9" LangID="1" JobTitle="test29408" JobDes="&lt;FONT size=2&gt;ssssssssssssssssss&lt;/FONT&gt;" JobReference="ssssssss" JobLevelID="101" />
2     <Job AccountID="12100387" EntityCode="Aceyue" JobID="10" LangID="1" JobTitle="sssssssssssss" JobDes="&lt;FONT size=2&gt;ssssssssssssssssssssssssssssssssssssssssssssssss&lt;/FONT&gt;" JobReference="sssssssss" JobLevelID="102" />
3     <Job AccountID="12100387" EntityCode="ace" JobID="11" LangID="1" JobTitle="sssssssssssss" JobDes="&lt;FONT size=2&gt;ssssssssssssss&lt;/FONT&gt;" JobReference="" JobLevelID="103" />
4     <Job AccountID="12100387" EntityCode="ace" JobID="12" LangID="1" JobTitle="ssssssssss" JobDes="&lt;FONT size=2&gt;ssssssssss&lt;/FONT&gt;" JobReference="ssss" JobLevelID="101" />
5     <Job AccountID="12100387" EntityCode="ace" JobID="13" LangID="1" JobTitle="bbbbbbbbb" JobDes="ssssssssssssssssssss" JobReference="" JobLevelID="102" />
6     <Job AccountID="12100387" EntityCode="ace" JobID="14" LangID="1" JobTitle="ssssssssss" JobDes="&lt;FONT size=2&gt;ssssssssssssssssss&lt;/FONT&gt;" JobReference="" JobLevelID="102" />

 7，将SQL中某些行的值合并：

如：

合并为：

实例代码：

View Code 
 1 CREATE TABLE body
 2 （
 3 ID int,
 4 Body nvarchar(20)
 5 ）
 6 
 7 insert into body values(1,'aaaaaaa'),(2,'bbbbbbbb'),(3,'cccccccc'),(1,'ddddddddddd')
 8 
 9 select * from body
10 
11     SELECT distinct a.ID,stuff((SELECT ','+BODY FROM body WHERE ID=a.ID FOR xml path('')),1,1,'') AS BODY from body a
12 
13 --具体思路是
14 SELECT ','+BODY FROM body WHERE ID=a.ID FOR xml path('') 
15 --这条语句的作用是按照a中的ID查找body表中ID=a.ID的所有记录，并把它转换成一个xml
16 stuff((SELECT ','+BODY FROM body WHERE ID=a.ID FOR xml path('')),1,1,'') 
17 --这条语句的作用是把生成的xml前面的一个逗号去掉并转化成标量值
18 --最后用一个distinct去掉重复的记录
19 
20 --如果查询过程中有‘’这种空字符串，使用这种方法会出现‘，，，aaa’这样的情况，我们可以 采取以下方法替换：
21 select distinct f.AccountID,f.JobID,stuff((SELECT case JobFuntion when '' then '' when null then '' else ','+JobFuntion end FROM @Job_Function WHERE AccountID=f.AccountID and JobID=f.JobID FOR xml path('')),1,1,'') as JobFunction from @Job_Function  f

 8 ， MVC3 全站域名重定向代码：

View Code 
1  protected void Application_BeginRequest(object sender, EventArgs e)
2  {
3             string strUrl = Request.Url.ToString().Trim().ToLower();
4             if (strUrl.Contains("http://xxx.cn"))
5             {
6                       Response.RedirectPermanent(strUrl.Replace("http://xxx.cn", "http://www.xxx.cn")); 
7             }
8  }

 9，求数组中出现次数超过一半元素（一定存在一个超过一半的情况下） 一种算法：

View Code 
 int Find(int *arr,int n)
 {
     int tmp = arr[0];
     int count = 1;//计数器
     for(int i = 1 ; i < n ; i++)
     {
         if(count == 0)
         {
             tmp = arr[i];
             count = 1;
         }
         else if(tmp == arr[i])
         {
             count++;
        }
         else
         {
            count--;
         }
     }
     return tmp;
 }

 10，使用反射解决简单工厂模式的（违背开发封闭）

抽象产品的定义

View Code 
 1 //------------------------------------------------
 2 // All Rights Reserved , Copyright (C) 2012 AceYue
 3 //------------------------------------------------
 4 using System;
 5 using System.Collections.Generic;
 6 
 7 namespace Common.DesignPattern.Factory
 8 {
 9     /// <summary>
10     /// 抽象产品
11     /// </summary>
12     interface IHero
13     {
14         /// <summary>
15         /// 姓名
16         /// </summary>
17         string Name { get; }
18 
19         /// <summary>
20         /// 英雄方法
21         /// </summary>
22         void Create();
23     }
24 }

具体产品

View Code 
 1 //--------------------------------------------------------
 2 // All Rights Reserved , Copyrights (C) 2012  AceYue
 3 //--------------------------------------------------------
 4 using System;
 5 using System.Collections.Generic;
 6 
 7 namespace Common.DesignPattern.Factory
 8 {
 9     /// <summary>
10     /// 具体产品
11     /// </summary>
12     [HeroMark(HeroType.Ace)]
13     public class Ace:IHero
14     {
15         public string Name { get { return "Ace"; } }
16 
17         public void Create()
18         {
19             Console.WriteLine(this.Name);
20         }
21     }
22 }
View Code 
 1 //-------------------------------------------------
 2 // All Rights Reserved , Copyright (C) 2012 AceYue
 3 //---------------------------------------------------
 4 using System;
 5 using System.Collections.Generic;
 6 
 7 
 8 namespace Common.DesignPattern.Factory
 9 {
10     /// <summary>
11     /// 具体产品
12     /// </summary>
13     [HeroMark(HeroType.Milo)]
14     public class Milo:IHero
15     {
16         public string Name { get { return "Milo"; } }
17 
18         public void Create()
19         {
20             Console.WriteLine(this.Name);
21         }
22     }
23 }

产品枚举

View Code 
 1 //--------------------------------------------------
 2 // All Rights Reserved,  Copyright (C) 2012 AceYue
 3 //--------------------------------------------------
 4 using System;
 5 
 6 
 7 namespace Common.DesignPattern.Factory
 8 {
 9     /// <summary>
10     /// 产品枚举
11     /// </summary>
12      public enum HeroType
13     {
14          /// <summary>
15          /// Ace
16          /// </summary>
17          Ace,
18          /// <summary>
19          /// Milo
20          /// </summary>
21          Milo
22     }
23 }

反射标记

View Code 
 1 //------------------------------------------------------------
 2 // All Rights Reserved , Copyright (C) 2012 AceYue
 3 //------------------------------------------------------------
 4 using System;
 5 
 6 
 7 namespace Common.DesignPattern.Factory
 8 {
 9     /// <summary>
10     /// 产品属性，标记要被反射的实例
11     /// </summary>
12     [AttributeUsage(AttributeTargets.All,AllowMultiple=false,Inherited=true)]
13     public class HeroMarkAttribute:Attribute
14     {
15         public HeroType Type { get; private set; }
16 
17         public HeroMarkAttribute(HeroType type)
18         {
19             this.Type = type;
20         }
21     }
22 }

工厂类

View Code 
 1 //---------------------------------------------------
 2 // All Rights Reserved , Copyright (C) 2012 AceYue
 3 //-------------------------------------------------------
 4 using System;
 5 using System.Collections.Generic;
 6 
 7 
 8 namespace Common.DesignPattern.Factory
 9 {
10     /// <summary>
11     /// HeroFactory
12     /// 工厂类
13     /// </summary>
14     public class HeroFactory
15     {
16         /// <summary>
17         /// hero字典
18         /// </summary>
19         private static Dictionary<HeroType, IHero> heroInstanceDic = new Dictionary<HeroType, IHero>();
20 
21         /// <summary>
22         /// 构造函数 （用反射自动添加所有实例到字典中）
23         /// </summary>
24         static HeroFactory()
25         {
26             var types = typeof(HeroFactory).Assembly.GetTypes();
27             if (types.Length > 0)
28             {
29                 foreach (var type in types)
30                 {
31                     var attributes = type.GetCustomAttributes(typeof(HeroMarkAttribute), true);
32                     if (attributes.Length > 0)
33                     {
34                         HeroType heroType = (attributes[0] as HeroMarkAttribute).Type;
35                         IHero instance = (IHero)typeof(HeroFactory).Assembly.CreateInstance(type.FullName, true);
36                         heroInstanceDic.Add(heroType, instance);
37                     }
38                 }
39             }
40         }
41 
42         /// <summary>
43         /// 创建实例类
44         /// </summary>
45         /// <param name="type"></param>
46         public static void CreateHero(HeroType type)
47         {
48             if (heroInstanceDic.ContainsKey(type))
49             {
50                 heroInstanceDic[type].Create();
51             }
52         }
53     }
54 }

 11, 在存储过程中调用WebServices

View Code 
 1 create procedure usp_CallWebServices
 2 (
 3 @parameter nvarchar(500)=null
 4 )
 5 as
 6    Declare @obj int
 7    Declare @SvercieUrl nvarchar(200)
 8    Declare @response nvarchar(max)
 9 
10 
11    Set @SvercieUrl = 'http://locahost/webservices/service.asmx/saludar?param='+@parameter
12    
13    
14    Execute sp_OACreate 'MSXML2.ServerXMLHttp',@obj out
15    Execute sp_OAMethod @obj,'open',null,'Get',@SvercieUrl,false
16    Execute sp_OAMethod @obj,'Send'
17    Execute sp_OAGetProperty @obj,'responseText',@response out
18    
19    Select @response [Response]
20    Execute sp_OADestroy @obj
21    
22 return
23 
24 
25 
26 Execute    usp_CallWebServices 'Frometa'

 12, SQL Server使用ROW_NUMBER分页实现：

View Code 
      select * from (
          select ROW_NUMBER() over(order by JobID desc) as orderIndex, * from @JobData 
       ) d where d.orderIndex > ((@PageIndex-1)*@Pagecount) and d.orderIndex <=(@PageIndex * @Pagecount)

 13，SQL 中取出xml的属性值：

        方法一：

View Code 
declare @str nvarchar(max)
set @str = '<Terms>
  <Term TermID="OT_3" TermName="Basic Salary" TermRmk="25 April 2002"/>
  <Term TermID="OT_4" TermName="Probation Period" TermRmk="25 April 2002"/>
  <Term TermID="OT_5" TermName="Annual Leave" TermRmk="25 April 2002"/>
  <Term TermID="OT_6" TermName="MPF/PF contribution %" TermRmk="25 April 2002"/>
  <Term TermID="OT_7" TermName="Share Options Number" TermRmk="25 April 2002"/>
  <Term TermID="OT_8" TermName="Commission" TermRmk="25 April 2002"/>
  <Term TermID="OT_9" TermName="Housing Allowance" TermRmk="25 April 2002"/>
  <Term TermID="OT_10" TermName="Traveling Allowance" TermRmk="25 April 2002"/>
  <Term TermID="OT_11" TermName="Bonus" TermRmk="25 April 2002"/>
</Terms>'
declare @table table(string nvarchar(max))
insert into @table values(@str)
select cast(string as xml).value('((//Terms/Term[@TermName="Basic Salary"])[1]/@TermRmk)[1]','nvarchar(255)') from @table

        方法二：

View Code 
declare @str xml
set @str = N'<Terms>
  <Term TermID="OT_3" TermName="Basic Salary" TermRmk="25 April 2002"/>
  <Term TermID="OT_4" TermName="Probation Period" TermRmk="25 April 2002"/>
  <Term TermID="OT_5" TermName="Annual Leave" TermRmk="25 April 2002"/>
  <Term TermID="OT_6" TermName="MPF/PF contribution %" TermRmk="25 April 2002"/>
  <Term TermID="OT_7" TermName="Share Options Number" TermRmk="25 April 2002"/>
  <Term TermID="OT_8" TermName="Commission" TermRmk="25 April 2002"/>
  <Term TermID="OT_9" TermName="Housing Allowance" TermRmk="25 April 2002"/>
  <Term TermID="OT_10" TermName="Traveling Allowance" TermRmk="25 April 2002"/>
  <Term TermID="OT_11" TermName="Bonus" TermRmk="25 April 2002"/>
</Terms>'

Select T.c.value('@TermName','nvarchar(200)')
From @str.nodes('Terms/Term') T(c)
Where T.c.value('@TermID','nvarchar(20)') = 'OT_3'

14，SQL中操作xml的帮助示例：

View Code 
/*
sql xml 入门:

    1、xml:        能认识元素、属性和值
    
    2、xpath:    寻址语言，类似windows目录的查找(没用过dir命令的话就去面壁)
                
                语法格式，这些语法可以组合为条件：
                "."表示自己，".."表示父亲，"/"表示儿子，"//"表示后代，
                "name"表示按名字查找，"@name"表示按属性查找
                
                "集合[条件]" 表示根据条件取集合的子集，条件可以是
                    数  值：数字,last(),last()-数字 等
                    布尔值：position()<数字,@name='条件',name='条件'
                条件是布尔值的时候可以合并计算：and or
    
    3、xquery:    基于xpath标的准查询语言，sqlserver xquery包含如下函数
                exist(xpath条件)：返回布尔值表示节点是否存在
                query(xpath条件)：返回由符合条件的节点组成的新的xml文档
                value(xpath条件,数据类型)：返回指定的标量值，xpath条件结果必须唯一
                nodes(xpath条件): 返回由符合条件的节点组成的一行一列的结果表
*/

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

--测试语句，如果不理解语法请参考上面的xpath规则和xquery函数说明

--1、文档
select @data
--2、任意级别是否存在price节点
select @data.exist('//price')
--3、获取所有book节点
select @data.query('//book')
--4、获取所有包含lang属性的节点
select @data.query('//*[@lang]') 
--5、获取第一个book节点
select @data.query('//book[1]')
--6、获取前两个book节点
select @data.query('//book[position()<=2]')
--7、获取最后一个book节点
select @data.query('//book[last()]')
--8、获取price>35的所有book节点
select @data.query('//book[price>35]')
--9、获取category="WEB"的所有book节点
select @data.query('//book[@category="WEB"]')
--10、获取title的lang="en"的所有book节点
select @data.query('//book/title[@lang="en"]')
--11、获取title的lang="en"且 price>35的所有book节点
select @data.query('//book[./title[@lang="en"] or price>35 ]')
--12、获取title的lang="en"且 price>35的第一book的(第一个)title
select @data.query('//book[./title[@lang="en"] and price>35 ]').value('(book/title)[1]','varchar(max)')
--13、等价于12
select @data.value('(//book[./title[@lang="en"] and price>35 ]/title)[1]','varchar(max)')
--14、获取title的lang="en"且 price>35的第一book的(第一个)title的lang属性
select @data.value('((//book[@category="WEB" and price>35 ]/title)[1]/@lang)[1]','varchar(max)')
--15、获取第一本书的title
select Tab.Col.value('(book/title)[1]','varchar(max)') as title
    from @data.nodes('bookstore')as Tab(Col) 
--16、获取每本书的第一个author
select Tab.Col.value('author[1]','varchar(max)') as title
    from @data.nodes('//book')as Tab(Col)
--17、获取所有book的所有信息
select
 T.C.value('title[1]','varchar(max)') as title,
 T.C.value('year[1]','int') as year,
 T.C.value('title[1]','varchar(max)')as title,
 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)
--18、获取不是日语(lang!="jp")且价格大于35的书的所有信息
select
 T.C.value('title[1]','varchar(max)') as title,
 T.C.value('year[1]','int') as year,
 T.C.value('title[1]','varchar(max)')as title,
 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)