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

sqlserver FOR XML PATH Óï¾äµÄÓ¦ÓÃ

´úÂëÈçÏÂ:

DECLARE
@TempTable table(UserID int , UserName nvarchar(50));
insert into
@TempTable (UserID,UserName) values (1,'a')
insert into @TempTable
(UserID,UserName) values (2,'b')

select UserID,UserName
from @TempTable FOR XML PATH
ÔËÐÐÕâ¶Î½Å±¾£¬½«Éú³ÉÈçϽá¹û£º
¸´ÖÆ´úÂë
´úÂëÈçÏÂ:

<row>
<UserID>1</UserID>

<UserName>a</UserName>
</row>
<row>

<UserID>2</UserID>
<UserName>b</UserName>

</row>

´ó¼Ò¿ÉÒÔ¿´µ½Á½ÐÐÊý¾ÝÉú³ÉÁËÁ½¸ö½Úµã£¬ÐÞ¸ÄÒ»ÏÂPATHµÄ²ÎÊý£º
select
UserID,UserName from @TempTable FOR XML PATH('lzy')
ÔÙ´ÎÔËÐÐÉÏÊö½Å±¾£¬½«Éú³ÉÈçϵÄ
½á¹û£º
¸´ÖÆ´úÂë
´úÂëÈçÏÂ:

<lzy>
<UserID>1</UserID>

<UserName>a</UserName>
</lzy>
<lzy>

<UserID>2</UserID>
<UserName>b</UserName>

</lzy>

¿ÉÒÔ¿´µ½½Úµã±ä³É£¬ÆäʵPATH()
À¨ºÅÄڵIJÎÊýÊÇ¿ØÖƽڵãÃû³ÆµÄ£¬ÕâÑùµÄ»°´ó¼Ò¿ÉÒÔ¿´Ò»ÏÂÈç¹ûÊÇ¿Õ×Ö·û´®£¨²»ÊÇûÓвÎÊý£©»áÊÇʲô½á¹û?
select
UserID,UserName from @TempTable FOR XML PATH('')
Ö´ÐÐÉÏÃæÕâ¶Î½Å±¾½«Éú³É½á¹û£º
¸´ÖÆ´úÂë
´úÂëÈçÏÂ:

<UserID>1</UserID>
<UserName>a</UserName>

<UserID>2</UserID>
<UserName>b</UserName>


ÕâÑù¾Í²»ÏÔʾÉϼ¶½ÚµãÁË£¬´ó¼ÒÖªµÀÔÚ PATH ģʽÖУ¬ÁÐÃû»òÁбðÃû±»×÷Ϊ XPath
±í´ïʽÀ´´¦Àí£¬Ò²¾ÍÊÇ˵£¬ÊÇÁеÄÃû×Ö£¬ÕâÑù´óµ¨ÊÔÑéһϲ»¸øÖ¸¶¨ÁÐÃûºÍ±ðÃû»áÊÇÔõôÑù£¿
select CAST(UserID AS
varchar) + '',UserName + '' from @TempTable FOR XML PATH('')
ÔËÐÐÉÏÃæÕâ¾ä½«
Éú³É½á¹û
1a2b
ËùÓÐÊý¾Ý¶¼Éú³ÉÒ»ÐУ¬¶øÇÒ»¹Ã»ÓÐÁ¬½Ó×Ö·û£¬ÕâÑùµÄÊý¾Ý¿ÉÄܶԴó¼ÒûÓÐÓô¦£¬»¹¿ÉÒÔÔٱ仯һÏ£º
select
CAST(UserID AS varchar) + ',',UserName + '',';' from @TempTable FOR XML
PATH('')
Éú³É½á¹û
1,a;2,b;
´ó¼ÒÏÖÔÚÃ÷°×ÁË°É£¬¿ÉÒÔͨ¹ý¿ØÖƲÎÊýÀ´Éú³É×Ô¼ºÏëÒªµÄ½á¹û£¬
ÀýÈ磺
select '{' + CAST(UserID AS varchar) + ',','"' +UserName +
'"','}' from @TempTable FOR XML PATH('')
Éú³É½á¹û
{1,"a&q


Ïà¹ØÎĵµ£º

ÔÚVisual C#ÖÐʹÓÃXMLÖ¸ÄÏÖ®¶ÁÈ¡XML

ÎÒÃǽñÌìÀ´½²Ò»Ï¸ÃÌåϵ½á¹¹ÖеÄXmlTextReaderÀ࣬¸ÃÀàÌṩ¶ÔXmlÎļþ½øÐжÁÈ¡µÄ¹¦ÄÜ£¬Ëü¿ÉÒÔÑéÖ¤ÎĵµÊÇ·ñ¸ñʽÁ¼ºÃ£¬Èç¹û²»ÊǸñʽÁ¼ºÃµÄXmlÎĵµ£¬¸ÃÀàÔÚ¶ÁÈ¡¹ý³ÌÖн«»áÅ׳öXmlExceptionÒì³££¬¿ÉʹÓøÃÀàÌṩµÄһЩ·½·¨¶ÔÎĵµ½Úµã½øÐжÁÈ¡£¬É¸Ñ¡µÈ²Ù×÷ÒÔ¼°µÃµ½½ÚµãµÄÃû³ÆºÍÖµ£¬ÇëÀμǣºXmlTextReaderÊÇ»ùÓÚÁ÷Ä£Ð͵ÄʵÏÖ£¬´ò¸ö ......

ÔÚ²»Í¬sqlserver·þÎñÆ÷¼ä£¬´´½¨ÏàͬÊý¾Ý¿â¶ÔÏó¡£

·ÖÁ½ÖÖÇé¿ö£º
1¡¢Í¨¹ý½Å±¾·½Ê½£¬2¡¢µ¼Èëµ¼³ö·½Ê½
µ«²»Í¬°æ±¾µÄSqlServer,ÔÚÕâ·½Ãæ²¢²»Ïàͬ¡£
sql2000¶ÔÊý¾Ý¿â¶ÔÏóÖ´ÐÐÉú³É½Å±¾Ê±£¬¾¡¹Ü¶¼Ñ¡Éϸ÷¸öÌõ¼þ£¬µ«¶ÔÓÚ±í¶ÔÏó²¢²»ÄÜ´´½¨Ö÷¼ü
¿ÉÒÔͨ¹ýµ¼Èëµ¼³ö·½Ê½£¬ÔÚµ¼ÈëÄ¿±êÑ¡ÔñÄ¿±êsqlserverºó£¬Ñ¡ÔñÖ»µ¼³ö¶ÔÏó½á¹¹¾ÍºÃÁË¡£
sql2005¶ÔÊý¾Ý¿â¶ÔÏóÖ´ÐÐÉú³É½Å±¾Ê±£¬¿ÉÒÔ´´½¨Ö÷¼ ......

һЩSqlServerÓï¾ä

Ò»¡¢»ù´¡
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREATE DATABASE database-name
2¡¢ËµÃ÷£ºÉ¾³ýÊý¾Ý¿â
drop database dbname
3¡¢ËµÃ÷£º±¸·Ýsql server
--- ´´½¨ ±¸·ÝÊý¾ÝµÄ device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- ¿ªÊ¼ ±¸·Ý
BACKUP DATABASE pubs TO testBack
4¡¢Ëµ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ