declare @id varchar(20),@name varchar(20),@xml xml
set @id='All'
set @name='全選'
set @xml=(select note.GP_CODE 'ID',note.GP_NAME 'Name',item.COMPANY_CODE 'ID',item.COMPANY_NAME 'Name',items.DEPT_CODE 'ID',items.DEPT_NAME 'Name' from GP note join COMPANY item
on item.GP_CODE=note.GP_CODE join (select * from DEPT where DEPT_GP is not null ) items
on note.GP_CODE=items.DEPT_GP order by note.GP_CODE for xml auto,root('root'))
set @xml.modify('insert attribute ID{sql:variable(''@id'')} as last into (/root)[1]') --向根节点添加ID属性
set @xml.modify('insert attribute Name{sql:variable(''@name'')} as last into (/root)[1]') --向根节点添加Name属性
select @xml