sql for xml嵌套查询，怎么出这个错？

[mymantou]

我想查询出来的结果是，架构名节点，后头跟着架构下各表的名字。
select SchemaName.TABLE_SCHEMA as "SchemaName/@name",
(select sys.tables.name as "TableName/@name"
from
sys.tables
where SchemaName.TABLE_NAME= (select sys.schemas.name from
sys.tables,sys.schemas where sys.tables.schema_id=sys.schemas.schema_id)
for xml auto,type,elements)
from
INFORMATION_SCHEMA.TABLES as SchemaName
order by SchemaName.TABLE_SCHEMA
for xml auto,type



提示错误：
子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。

[--小F--]

select SchemaName.TABLE_SCHEMA as "SchemaName/@name",
(select sys.tables.name as "TableName/@name"  
from  
sys.tables
where SchemaName.TABLE_NAME in(select sys.schemas.name from  
sys.tables,sys.schemas where sys.tables.schema_id=sys.schemas.schema_id)
for xml auto,type,elements)
from
INFORMATION_SCHEMA.TABLES as SchemaName
order by SchemaName.TABLE_SCHEMA  
for xml auto,type

[mymantou]

[Quote=引用 1 楼 fredrickhu 的回复:]
SQL code
select SchemaName.TABLE_SCHEMA as "SchemaName/@name",
(select sys.tables.name as "TableName/@name"
from
sys.tables
where SchemaName.TABLE_NAME in(select sys.schemas.name from
sys.……
[/Quote]


谢谢回复！
现在这样的查询结果里没有表名，只有架构名了

[liangCK]

没看语句，你想得到的xml结构是怎样的？

[mymantou]

[Quote=引用 3 楼 liangck 的回复:]
没看语句，你想得到的xml结构是怎样的？
[/Quote]

<SchemaName name="">
<TableName name=""/>
<TableName name=""/>
</SchemaName>

<SchemaName name="">
</SchemaName>

[mymantou]

自己顶上来，还没解决。。。

[liangCK]

select name ,
    (select name from sys.tables 
       where a.schema_id=schema_id
      for xml raw('TableName'),type)
from sys.schemas AS a
for xml raw('SchemaName')

[mymantou]

[Quote=引用 6 楼 liangck 的回复:]
SQL code
select name ,
(select name from sys.tables
where a.schema_id=schema_id
for xml raw('TableName'),type)
from sys.schemas AS a
for xml raw('SchemaName')
[/Quote]


非常感谢，
不过查询出来的包括了系统的架构。正在改

[liangCK]

select name ,
    (select name from sys.tables 
       where a.schema_id=schema_id
      for xml raw('TableName'),type)
from sys.schemas AS a
where exists(select * from sys.tables where a.schema_id=schema_id)
for xml raw('SchemaName')


select name,
    (select name from sys.tables 
       where a.schema_id=schema_id
      for xml raw('TableName'),type)
from (select distinct a.schema_id,b.name 
       from sys.tables as a
           join sys.schemas as b
        on a.schema_id=b.schema_id) as a
FOR xml raw('SchemaName')