sql xml ÈëÃÅ
*
sql xml ÈëÃÅ:
--by jinjazz
--http://blog.csdn.net/jinjazz
1¡¢xml: ÄÜÈÏʶԪËØ¡¢ÊôÐÔºÍÖµ
2¡¢xpath: Ñ°Ö·ÓïÑÔ£¬ÀàËÆwindowsĿ¼µÄ²éÕÒ(ûÓùýdirÃüÁîµÄ»°¾ÍÈ¥Ãæ±Ú)
Óï·¨¸ñʽ£¬ÕâЩÓï·¨¿ÉÒÔ×éºÏΪÌõ¼þ£º
"."±íʾ×Ô¼º£¬".."±íʾ¸¸Ç×£¬"/"±íʾ¶ù×Ó£¬"//"±íʾºó´ú£¬
"name"±íʾ°´Ãû×Ö²éÕÒ£¬"@name"±íʾ°´ÊôÐÔ²éÕÒ
"¼¯ºÏ[Ìõ¼þ]" ±íʾ¸ù¾ÝÌõ¼þÈ¡¼¯ºÏµÄ×Ó¼¯£¬Ìõ¼þ¿ÉÒÔÊÇ
Êý Öµ£ºÊý×Ö,last(),last()-Êý×Ö µÈ
²¼¶ûÖµ£ºposition()<Êý×Ö,@name='Ìõ¼þ',name='Ìõ¼þ'
Ìõ¼þÊDz¼¶ûÖµµÄʱºò¿ÉÒԺϲ¢¼ÆË㣺and or
3¡¢xquery: »ùÓÚxpath±êµÄ×¼²éѯÓïÑÔ£¬sqlserver xquery°üº¬ÈçϺ¯Êý
exist(xpathÌõ¼þ)£º·µ»Ø²¼¶ûÖµ±íʾ½ÚµãÊÇ·ñ´æÔÚ
query(xpathÌõ¼þ)£º·µ»ØÓÉ·ûºÏÌõ¼þµÄ½Úµã×é³ÉµÄеÄxmlÎĵµ
&n
Ïà¹ØÎĵµ£º
1ÓÃÓÚÅÅÐòµÄº¯Êý
row_number()
rank()
dense_rank()
ntile(group_number)
ÏÂÃæÁоÙÕâ¸öº¯ÊýµÄÓ÷¨£º
row_number()º¯ÊýÒ»°ãÓÃÓÚ×éÄÚÅÅÐò£¬¶øÆäËûÈý¸öº¯ÊýÊǶԽá¹û¼¯ÅÅÐò
Àý×Ó£º·ÖÒ³ÅÅÐò
<!--×¢ÒâÈ«¾Ö±äÁ¿Ò²ÔÚÕâÀïÉùÃ÷£¬²¢ÓöººÅ¸ô¿ª-->
create proc MyDividePageSort @iRowCount int ,@iPageNo int
AS
< ......
¹ØÓÚÁ½±í¹ØÁªµÄupdate£¬µ«Óï¾äÔõôд¶¼²»ÕýÈ·£¬ÀÏÊDZ¨´í£¬ÓÚÊÇÐľªÈâÌø£¨¾ÍŲ»Äܼ°Ê±Íê³É²Ù×÷£©È¥²éÁËһϣ¬NND£¬ÔÀ´°ÑSQLд³ÉÁËÔÚSQL ServerÏÂÃæµÄÌØÓÐÐÎʽ£¬ÕâÖÖÓï·¨ÔÚOracleÏÂÃæÊÇÐв»Í¨µÄ£¬¼±Ã¦¸Ä»ØÀ´£¬¼°Ê±Íê³ÉÁËÈÎÎñ¡£Ë³±ãÒ²°Ñ²éµ½µÄSQLÌû³öÀ´£¬ÄÄÌìÔÙÍü¼ÇÁË£¬Ò²ºÃÔÚÕâÀïÕÒ»ØÀ´£º
update customers a ......
<?xml version="1.0" encoding="UTF-8"?>
<projects>
<node Country="°µ°µ°¡" Gold="10" Silver="20" Bronze="30"/>
<node Country="°×°ß²¡" Gold="30" Silver="20" Bronze="10"/>
<node Country="³£³£³§" Gold="20" Silver="40" Bronze="60"/>
<node Country="¶Ä¶«µÀ" Gold="5 ......
LINQ to sqlËäÈ»½«Êý¾Ý¿â²Ù×÷ºÍÒµÎñÂß¼¸ôÀ뿪À´£¬Ê¹¿ª·¢ÈËÔ±Äܹ»Ê¹Óõ¥Ò»µÄÓïÑÔºÍ֪ʶÄܹ»·½±ãµÄ²Ù×÷Êý¾Ý¿â²¢´¦ÀíÒµÎñÂß¼¡£µ«ÊÇÕâ±Ï¾¹ÊÇ΢ÈíO/R½â¾ö·½°¸µÄµÚÒ»¸ö°æ±¾£¬Ïà±ÈÏà¶Ô³ÉÊìµÄDataSetÊý¾Ý¼¯½â¾ö·½°¸À´Ëµ£¬ÎÒÃÇ»¹ÊÇ¿ÉÒÔ¿´µ½Ò»Ð©²»×ã¡£
¡¡¡¡Ê×ÏÈ£¬ÎÒÃÇ×¢Òâµ½ËùÓеÄÊý¾ÝʵÌ岢ûÓдÓÒ»¸ö»ùÀàÖÐÅÉÉú£¬ÕâʹµÃ¸ø¿ª·¢Í¨Ó ......
SQL Server 2005ΪXMLÌṩ±¾µØÖ§³Ö;ÏÖÔÚ¿ÉÒÔÔÚOPENXML½ÚµãÖÐʹÓÃÒ»¸ö´øÓÐnodes()º¯ÊýµÄXML×Ö¶ÎÀàÐÍ°ÑÒ»¸öXMLÎļþת»¯ÎªÒ»¸öÐм¯¡£ÈÃÎÒÃÇÀ´¿´Ò»¸öʹÓÃOPENXMLµÄ¼òµ¥Àý×Ó£¬²¢ÏÔʾÈçºÎ°ÑËüת»¯ÎªÔÚSQL Server 2005ÖÐʹÓÃXML×Ö¶ÎÀàÐͺÍnodes()º¯Êý¡£
¡¡¡¡ÎªÁËʹÎÒÃǵÄÀý×Ó¼òµ¥»¯£¬ÎÒÃǽ«¼ÙÉèÎÒÃÇÐèÒªÖ´ÐÐijÖÖ»ùÓڲɹº¶©µ¥ÁÐ±íµ ......