在具有相同元素名称的 FOR XML 路径 T-SQL 查询中多次使用属性

我正在尝试使用 FOR XML 路径在 SQL 2008 中创建 xml 输出。这工作正常：

<Taxonomy>
      <Category Level="1">Clothing</Category>
      <SubCategory Level="2">Jeans</SubCategory>
 </Taxonomy>

但我希望输出是：

<Taxonomy>
      <Category Level="1">Clothing</Category>
      <Category Level="2">Jeans</Category>
</Taxonomy>

当然你也可以编写如下代码：

1 as 'Taxonomy/Category/@Level',
2 as 'Taxonomy/Category/@Level',
t.MainCat as 'Taxonomy/Category', 
t.SubCat as 'Taxonomy/Category',

但这给出了错误消息：以属性为中心的列 '列名重复。同一属性不能在同一 XML 标记上生成多次。

可以做什么来获得所需的输出？ 子选择有效还是某种交叉适用？或者也许是一个工会？但如何呢？

---- 编辑 - 经过几个答案后得出以下解决方案：

SELECT 
1 as 'Category/@Level',
t.Cat as 'Category'
FROM table t

UNION

SELECT 
2 as 'Category/@Level',
t.SubCat as 'Category'
FROM table t

FOR XML PATH (''), ROOT('Taxonomy')

给出这个输出：

<Taxonomy>
  <Category Level="1">Clothing</Category>
  <Category Level="2">Jeans</Category>
</Taxonomy>

仍然需要弄清楚如何将这部分编码放入已经有多个“嵌套”FOR XML 的更大代码中

捷径方法可能无法解决这个问题。 AUTO 和 PATH 不喜欢多个同名元素。看起来你必须使用对于 XML 显式 http://msdn.microsoft.com/en-us/library/ms189068.aspx命令。

它有效，但很麻烦。

Sample:

--Generate Sample Data
--FOR XML EXPLICIT requires two meta fields: Tag and Parent
--Tag is the ID of the current element.
--Parent is the ID of the parent element, or NULL for root element.

DECLARE @DataTable as table
   (Tag int NOT NULL
   , Parent int
   , TaxonomyValue nvarchar(max)
   , CategoryValue nvarchar(max)
   , CategoryLevel int)

--Fill with sample data: Category Element (2), under Taxonomy(1), with no Taxonomy value.
INSERT INTO @DataTable
VALUES (2, 1, NULL, 1, 'Clothing')
     , (2, 1, NULL, 2, 'Jeans')

--First part of query: Define the XML structure
SELECT
   1 as Tag  --root element
   , NULL as Parent
   , NULL as [Taxonomy!1]       --Assign Taxonomy Element to the first element, aka root.
   , NULL as [Category!2]       --Assign Category Element as a child to Taxonomy.
   , NULL as [Category!2!Level] --Give Category an Attribute 'Level'

--The actual data to fill the XML
UNION
SELECT
   Data.Tag
   , Data.Parent
   , Data.TaxonomyValue
   , Data.CategoryValue
   , Data.CategoryLevel
FROM
   @DataTable as Data
FOR XML EXPLICIT

生成 XML

<Taxonomy>
  <Category Level="1">Clothing</Category>
  <Category Level="2">Jeans</Category>
</Taxonomy>

编辑：列颠倒了。不再有牛仔裤的水平。