Change element name with xml.modify or other method in TSQL

Question

Suppose you have a TSQL query:

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:

<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.

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

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.

score 2 · Answer 1 · answered Jul 30 '14 at 17:39

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')

answered Jul 30 '14 at 17:39

Jayvee

10,670
3
29
40

thanks, wow! All it took was an extra NULL?! And it doesn't harm the code in anyway apperently. That's a neat little trick. – J3FFK Jul 31 '14 at 13:40
However, in my larger code it takes out the structure of the xml. The NULL 'undoubles' the column/element name, but also unties the relation between them, so I have to go for i-one answer, sorry. – J3FFK Jul 31 '14 at 13:49
maybe the larger code need some tweak or reordering to work as well to apply this solution, but since it's now working with a different approach no need to fix what is not broken. – Jayvee Jul 31 '14 at 14:51

score 1 · Accepted Answer · answered Jul 30 '14 at 18:17

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:

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

answered Jul 30 '14 at 18:17

i-one

5,050
1
28
40

Tried both ways, and the subselect method worked very nice, also as a part of much larger query. It kind of pastes them together in one large xml. Thanks! – J3FFK Jul 31 '14 at 13:50

Change element name with xml.modify or other method in TSQL

2 Answers2