Создание динамических имен тегов XML для вывода из SQL
(Это продолжение Управление именами тегов и структурой вывода в SQL, на которое хорошо ответил Ицхак Хабинский)
Я пытаюсь извлечь данные из Microsoft SQL Server 2016 в определенных форматах XML. Я могу приблизиться к стандартным операторам FOR XML, но у меня нет опыта использования XQuery, который, как я подозреваю, лежит в основе элегантного решения.
-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, FirstName
VARCHAR(20), MiddleName VARCHAR(20), LastName VARCHAR(20));
INSERT @tbl (FirstName, MiddleName, LastName) VALUES
('Fred', 'A.','Smith'),
('Anna', NULL,'Polack');
-- DDL and sample data population, end
select * from @tbl
;WITH rs(x) AS
(
SELECT *
FROM @tbl
FOR XML PATH(''), TYPE, ROOT('root')
)
SELECT
x.query('for $x in /root/*
return <Answer name = "{local-name($x)}">
<value>{data($x)}</value>
</Answer>') AS Result
FROM rs
Производит следующее:
<Answer name = "ID">
<value>1</value>
</Answer>
<Answer name = "FirstName">
<value>Fred</value>
</Answer>
<Answer name = "MiddleName">
<value>A.</value>
</Answer>
<Answer name = "LastName">
<value>Smith</value>
</Answer>
<Answer name = "ID">
<value>2</value>
</Answer>
<Answer name = "FirstName">
<value>Anna</value>
</Answer>
<Answer name = "LastName">
<value>Polack</value>
</Answer>
Я хотел бы получить более явное перечисление вывода, например следующее:
<Answer name = "ID_1">
<value>1</value>
</Answer>
<Answer name = "FirstName_1">
<value>Fred</value>
</Answer>
<Answer name = "MiddleName_1">
<value>A.</value>
</Answer>
<Answer name = "LastName_1">
<value>Smith</value>
</Answer>
<Answer name = "ID_2">
<value>2</value>
</Answer>
<Answer name = "FirstName_2">
<value>Anna</value>
</Answer>
<Answer name = "LastName_2">
<value>Polack</value>
</Answer>
Я могу взломать его, используя:
select STRING_AGG('<Answer name = "'+ColKey+'">'+
'<value>'+ColValue+'</value></Answer>',' ')
from
(
select tab.* from @tbl
CROSS APPLY (
VALUES ('FirstName_'+cast(ID as varchar), FirstName),
('LastName_'+cast(ID as varchar), LastName)
) tab(ColKey, ColValue)
)t
Но я уверен, что есть более элегантный способ использования XQuery.
Ответы 2
Нет необходимости в string_agg()
Пример
Select [Answer/@name] = concat('ID','_',id)
,[Answer/value] = id
,null
,[Answer/@name] = concat('FirstName','_',id)
,[Answer/value] = FirstName
,null
,[Answer/@name] = concat('MiddleName','_',id)
,[Answer/value] = MiddleName
,null
,[Answer/@name] = concat('LastName','_',id)
,[Answer/value] = LastName
From @tbl
For xml path('')
Полученные результаты
<Answer name = "ID_1">
<value>1</value>
</Answer>
<Answer name = "FirstName_1">
<value>Fred</value>
</Answer>
<Answer name = "MiddleName_1">
<value>A.</value>
</Answer>
<Answer name = "LastName_1">
<value>Smith</value>
</Answer>
<Answer name = "ID_2">
<value>2</value>
</Answer>
<Answer name = "FirstName_2">
<value>Anna</value>
</Answer>
<Answer name = "MiddleName_2" />
<Answer name = "LastName_2">
<value>Polack</value>
</Answer>
ОБНОВЛЕНИЕ: для многих столбцов. Поскольку у вас есть string_agg()... Я могу предположить, что у вас есть OPENJSON.
Пример
Select [Answer/@name] = concat([key],'_',A.ID)
,[Answer/value] = B.value
From @tbl A
Cross Apply (
Select [Key]
,Value
From OpenJson( (Select A.* For JSON Path,Without_Array_Wrapper ) )
) B
For xml path('')
Спасибо, Джон. Однако в моей конечной таблице много столбцов, поэтому я надеялся найти способ избежать необходимости называть каждый из них конкретно. С вашим подходом есть ли способ избежать нулей? (например, Отчество_2)?
Стоит отметить. Дай мне минуту
Спасибо, Джон. Очень понятно - помечено как ответ
Вы можете использовать concat в XQuery, чтобы добавить к нему значение ID.
Вам просто нужно разбить первый сгенерированный XML на два уровня
WITH rs(x) AS
(
SELECT *
FROM @tbl
FOR XML PATH('row'), TYPE
)
SELECT
x.query('for $x in /row
for $y in $x/*
return <Answer name = "{concat(local-name($y), "_", ($x/ID/text())[1])}">
<value>{data($y)}</value>
</Answer>') AS Result
FROM rs;
К вашему сведению, Stack Overflow не место, чтобы спрашивать «по частям»; каждый вопрос должен быть завершен в его собственных достоинствах. Вы действительно должны указать здесь лучший заголовок, чем; это читается так, как будто нам нужно иметь возможность обратиться к обоим сообщениям.