Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

SQL Server2005µÄXMLÊý¾ÝÀàÐÍÖ®»ù´¡Æª1

Ò»¡¢ÒýÑÔ
Èç½ñ£¬ÔÚSQL Server 2005ÖУ¬XML³ÉΪµÚÒ»Á÷µÄÊý¾ÝÀàÐÍ¡£½èÖúÓÚ»ùÓÚXMLģʽµÄÇ¿ÀàÐÍ»¯Ö§³ÖºÍ»ùÓÚ·þÎñÆ÷¶ËµÄXMLÊý¾ÝУÑ鹦ÄÜ£¬ÏÖÔÚ
£¬¿ª·¢Õß¿ÉÒÔ¶Ô´æ´¢µÄXMLÎĵµ½øÐÐÇáËɵØÔ¶³ÌÐ޸ġ£×÷ΪÊý¾Ý¿â¿ª·¢Õߣ¬Ðí¶àÈ˶¼±ØÐë´óÁ¿µØÉæ¼°XML¡£
Èç½ñ£¬ÔÚSQL Server 2005ÖУ¬ÄãÄÜÒÔÒ»ÖÖеÄÊý¾ÝÀàÐ͵ÄÐÎʽ°ÑXML´æ´¢ÔÚÊý¾Ý¿âÖС£
ÊÂʵÉÏ£¬ÔÚSQL Server 2000ÖоÍÒѾ­°üÀ¨ÁËһЩXMLÌØÕ÷¡£ÆäÖУ¬×î¹Ø¼üµÄÌØÕ÷ÊÇʹÓÃFOR XMLÓï¾äÒÔXMLÐÎʽ·µ»Ø½á¹û¡£SQL Server 2005
µÄ¹¦ÄÜÔòÃ÷ÏÔ²»Í¬¡£ÔÚSQL Server 2005ÖУ¬XMLÊÇÒ»ÖÖÕæÕýµÄÊý¾ÝÀàÐÍ£»ÕâÒâζ×Å£¬Äã¿ÉÒÔʹÓÃXML×÷Ϊ±íºÍÊÓͼÖеÄÁУ¬XML¿ÉÒÔÓÃÓÚT-SQLÓï
¾äÖлò×÷Ϊ´æ´¢¹ý³ÌµÄ²ÎÊý¡£ÏÖÔÚ£¬Äã¿ÉÒÔÖ±½ÓÔÚÊý¾Ý¿âÖд洢¡¢²éѯºÍ¹ÜÀíXMLÎļþ¡£
¸üÖØÒªµÄÊÇ£¬ÏÖÔÚÄ㻹Äܹ涨ÄãµÄXML±ØÐë×ñ´ÓµÄģʽ¡£
ÔÚSQL Server 2005ÖУ¬³ýÁËÌṩ»úÖÆÒÔУÑéÄãµÄÊý¾Ý¿âÖеÄXMLÀàÐÍÖ®Í⣬Ëü»¹ÔÊÐíÄãÃèÊöÒª±»´æ´¢µÄ¸´ÔÓÊý¾ÝÀàÐͲ¢ÇÒÌṩһ¸öÒýÇæÀ´
Ç¿ÖÆÊ©¼ÓÕâЩ¹æÔò¡£
¶þ¡¢Ê¹ÓÃXMLÊý¾ÝÀàÐÍ
Æäʵ£¬XMLÊý¾ÝÀàÐÍÓëSQL ServerÖÐµÄÆäËüÊý¾ÝÀàÐͲ¢²»´æÔÚ¸ù±¾µÄÇø±ð¡£Äã¿ÉÒÔ°ÑËüÓÃÔÚʹÓÃÈÎºÎÆÕͨSQLÊý¾ÝÀàÐ͵ĵط½¡£ÀýÈ磬ÏÂÁÐ
Óï¾ä´´½¨Ò»¸öXML±äÁ¿²¢ÓÃÒ»¸öXMLÌî³äËü£º
DECLARE @doc XML
SELECT @doc = '£¼Team name="Braves" /£¾'
ÁíÍ⣬Ä㻹¿ÉÒÔʹÓÃÒ»¸ö²éѯºÍSQL ServerµÄFOR XMLÓï·¨À´Ìî³äÒ»¸öXML±äÁ¿£º
SELECT @doc =
(SELECT * from Person.Contact FOR XML AUTO)
XMLÊý¾ÝÀàÐͲ»½ö¿ÉÒÔ×÷Ϊ±äÁ¿Ê¹Óã¬Ò²¿ÉÒÔÓ¦ÓÃÓÚ±íÁÐÖС£Ä㻹ÄÜ·ÖÅäȱʡֵ²¢ÇÒÖ§³ÖNOT NULLÔ¼Êø£º
ÒÔÏÂΪÒýÓõÄÄÚÈÝ£º
CREATE TABLE Team
(
TeamID int identity not null£¬
TeamDoc XML DEFAULT '£¼Team /£¾' NOT NULL

×¢Ò⣺SQL Server 2005µÄXML¹¦ÄÜÓëSQL Server 2000ÖоßÓÐÃ÷ÏԵIJ»Í¬¡£
°ÑXMLÊý¾Ý²åÈëµ½±í¸ñÖÐÖ»ÐèÒªÓÃ×Ö·û´®ÐÎʽµÄXMLÖ¸¶¨¼´¿É¡£
ÏÂÁÐʾÀý²åÈëÒ»×é¼Ç¼£º
ÒÔÏÂΪÒýÓõÄÄÚÈÝ£º
INSERT INTO Team (TeamDoc)
VALUES ('
£¼Team name="Braves"£¾
£¼Players£¾
£¼Pitcher name="John Smoltz"
role="Closer"/£¾
£¼/Players£¾
£¼/Team£¾');
INSERT INTO Team (TeamDoc)
VALUES ('
£¼Team name="Red Sox"£¾
£¼Players£¾
£¼Pitcher name="Petro Martinez"
role="Starter"/£¾
£¼/Players£¾
£¼/Team£¾');
µ±ÔÚSQL Server 2005Öд´½¨XMLµÄʵÀýʱ£¬Î¨Ò»µÄת»»ÊÇ´ÓÒ»¸ö×Ö·û´®×ª»»³ÉÒ»¸öXMLÀàÐÍ¡£Í¬Ñù£¬ÑØ×ÅÏà·´µÄ·½


Ïà¹ØÎĵµ£º

SQL Ìí¼Óɾ³ý·þÎñÆ÷

//--Ìí¼Ó·þÎñÆ÷
EXEC sp_addlinkedserver
      @server='LQXLSJ-600A5A60',--±»·ÃÎʵķþÎñÆ÷±ðÃû
      @srvproduct='',
      @provider='SQLOLEDB',
      @datasrc='LQXLSJ-600A5A60'   --Òª·ÃÎ ......

SQL °´ÖÜ,ÔÂ,¼¾¶È,Äê²éѯͳ¼ÆÊý¾Ý

//°´×ÔÈ»ÖÜͳ¼Æ 
select to_char(date,'iw'),sum() 
from 
where 
group by to_char(date,'iw') 
//°´×ÔÈ»ÔÂͳ¼Æ 
select to_char(date,'mm'),sum() 
from 
where 
group by to_char(date,'mm') 
//°´¼¾Í³¼Æ 
select to_char(date,'q'),sum() 
fr ......

PowerDesignerÉú³ÉSQL ERͼ ˵Ã÷Îĵµ

Ò»¡¢PowerDesignerÉú³ÉsqlÎÊÌâ
Éú³ÉsqlµÄ·½·¨ÊÇ Database -->Generate Database (Ctrl + G ) µ«ÊÇÌáʾ Could not load VBScript engine.
Check VBScript installation. Generation aborted due to errors detected during the verification of the model. ¼ì²éÁ˺þà ·¢ÏÖ½«check model È¥µô¾Í¿ÉÒÔÁË£¡ÆäÖУ¬ o ......

SQL ServerË÷Òý¹ÜÀíÖ®Áù´óÌúÂÉ


Ô­ÎÄת×Ô:http://tech.it168.com/a2009/0218/265/000000265868.shtml
Ë÷ÒýÊÇÒÔ±íÁÐΪ»ù´¡µÄÊý¾Ý¿â¶ÔÏó¡£Ë÷ÒýÖб£´æ×űíÖÐÅÅÐòµÄË÷ÒýÁУ¬²¢ÇҼͼÁËË÷ÒýÁÐÔÚÊý¾Ý¿â±íÖеÄÎïÀí´æ´¢Î»Öã¬ÊµÏÖÁ˱íÖÐÊý¾ÝµÄÂß¼­ÅÅÐò¡£Í¨¹ýË÷Òý£¬¿ÉÒÔ¼Ó¿ìÊý¾ÝµÄ²éѯËٶȺͼõÉÙϵͳµÄÏìӦʱ¼ä;¿ÉÒÔʹ±íºÍ±íÖ®¼äµÄÁ¬½ÓËٶȼӿ졣
  µ«ÊÇ£¬ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ