FOR XML PATH
FOR XML PATH 是将查询结果集以XML形式展现,将多行的结果,展示在同一行
SELECT * FROM dbo.UserList FOR XML PATH;
展示结果:
<row><ID>1</ID><Sex>F</Sex><Name>Wang</Name><Age>23</Age></row>
<row><ID>2</ID><Sex>M</Sex><Name>Zhang</Name><Age>32</Age></row>
<row><ID>3</ID><Sex>M</Sex><Name>Li</Name><Age>12</Age></row>
若想要将数据包含在自己定义的标签里,则给Path加上参数即可
SELECT * FROM dbo.UserList FOR XML PATH('User');
展示结果:
<User><ID>1</ID><Sex>F</Sex><Name>Wang</Name><Age>23</Age></User>
<User><ID>2</ID><Sex>M</Sex><Name>Zhang</Name><Age>32</Age></User>
<User><ID>3</ID><Sex>M</Sex><Name>Li</Name><Age>12</Age></User>
若不需要形成XML格式的数据,只需要数据,则可以写成
SELECT ','+Name FROM dbo.UserList FOR XML PATH('');
展示结果:
,Wang,Zhang,Li
也可以用其他形式隔开,不单单是逗号,也可以用其他符号进行间隔,但如果是‘<’,‘>’ 这类字符则会在存储的时候会发生转换
SELECT '<>'+Name FROM dbo.UserList FOR XML PATH('');
展示结果:
lt;>Wanglt;>Zhanglt;>Li
此时若不想要符号发生转变,则需要写成
select (select '<>'+Name from dbo.UserListfor xml path(''),type).value('.','NVARCHAR(MAX)')
结果为:
<>Wang<>Zhang<>Li
STUFF()函数
FOR XML PATH 总是搭配STUFF()使用
STUFF()函数用于删除指定长度的字符,并可以在指定的起点处插入另一组字符。
如果开始位置或长度值是负数,或若开始位置大于第一个字符串的长度,将返回空字符串。
如果要删除的长度大于第一个字符串的长度,将删除到最后一个字符串中的第最后一个字符。
STUFF ( character_expression , start , length ,character_expression )
character_expression:字符数据表达式。
start :整数值,指定删除和插入的开始位置。为负,则返回空字符串。如果 start 比第一个 character_expression
长,则返回空字符串。length:整数值,指定要删除的字符数。为负,则返回空字符串。如果 length 比第一个 character_expression
长,则最多删除到最后一个character_expression 中的最后一个字符。
以下结果可以进行对比
select STUFF(',123',1,0,'abc') -- abc,123
select STUFF(',123',1,1,'abc') -- abc123
select STUFF(',123',1,3,'abc') -- abc3
select STUFF(',123',2,3,'abc') -- ,abc
与FOR XML PATH一起使用
select STUFF((SELECT ','+Name FROM dbo.UserList FOR XML PATH('')),1,1,'')
展示结果:
Wang,Zhang,Li
select STUFF( (SELECT ' OR '+CASE WHEN name='ID' THEN NULL ELSE '('+name+'<>'''' AND '+name+' IS NOT NULL)' END
FROM syscolumns WITH(NOLOCK)
WHERE id=OBJECT_ID('dbo.UserList') FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),2,2,'')
结果:
(Sex<>'' AND Sex IS NOT NULL) OR (Name<>'' AND Name IS NOT NULL) OR (Age<>'' AND Age IS NOT NULL)