SQL Server2005µÄXMLÊý¾ÝÀàÐÍÖ®»ù´¡Æª2
value·½·¨
µ±Äã²»Ïë½âÊÍÕû¸ö²éѯµÄ½á¹û¶øÖ»ÏëµÃµ½Ò»¸ö±êÁ¿ÖµÊ±£¬Õâ¸övalue·½·¨ÊǺÜÓаïÖúµÄ¡£Õâ¸övalue·½·¨ÓÃÓÚ²éѯXML²¢ÇÒ·µ»ØÒ»¸öÔ×ÓÖµ¡£
Õâ¸övalue·½·¨µÄÓï·¨ÈçÏ£º
value(XQuery£¬datatype)
½èÖúÓÚvalue·½·¨£¬Äã¿ÉÒÔ´ÓXMLÖеõ½µ¥¸ö±êÁ¿Öµ¡£Îª´Ë£¬Äã±ØÐëÖ¸¶¨XQueryÓï¾äºÍÄãÏëÒªËü·µ»ØµÄÊý¾ÝÀàÐÍ£¬²¢ÇÒÄã¿ÉÒÔ·µ»Ø³ýÁËXMLÊý
¾ÝÀàÐÍÍâµÄÈκÎÊý¾ÝÀàÐÍ¡£ÀýÈ磬Èç¹ûÄãÏëµÃµ½Ã¿Ò»¸öС×éÖеĵÚÒ»¸öͶÇòÊÖµÄÃû×Ö£¬Äã¿ÉÒÔ±àдÈçÏÂÐÎʽµÄ²éѯÓï¾ä£º
½øÐÐÒ»´Î²éѯÒԵõ½µ¥¸öÖµ
ÒÔÏÂΪÒýÓõÄÄÚÈÝ£º
SELECT TeamDoc.value(
'(/Team/Players/Pitcher/@name)[1]'£¬
'nvarchar(max)')
AS FirstPitcher
from Team
ÔÚÿһ¸öС×éµÄµÚÒ»¸öͶÇòÊֵıêÁ¿ÖµÖеÄÕâ¸ö²éѯ½á¹û·µ»ØÖµÈçÏ£º
ÒÔÏÂΪÒýÓõÄÄÚÈÝ£º
FirstPitcher
------------------------------
John Smoltz
(1 row(s) affected)
×¢Ò⣬queryºÍvalue·½·¨Ö®¼äµÄ²»Í¬ÔÚÓÚ£¬query·½·¨·µ»ØÒ»¸öXMLÊý¾ÝÀàÐÍ-Ëü°üº¬²éѯµÄ½á¹û£»¶øvalue·½·¨·µ»ØÒ»¸ö´øÓвéѯ½á¹ûµÄ·Ç
XMLÊý¾ÝÀàÐÍ¡£ÁíÍ⣬value·½·¨½öÄÜ·µ»Øµ¥¸öÖµ(»ò±êÁ¿Öµ)¡£Èç¹ûÄãÊÔͼ´´½¨Ò»¸öʹÓÃvalue·½·¨·µ»Ø¶àÓÚÒ»¸öÖµµÄXQuery±í´ïʽ£¬Ä㽫µÃµ½Ò»
¸ö´íÎó¡£
modify·½·¨
¾¡¹ÜXQuery±ê×¼²¢Ã»ÓÐÌṩһÖÖ¸üÐÂXMLµÄ»úÖÆ£¬µ«ÊÇSQL Server 2005ÌṩÁËÒ»ÖÖ·½·¨ÓÃÓÚ¼´Ê±µØÐÞ¸ÄÒ»¸öXML¶ÔÏóµÄÒ»²¿·Ö¡£ÕâÒâζ×Å£¬
Äã²»±Ø½öΪÁËÐ޸Ķø¼ìË÷Ò»¸öÍêÕûµÄXMLÎĵµ¡£ÎªÁ˼´Ê±ÐÞ¸ÄÒ»¸öÎĵµ£¬Äã¿ÉÒÔ²ÉÓÃÒ»ÖÖ½áºÏ·½Ê½-Modify·½·¨ºÍSQL Server 2005µÄеÄXMLÊý
¾ÝÐÞ¸ÄÓïÑÔ(XML DML)¡£
Modify·½·¨µÄÓï·¨ÊÇ£º
modify(£¼XMLDML£¾)
¸Ã·½·¨½öʹÓÃÒ»¸ö²ÎÊý£ºXML DMLÓï¾ä¡£XML DMLÒ²ÀàËÆÓÚSQLµÄinsert£¬updateºÍdeleteÓï·¨£¬µ«ÊDz¢²»Ò»Ñù¡£ÀýÈ磬Äã¿ÉÒÔͨ¹ýʹÓÃ
insert DMLÓï¾äÀ´ÐÞ¸ÄXML£º
ÒÔÏÂΪÒýÓõÄÄÚÈÝ£º
SET @doc.modify('
insert £¼Pitcher name="Jaret Wright"/£¾ as last
into (/Team/Players)[1]
')
ÁíÍ⣬Ä㻹¿ÉÒÔͨ¹ýµ÷ÓÃÒ»¸öUPDATEÓï¾ä²¢ÐÞ¸ÄÒ»¸öXMLÁÐÀ´ÊµÏÖͬÑùÄ¿µÄ£º
ÐÞ¸ÄÒ»¸öXMLÎĵµ¶ø²»ÍêÈ«Ìæ»»Ëü£º
ÒÔÏÂΪÒýÓõÄÄÚÈÝ£º
UPDATE Team
SET TeamDoc.modify('
insert £¼Pitcher name="Jaret Wright"/£¾ as last
into (/Team/Players)[1]
')
WHERE TeamDoc.exist('/Team[@name="Braves"]') = 1
×¢Ò⣬ÔÚÕâ¸öUPDATEÓï¾äÖеÄSET×Ӿ䲢²»×ñÑÄã¹ýÈ¥±àдSQLʱËùʹÓõÄSET x = y ģʽ¡£¸ÃÓï·¨¼Ù¶¨£¬ÄãÄܹ»Ìṩһ¸öÍêȫеÄÖµÀ´´ú
Ïà¹ØÎĵµ£º
²Î¿¼ÎÄÏ×£ºhttp://www.cnblogs.com/chenxizhang/archive/2009/04/23/1441913.html
SQL ServerÌṩËÄÖÖ·½Ê½Á¬½ÓÊý¾Ý¿â·þÎñÆ÷:
1. share memory: Ö»ÊÊÓÃÓÚ¿Í»§¶ËºÍ·þÎñÆ÷¶ËÔÚͬһ̨»úÆ÷ÉÏ,²ÅÄܲÉÓõÄÁ¬½Ó
2. Named pipe:
ÔÚ¾ÖÓòÍø(LAN)ÄÚʵÏÖÁ¬½Ó,Ò»¸ö½ø³ÌʹÓÃÒ»²¿·ÖµÄÄÚ´æÀ´Ïò½ø³ÌµÄÁíÒ»¶Ë´«µÝÏûÏ¢,Ò»¸ö½ø³ÌµÄÊä³öÊÇÁíÍâ ......
--> Title : SQL ServerÊÓͼÖг£見ÏÞÖÆÌõ¼þ
--> Author : wufeng4552
--> Date : 2010-03-01
(1): ÊÓͼÊý¾Ý¸ü¸ÄµÄ³£見ÏÞ¶¨
µ±Óû§¸üÐÂÊÓͼÖеÄÊý¾Ýʱ,Æäʵ¸ü¸ÄµÄÊÇÆä¶ÔÓ¦µÄÊý¾Ý±íµÄÊý¾Ý.ÎÞÂÛÊǶÔÊÓͼÖеÄÊý¾Ý½øÐиü¸Ä,»¹ÊÇÔÚÊÓͼÖвåÈë»òÕßɾ³ýÊý¾Ý,¶¼ÊÇÀàËÆµÄµÀÀí.µ«ÊÇ,² ......
MSSQL»¹ÔÊý¾Ý¿âʧ°Ü£¬ÌáʾÈçÏ£º »¹Ô¶ÔÓë·þÎñÆ÷ ʧ°Ü¡£(Microsoft.SqlServer.Smo) ÆäËüÐÅÏ¢£º ©§ ©»¡úÖ´ÐÐTransact-SQLÓï¾ä»òÅú´¦Àíʱ·¢ÉúÒì³£¡££¨Microsoft.SqlServer.ConnectionsInfo£© ©§ ©»¡ú²»ÄÜÑ¡ÔñÎļþ»òÎļþ×é¡°¡¡¡±ÓÃÓڴ˲Ù×÷¡£ RESTORE DATABASE ÕýÔÚÒì³£ÖÕÖ¹¡££¨Microsoft SQL Server, ´íÎó£º3219£© ³öÏÖ´Ë ......
--»ñȡij¸öÊý¾Ý¿âÖеıí½á¹¹
SELECT
--±íÃû=case when a.colorder=1 then d.name else '' end,
ÐòºÅ=a.colorder,
--±êʶ=case when COLUMNPROPERTY(&nbs ......
Ò»¡¢ÒýÑÔ
Èç½ñ£¬ÔÚSQL Server 2005ÖУ¬XML³ÉΪµÚÒ»Á÷µÄÊý¾ÝÀàÐÍ¡£½èÖúÓÚ»ùÓÚXMLģʽµÄÇ¿ÀàÐÍ»¯Ö§³ÖºÍ»ùÓÚ·þÎñÆ÷¶ËµÄXMLÊý¾ÝУÑ鹦ÄÜ£¬ÏÖÔÚ
£¬¿ª·¢Õß¿ÉÒÔ¶Ô´æ´¢µÄXMLÎĵµ½øÐÐÇáËɵØÔ¶³ÌÐ޸ġ£×÷ΪÊý¾Ý¿â¿ª·¢Õߣ¬Ðí¶àÈ˶¼±ØÐë´óÁ¿µØÉæ¼°XML¡£
Èç½ñ£¬ÔÚSQL Server 2005ÖУ¬ÄãÄÜÒÔÒ»ÖÖеÄÊý¾ÝÀàÐ͵ÄÐÎʽ°ÑXML´æ´¢ÔÚÊý¾Ý¿âÖÐ ......