SQl 2005 For XMl ¼òµ¥²éѯ(Raw£¬Auto£¬Pathģʽ)(2)
ÎÄÕµ¼º½ SQL Server 2005 ѧϰ±Ê¼ÇϵÁÐÎÄÕµ¼º½
ÔÚSQl 2005 For XMl ¼òµ¥²éѯ(Raw£¬Auto£¬Pathģʽ)(1) ÀïÎÒÃÇ˵Á˹ØÓÚPath£¬RawºÍAutoģʽµÄÓ÷¨£¬ÆäʵÀïÃæ²»½ö½ö ÊÇÕâЩ¼òµ¥µÄ²Ù×÷£¬»¹ÓÐһЩÆäËüµÄÌØÐÔ£¬±ÈÈç˵Type»òOpenXml·½·¨£¬sp_xml_preparedocument´æ´¢¹ý³Ì µÈÕâЩÔö¼ÓµÄ¶«¶«£¬ÎÒÃÇÀ´Ò»¸öÒ»¸öµÄ¿´°É£¬
µÚÒ»¸öType¹Ø¼ü×Ö£¬Type´ó¼Ò¶¼ ÖªµÀ Ó¢ÎÄÒâ˼ÊÇÀàÐÍ£¬ÔÚÕâÀïÒ²ÊǺÍÀàÐÍÏà¹ØµÄ£¬Òâ˼¾ÍÊÇ˵ÈÃ×Ó¼¯ÀïÃæÀàÐͺͼ¯ºÏµÄÀàÐÍͳһ£¬¾ßÌå ÊÇÔõôͳһµÄÕâ¸öÎÒÒ²²»¶®£¬´ó¼Ò»¹µÃ×Ô¼ºÈ¥²é»òÊÇÕÒÕÒMSµÄÍøÕ¾°É£¬ÎÒÃÇ»¹ÓÃÉÏÒ»½ÚµÄÊý¾Ý¿âºÍ±íÀ´ÊµÏÖ¾ßÌå±íµÄ½á¹¹»¹Çë´ó¼Ò²Î¿¼SQl 2005 For XMl ¼òµ¥²éѯ(Raw£¬Auto£¬Pathģʽ)(1)ÀïÃæµÄ ÎÒÃÇÀ´×öдһ¸öÕâÑùµÄ×Ó²éѯ
SELECT SID,SName,(SELECT ClassInfo.CName from ClassInfo WHERE ClassInfo.CID=Students.CID FOR xml raw)
from Students
ORDER BY CID FOR xml auto
ÎÒÃÇ¿ÉÒÔÇå³þµÄ¿´µ½×Ó²éѯ·µ»ØµÄÊÇÒ»¸öXmlÀàÐ͵ĶøÍâÃæµÄ¼¯ºÏÒ²ÊÇÒ»¸öXmlÀàÐ͵ģ¬Õâ¸öʱºòÈç¹ûÎÒÃÇÖ±½ÓÖ´ÐеĻ°¾Í»á·¢Éú´íÎó
ÏûÏ¢ 6809£¬¼¶±ð 16£¬×´Ì¬ 1£¬µÚ 1 ÐÐ
²»Äܽ«Î´ÃüÃûµÄ±íÓÃ×÷ XML ±êʶ·û£¬Ò²²»Äܽ«Î´ÃüÃûµÄÁÐÓÃÓÚÊôÐÔÃû³Æ¡£ÇëÔÚ SELECT Óï¾äÖÐʹÓà AS ¶ÔδÃüÃûµÄÁÐ/±í½øÐÐÃüÃû¡£
ÎÒÃÇÖªµÀ Èç¹ûÊÇÕý³£µÄÇé¿öÏÂÊÇ¿ÉÒԵģ¬Î޷ǾÍÊÇÕâÒ»ÁÐûÓÐÁÐÃû£¬µ«ÊÇÊDz»»á±¨´íµÄ£¬ÄÇÕâ¸öʱºòÎÒÃǾͿÉÒÔÕâÑùÀ´Ð´ÁË
SELECT SID,SName,(SELECT ClassInfo.CName from ClassInfo WHERE ClassInfo.CID=Students.CID FOR xml raw,type)
from Students
ORDER BY CID FOR xml auto
Ö»ÒªÔÚ×Ó¼¯µÄºóÃæ¼ÓÉÏÒ»¸öType¾Í¿ÉÒÔÁË£¬ÎÒÃǵÄType¾ÍÊÇÔÚÕâÖÖÇé¿öÏÂʹÓõģ¬Ò²¿ÉÄÜÊÇÎÒÁ˽âµÄÌ«ÉÙÁË£¬ÎÒ¸öÈ˸оõ Õâ¸ö¶«¶«ÊÇûÓÐʲô̫´óµÄÓô¦µÄ£¬ÎÒÃÇ¿ÉÒÔÕâÑùÀ´Ð´
SELECT SID,SName,(SELECT ClassInfo.CName from ClassInfo WHERE ClassInfo.CID=Studen
Ïà¹ØÎĵµ£º
-- ˵Ã÷:
-- 1. Ҫɾ³ý±í table_name, ÐëÏÈÅжϸñíÊÇ·ñÕý±»Êý¾Ý¿âÖÐÆäËü±íËùÒýÓÃ.
-- 2. Èç¹ûδ±»ÒýÓÃ, ¿ÉÖ±½Ó DROP TABLE table_name; ·ñÔò±ØÐëÏÈɾ³ýÒýÓñíµÄÔ¼Êø, ÔÙ DROP TABLE table_name.
-- 3. ÁíÍâ, table_name ÊÇ·ñÕýÒýÓÃÆäËü±íµÄÇé¿öÎÞÐ뿼ÂÇ.
-- ´´½¨Ö÷±í
CREATE TABLE tParent
......
СµÜÏëÎʸöÎÊÌ⣬ÔÚSQL Server 2005ÉÏ£¬½¨Á¢Ö÷¼üµÄͬʱ¾Í»áĬÈÏÔÚÖ÷¼üÉÏÉèÖþ۴ØË÷Òý£¬ÄÇôÄÜ·ñÔÚÒ»¸ö×Ö¶ÎÉÏÉèÖÃÖ÷¼üºÍΨһÐÔË÷Òý£¨Unique£©£¬µ«ÊǰѾ۴ØË÷ÒýÉèÖõ½ÁíÒ»¸ö×Ö¶ÎÉÏ£¿
¾Ù¸ö¼òµ¥µÄÀý×Ó£¬±ÈÈçÎÒÓиö±í½ÐTableTest£¬±íÀïÓÐÁ½¸ö×ֶΣ¬id,date£¬±¾À´idÊÇÖ÷¼üµÄ£¬ÎÒÏÖÔÚÏë°Ñ¾Û´ØË÷ÒýÉèÖõ½date×Ö¶ÎÉÏ¡£
ÎÒÏÈɾ³ýÖ ......
´æ´¢¹ý³Ì
==========================================================================
==========================================================================
¡¾ÑµÁ·15.1¡¿ ´´½¨Ò»¸öÏÔʾ¹ÍÔ±×ÜÈËÊýµÄ´æ´¢¹ý³Ì¡£
²½Öè1£ºµÇ¼SCOTTÕË»§(»òѧÉú¸öÈËÕË»§)¡£
²½Öè2£º ......
Martin C. Brown (questions@mcslp.com), ¿ª·¢ÈËÔ±ºÍ×÷¼Ò
2008 Äê 6 ÔÂ 16 ÈÕ
ʹÓñ¾ÎĽéÉÜµÄ 10 ¸ö¼¼Çɸü¼ÓÇáËɵش¦Àí XML£¬´Ó¶ø¼õÉÙ´íÎó²¢Ìá¸ßЧÂÊ¡£
¼ò½é
ÈËÃǶ¼ºÜϲ»¶ XML ÒÔ¼°ËüËùÌṩµÄÁé»îÐԺͻ¥²Ù×÷ÐÔ£¬µ«ÊÇ£¬Í¨¹ýʹÓÃһЩ¼¼ÇÉ£¬¿ÉÒÔʹÓë XML µÄ»¥²Ù×÷ºÍÓë XML Ò»ÆðʹÓõŤ¾ß¸ü¼Ó¼òµ¥¡£ÔÚ´¦Àí XML ʱÑø³ÉһР......
ʹÓÃ×Ô¶¨Òå±íÀàÐÍ£¨SQL Server 2008£©
http://tech.ddvip.com 2009Äê09ÔÂ19ÈÕ À´Ô´£º²©¿ÍÔ° ×÷Õߣº³ÂÏ£ÕÂ
¡¡¡¡ÔÚ SQL Server 2008 ÖУ¬Óû§¶¨Òå±íÀàÐÍÊÇÖ¸Óû§Ëù¶¨ÒåµÄ±íʾ±í½á¹¹¶¨ÒåµÄÀàÐÍ¡£Äú¿ÉÒÔʹÓÃÓû§¶¨Òå±íÀàÐÍΪ´æ´¢¹ý³Ì»òº¯ÊýÉùÃ÷±íÖµ²ÎÊý£¬»òÕßÉùÃ÷ÄúÒªÔÚ ......