|
阅读：3042
回复：3
|
[已解决]sql语句查询多人xml
SQL语句如何解析多人xml字段，关联User表查询出姓名，用于做报表。(sql server)
产品版本：V9.2 |
|
|
1楼#
发布于：2017-04-13 10:45
帖内置顶 – – 2017-04-24 18:33
哦，多人字段存的是xml,我的处理方法是，自己写了个sql函数。
ALTER FUNCTION [dbo].[SearchUserName]
(
@temp XML
)
RETURNS NVARCHAR(50)
AS
BEGIN
-- Declare the return variable here
DECLARE @nameList NVARCHAR(50)
-- Add the T-SQL statements to compute the return value here
SELECT @nameList =(select Name+',' FROM dbo.OT_User
WHERE ObjectID IN (SELECT temp.value('.', 'nvarchar(50)') AS id
FROM @temp.nodes('/ArrayOfString/string') o(temp)) for xml path('')
)
-- Return the result of the function
RETURN SUBSTRING(@nameList, 1, len(@nameList)-1)
END
|
|
|
|
3楼#
发布于：2017-04-13 10:45
哦，多人字段存的是xml,我的处理方法是，自己写了个sql函数。
ALTER FUNCTION [dbo].[SearchUserName]
(
@temp XML
)
RETURNS NVARCHAR(50)
AS
BEGIN
-- Declare the return variable here
DECLARE @nameList NVARCHAR(50)
-- Add the T-SQL statements to compute the return value here
SELECT @nameList =(select Name+',' FROM dbo.OT_User
WHERE ObjectID IN (SELECT temp.value('.', 'nvarchar(50)') AS id
FROM @temp.nodes('/ArrayOfString/string') o(temp)) for xml path('')
)
-- Return the result of the function
RETURN SUBSTRING(@nameList, 1, len(@nameList)-1)
END
|
|
|
|
4楼#
发布于：2017-04-16 16:57
|
|
|
