CODEWITHSUNDEEP
sqlxmlt-sqlxml.modify
J3FFK
J3FFK

Reputation: 684

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

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.

Upvotes: 2

Views: 754

Answers (2)

i-one
i-one

Reputation: 5120

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

Upvotes: 1

Jayvee
Jayvee

Reputation: 10873

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

Upvotes: 2

Related Questions