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
Ïà¹ØÎĵµ£º
×î½ü¿´µ½ÁËÒ»¸öÏîÄ¿½âÎöxmlµÄ·½Ê½£¬¾õµÃÒ²²»´í£¬²âÊÔϸú´ó¼Ò·ÖÏíÏ£¡ÑÛϽâÎöxmlµÄ·½Ê½ºÜ¶à£¬ÕâÀï²»ÌáÁË£¡Ö»Ì¸Ì¸digesterµÄʹÓã¬Ç³Ì¸°É£¡Ï£ÍûÒÔºóÄãÃÇÓöµ½ÁËÄÜÓеã°ïÖú°É£¡
Ê×ÏÈÔÚʹÓÃDigester½âÎöxmlʱҪµ¼Èë2¸öjar°ü£¬ÓеÄÈË˵ÊÇ4¸ö£ ......
³£ÓõÄϵÁл¯¶¨Ò壨using System.Xml.Serialization;£©
[XmlAttribute("name")] // ¶¨Òå<Tag name="…"></Tag>
[XmlElement("label")] // ¶¨Òå<label>…</label>
[XmlIgnoreAttrib ......
±¾À´ÎÒÊDz»ÔÞ³ÉʹÓÃͨÓô洢¹ý³ÌµÄ£¬Ö÷ÒªÊÇÒòΪ¸ù¾Ý±í½á¹¹À´¶¨ÖÆ·ÖÒ³²éѯ²»Óö¯Ì¬µÄÆ´SQL£¬ÕâÑù²ÅÊÇÕæÕýµÄ¸ßЧ£¬¶øÇÒֻҪд¹ýÒ»¸ö£¬ÄÇôÔÙÓÐÐÂÐèÇóµÄʱºò£¬Ð¡·¶Î§¸Ä¶¯¼¸´¦¾ÍokÁË¡£
µ«×ÜÊÇÓÐÈËÏòÎÒÌÖÒª»òÕßÌÖÂÛͨÓô洢¹ý³Ì£¬Ã»°ì·¨£¬±»±ÆÎÞÄΣ¬Á¼ÐÄÉ¥ÓëÀ§¾³¡£
ľÓÐÕÒµ½T-SQL´úÂë±à¼Æ÷
-- ============================= ......
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
create function [dbo].[split](@str nvarchar(1000),@word varchar(5),@no
int) returns nvarchar(500)
as begin
declare @len int
declare @index int
set @index=charindex(@word,@str)
  ......