xml - 将 Sql 表转换为 XML

Question

我有一个包含三列的用户表:ID、用户名、电子邮件。

我需要将其转换为以下格式的 XML:

 <Table Name="Users">
      <Column Name='Id'>1</Column>
      <Column Name='Username'>user1</Column>
      <Column Name='Email'>user1@gmail.com</Column>
 </Table>
 <Table Name="Users">
      <Column Name='Id'>2</Column>
      <Column Name='Username'>user2</Column>
      <Column Name='Email'>user2@gmail.com</Column>
 </Table>

到目前为止，我已经写了这个:

select * from Users for xml raw('Table'), Elements, type

这给了我这个结果:

 <Table>
      <Id>1</Id>
      <Username>user1</Username>
      <Email>user1@gmail.com</Email>
 </Table>
 <Table>
      <Id>2</Id>
      <Username>user2</Username>
      <Email>user2@gmail.com</Email>
 </Table>

Answer 1

select 'Users' as [@Name],
        (select 'Id'       as [@Name], Id       as '*' for xml path('Column'), type),
        (select 'Username' as [@Name], Username as '*' for xml path('Column'), type),
        (select 'Email'    as [@Name], Email    as '*' for xml path('Column'), type)
from Users
for xml path('Table'), type

SQL Fiddle