输出XML的数据为行集(关键技术从MSDN得到)

 

--创建输出行集的用户自定义函数
create function udf_XML2Table (@xCol xml)
returns @ret_Table table (propPK int, propAuthor varchar(max))
with schemabinding
as
begin
      insert into @ret_Table
      select ROW_NUMBER() OVER(ORDER BY nref.value('.', 'varchar(max)')), nref.value('.', 'varchar(max)')
      from   @xCol.nodes('//HumanResources.Department') AS R(nref)
      return
end
go

--输出行集
DECLARE @myXML XML
SET @myXML = (select top 100 [Name] from HumanResources.Department
for xml raw('HumanResources.Department'), elements)

SELECT * FROM udf_XML2Table (@myXML)