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ÀàÐÍ¡£Í¬Ñù£¬ÑØ×ÅÏà·´µÄ·½
Ïà¹ØÎĵµ£º
//--Ìí¼Ó·þÎñÆ÷
EXEC sp_addlinkedserver
@server='LQXLSJ-600A5A60',--±»·ÃÎʵķþÎñÆ÷±ðÃû
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='LQXLSJ-600A5A60' --Òª·ÃÎ ......
XML DOM
XML DOM (XML Document Object Model) ¶¨ÒåÒ»Ì×·ÃÎʺͲÙ×÷ XML ÎĵµµÄ±ê×¼·½·¨¡£
DOM °Ñ XML Îĵµ×÷ΪÊ÷½á¹¹À´²é¿´¡£Äܹ»Í¨¹ý DOM Ê÷À´·ÃÎÊËùÓÐÔªËØ¡£¿ÉÒÔÐ޸Ļòɾ³ýËüÃǵÄÄÚÈÝ£¬²¢´´½¨ÐµÄÔªËØ¡£ÔªËØ£¬ËüÃǵÄÎı¾£¬ÒÔ¼°ËüÃǵÄÊôÐÔ£¬¶¼±»ÈÏΪÊǽڵ㡣
ÔÚÏÂÃæµÄÀý×ÓÖУ¬ÎÒÃÇʹÓà DOM ÒýÓÃ´Ó <to> ÔªËØÖлñÈ ......
ÒªÖªµÀ´Ó¸ù½ÚµãÒ»Ö±ÏÂÀ´µÄÏà¶Ô·¾¶²ÅÄÜÈ·¶¨XpathµÄд·¨¡£
/root/<½Úµã1>/<½Úµã2>//<@ÊôÐÔ>
XpathÊǹ¦ÄܺÜÇ¿´óµÄ£¬µ«ÊÇÒ²ÊÇÏà¶Ô±È½Ï¸´ÔÓµÄÒ»Ãż¼Êõ£¬×îºÃ»¹Êǵ½²©¿ÍÔ°ÉÏÃæÈ¥×¨ÃÅÕÒһЩרҵµÄÌû×ÓÀ´¿´Ò»¿´£¬ÏÂÃæÊÇһЩ¼òµ¥µÄXpathÓï·¨ºÍÒ»¸öʵÀý£¬Ìṩ¸øÄã²Î¿¼Ò»ÏÂ
<?xml version="1.0" encoding="IS ......
Ò»¡¢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 ......
--»ñȡij¸öÊý¾Ý¿âÖеıí½á¹¹
SELECT
--±íÃû=case when a.colorder=1 then d.name else '' end,
ÐòºÅ=a.colorder,
--±êʶ=case when COLUMNPROPERTY(&nbs ......