Как создать XML определенного формата из таблицы MSSQL с помощью FOR XML PATH?

Question

[RusCosinus]

Добрый день.

Никак не получается создать XML из таблицы формата, который мне нужен. На хабре нашел вроде хорошую статью по этому вопросу, но даже с ее использованием не выходит, такое ощущение, что я никак не могу понять суть, как вообще работает это xml path. Помогите, кто разбирается

Код создания таблиц для тестов (данные от балды, просто чтобы показать суть того, что я имею и что хочу получить):

if object_id('dbo.TestAutoInfo', 'U') is not null
    drop table dbo.TestAutoInfo;
go

create table dbo.TestAutoInfo
(
	CityID int
   ,CarBrand varchar(20)
   ,CarModel varchar(20)
   ,CarBodyType varchar(20)
   ,CarYear smallint
   ,CarColor varchar(20)
)
go

insert into dbo.TestAutoInfo 
(
	CityID
   ,CarBrand
   ,CarModel
   ,CarBodyType
   ,CarYear
   ,CarColor
)
values
(1283,'Toyota','Corolla','Sedan',2013,'White')
,(1283,'Toyota','Rav4','SUV',2021,'Black')
,(1283,'Ford','Mustang','Coupe',2024,'Red')
go

select CityID
   ,CarBrand
   ,CarModel
   ,CarBodyType
   ,CarYear
   ,CarColor
from TestAutoInfo

Примечание к данным - CityID всегда одно и тоже в рамках этого блока

Что я хочу получить из этих данных:

1) В идеале вот такое

<data>
	<field name="CityID">1283</field>
</data>	
<data>
	<field name="CarBrand">Toyota</field>
	<field name="CarModel">Corolla</field>
	<field name="CarBodyType">Sedan</field>
	<field name="CarYear">2013</field>
	<field name="CarColor">White</field>
</data>
<data>
	<field name="CarBrand">Toyota</field>
	<field name="CarModel">Rav4</field>
	<field name="CarBodyType">SUV</field>
	<field name="CarYear">2021</field>
	<field name="CarColor">Black</field>
</data>
<data>
	<field name="CarBrand">Ford</field>
	<field name="CarModel">Mustang</field>
	<field name="CarBodyType">Coupe</field>
	<field name="CarYear">2024</field>
	<field name="CarColor">Red</field>
</data>

Но если не такое, то хотя бы вот такое

<data>
  <field name="CityID">1283</field>
  <field name="CarBrand">Toyota</field>
  <field name="CarModel">Corolla</field>
  <field name="CarBodyType">Sedan</field>
  <field name="CarYear">2013</field>
  <field name="CarColor">White</field>
</data>
<data>
  <field name="CityID">1283</field>	
  <field name="CarBrand">Toyota</field>
  <field name="CarModel">Rav4</field>
  <field name="CarBodyType">SUV</field>
  <field name="CarYear">2021</field>
  <field name="CarColor">Black</field>
</data>
<data>
  <field name="CityID">1283</field>
  <field name="CarBrand">Ford</field>
  <field name="CarModel">Mustang</field>
  <field name="CarBodyType">Coupe</field>
  <field name="CarYear">2024</field>
  <field name="CarColor">Red</field>
</data>

Пробовал по разному, но никак не выходит. Если никто не сможет помочь кодом, то может хотя бы накидайте ссылок, пожалуйста, на адекватные статьи как работает этот "%:/** @ XML PATH с кучей примеров, а не просто два-три.

Comments

[Akina]

А простое FOR XML RAW('Data'), ELEMENTS не устраивает? обязательно требуется field name="column_name"?

[RusCosinus]

Akina,
К сожалению, не устраивает, в том то и дело. Именно в этом формате мои данные должны обрабатываться на другой стороне.
Обычный вариант реализуется легко, но это не то, что нужно. Как это не прискорбно :)

[Akina]

RusCosinus, по-моему, вам будет проще собрать нужный текст обычными строковыми функциями. Тем более что требуемый формат простой до задницы...

[RusCosinus]

Akina, у меня есть решение, найденное кем-то давно в интернете и использованное в коде. Но на мой взгляд оно очень кривое и плюс еще нужен курсор. Если вам интересно выглядит оно так:

declare
	@xml xml
	,@xml_f xml

select @xml =
(
select CityID as "CityID"
	   ,CarBrand as "CarBrand"
	   ,CarModel as "CarModel"
	   ,CarBodyType as "CarBodyType"
	   ,CarYear as "CarYear"
	   ,CarColor as "CarColor"
from TestAutoInfo
for xml path('data')  
)

--выведем промежуточный XML
select @xml

    declare @fields table(
     id int not null
    ,fname varchar(100)
    ,flabel varchar(100)
    )

    insert into @fields(id, fname, flabel)
    select 1, 'CityID', 'CityID'
    union all
    select 2, 'CarBrand', 'CarBrand'
    union all
    select 3, 'CarModel', 'CarModel'
    union all
    select 4, 'CarBodyType', 'CarBodyType'
    union all
    select 5, 'CarYear', 'CarYear'
    union all
    select 6, 'CarColor', 'CarColor'

	--Какая-то неизвестная магия
	set @xml_f = (
				select c.col_name as 'field/@name'
						,isnull(@xml.value('(//*[local-name()=sql:column("c.col_name")])[1]','varchar(max)'),'') as 'field'
				from (select fname as col_name from @fields) c
				for xml path(''), root('data')
				)

	--Выведем окончательный XML и поймем, что он обрабатывает только один блок, поэтому и нужен курсор
	select @xml_f