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

Question

RusCosinus @RusCosinus

Как создать XML определенного формата из таблицы с помощью FOR 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 всегда одно и тоже в рамках этого блока
Что я хочу получить из этих данных:

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

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

Пробовал но никак не выходит.

Вопрос задан 14 мая
62 просмотра

4 комментария

Подписаться 1 Простой 4 комментария

Akina @Akina

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

Написано 14 мая
RusCosinus @RusCosinus Автор вопроса

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

Написано 14 мая
Akina @Akina

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

Написано 14 мая

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

Написано 14 мая

Пригласить эксперта

Ваш ответ на вопрос

Войдите, чтобы написать ответ

Похожие вопросы

1С

+1 ещё

Простой
Как обойти ошибку создания уникального индекса?
- 1 подписчик
- 06 мая
- 100 просмотров
4

ответа
PHP

+1 ещё

Простой
Как добавить данные в бд с помощью php sqlsrv?
- 1 подписчик
- 18 апр.
- 172 просмотра
2

ответа
SQL Server

+1 ещё

Простой
T-SQL не видит пользовательский тип-таблицу. Каких прав/разрешений не хватает?
- 1 подписчик
- 16 мар.
- 35 просмотров
0

ответов
SQL Server

Простой
Как оптимизировать память?
- 3 подписчика
- 26 февр.
- 140 просмотров
0

ответов
Python

+1 ещё

Простой
Не отображаются данные мигрированные данные, почему???
- 1 подписчик
- 17 февр.
- 120 просмотров
0

ответов
Python

+1 ещё

Простой
Удаленное подключение к SQLserver?
- нет подписчиков
- 12 февр.
- 225 просмотров
1

ответ
SQL Server

Простой
Как продолжить INSERT если значение существует?
- 2 подписчика
- 11 февр.
- 117 просмотров
3

ответа
SQL Server

Простой
Как сделать Insert into с апдейтом PK?
- 1 подписчик
- 07 февр.
- 39 просмотров
1

ответ
PHP

+3 ещё

Простой
Как правильно использовать hreflang для мета тега и для карты сайта?
- 3 подписчика
- 03 февр.
- 684 просмотра
0

ответов
Показать ещё Загружается…

Аналитик, специалист по обработке данных в Excel в стрим Интернет-банк СМБ

ИТ-Холдинг Т1 • Москва

от 150 000 ₽

IT-администратор

Alta Personnel • Москва

До 305 000 ₽

Разработчик PHP, SQL фреймворк Symfony

Сервис электронной оценки • Москва

от 150 000 до 150 000 ₽

А простое FOR XML RAW('Data'), ELEMENTS не устраивает? обязательно требуется field name="column_name"?
Akina,
К сожалению, не устраивает, в том то и дело. Именно в этом формате мои данные должны обрабатываться на другой стороне.
Обычный вариант реализуется легко, но это не то, что нужно. Как это не прискорбно :)
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

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

Войдите, чтобы написать ответ

Минуточку внимания

Войдите на сайт