使用xml.modify或TSQL中的其他方法更改元素名称

Suppose you have a TSQL query:

假设您有一个TSQL查询:

1 as 'Category1/@Level',
2 as 'Category2/@Level',
t.MainCat as 'Category1', 
t.SubCat as 'Category2'
FOR XML PATH (''), ROOT('Taxonomy')

which generates following result:

产生以下结果:

<Taxonomy>
      <Category1 Level="1">Clothing</Category1>
      <Category2 Level="2">Jeans</Category2>
</Taxonomy>

The element Category1 and Category2 have unique names for the sql query to generate and convert in to xml format. That's why I used Category1 and Category2. I would like the end result to be like this:

元素Category1和Category2具有用于生成和转换为xml格式的sql查询的唯一名称。这就是我使用Category1和Category2的原因。我希望最终结果是这样的:

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

This could be done using XML EXPLICIT like here, but since my code is much larger it becomes very cluttered and complex quite fast.

这可以像这里使用XML EXPLICIT一样完成,但由于我的代码要大得多,所以它变得非常混乱和复杂得很快。

With xml.modify you can change values or attributes, but not the element itself.

使用xml.modify,您可以更改值或属性,但不能更改元素本身。

Is there a way to store the first query result in a @X1 xml variable and then change the element Category1 & Category2 into Category and put it into variable @X2? Something like a search and replace used in text files, but then during the query.

有没有办法将第一个查询结果存储在@ X1 xml变量中,然后将元素Category1和Category2更改为Category并将其放入变量@ X2?类似于在文本文件中使用的搜索和替换,但在查询期间。

2 个解决方案

#1

Also you may use nesting:

您也可以使用嵌套:

select
    (select 1 as '@Level', 'Clothing'
        for xml path('Category'), type),
    (select 2 as '@Level', 'Jeans'
        for xml path('Category'), type)
for xml path('Taxonomy');

or values clause to construct list of categories before forming output xml:

或者在创建输出xml之前构造类别列表的values子句:

select Cat.Level as '@Level', Cat.Value as 'text()'
from (values
        (1, 'Clothing')
        ,(2, 'Jeans')
    ) Cat(Level, Value)
for xml path('Category'), root('Taxonomy');

#2

sneaking a null in between elements can do the trick:

在元素之间偷偷摸摸可以做到这一点:

Select
1 as 'Category/@Level',
t.MainCat as "Category",
null,
2 as 'Category/@Level',
t.SubCat as "Category"
FOR XML PATH (''), ROOT('Taxonomy')

#1