sqlserver xml转表 及(cross apply与outer apply)

一. 需求是需要把','分割的字符串转为表，便于做关联查询，于是发现可以通过xml转为表，如下：

declare @XXX xml
set @XXX = '
<v>
<aa>12345</aa>
<bb>23456</bb>
</v>
<v>
<aa>54321</aa>
<bb>65432</bb>
</v>'

SELECT F.p.value('.', 'varchar(100)') as ccc,F.p.value('.[1]', 'varchar(5)') as a_a,F.p.value('bb[1]', 'varchar(5)') as b_b,
F.p.query('aa').value('.[1]', 'varchar(5)') as aa,F.p.query('bb').value('.[1]', 'varchar(5)') AS bb 
FROM @XXX .nodes('/v') AS F(p)

下面是字符串转表：

declare @str varchar(200),@split varchar(10)

set  @str='1,2,3,4,5,6' 

set @split=','

 

declare @xml xml

set @xml=convert(xml,'<v>'+replace(@str,',','</v>,<v>')+'</v>')

select val=f.p.value('.',varchar(100)) from @xml.nodes('/v') f(p)--这里不能直接使用convert(xml,'<v>'+replace(@str,',','</v>,<v>')+'</v>')，语法错误，原因不知

 字符串转表还可以通过分割拼接的方式，如下：

create Function StrToTable(@str varchar(1000))
Returns @tableName Table
(
   str2table varchar(50)
)
As
--该函数用于把一个用逗号分隔的多个数据字符串变成一个表的一列，例如字符串'1,2,3,4,5' 将编程一个表，这个表
Begin
set @str = @str+','
Declare @insertStr varchar(50) --截取后的第一个字符串
Declare @newstr varchar(1000) --截取第一个字符串后剩余的字符串
set @insertStr = left(@str,charindex(',',@str)-1)
set @newstr = stuff(@str,1,charindex(',',@str),'')
Insert @tableName Values(@insertStr)
while(len(@newstr)>0)
begin
   set @insertStr = left(@newstr,charindex(',',@newstr)-1)
   Insert @tableName Values(@insertStr)
   set @newstr = stuff(@newstr,1,charindex(',',@newstr),'')
end
Return
End

引用如下：

declare str vchar(100)

set str='1,2,3'

select * from tablename where id in (select str2table from StrToTable(@str) )

二. outer apply和cross apply  交叉查询

outer apply：结果集中将包含使右表表达式为空的左表表达式中的行。

cross apply：结果集中不包含使右表表达式为空的左表表达式中的行。 

测试如下：

IF OBJECT_ID('Employees') IS NOT NULL
DROP TABLE Employees

CREATE TABLE Employees
(
　　empid INT NOT NULL,
　　deptid INT NULL,
　　empname VARCHAR(25) NOT NULL
)
GO

IF OBJECT_ID('Departments') IS NOT NULL
DROP TABLE Departments

CREATE TABLE Departments
(
　　deptid INT NOT NULL PRIMARY KEY,
　　deptname VARCHAR(25) NOT NULL
)
GO


INSERT INTO employees VALUES (1,NULL,'Nancy')
INSERT INTO employees VALUES (2,1,'Andrew')
INSERT INTO employees VALUES (3,2,'Steven')
INSERT INTO employees VALUES (4,3,'Robert')
INSERT INTO employees VALUES (5,3,'Ann')
INSERT INTO employees VALUES (6,4,'Ina')
INSERT INTO employees VALUES (7,4,'David')
INSERT INTO employees VALUES (8,5,'James')

INSERT INTO departments VALUES (1,'HR')
INSERT INTO departments VALUES (2,'Marketing')
INSERT INTO departments VALUES (3,'Finance')
INSERT INTO departments VALUES (4,'R&D')
INSERT INTO departments VALUES (5,'Training')
INSERT INTO departments VALUES (6,'Gardening')
GO

-- cross apply query
SELECT *
FROM departments AS D
cross apply (select * from employees where deptid=D.deptid) E

-- outer apply query 把左侧表departments 有的deptid为6的，emloyees中没有关联的查询出来了。
SELECT *
FROM departments AS D
outer apply (select * from employees where deptid=D.deptid) E