Таблица MS SQL 2005 в XML
У меня есть простая таблица в SQL Server 2005, я хочу преобразовать ее в XML (используя предложение «FOR XML»). У меня проблемы с тем, чтобы мой XML выглядел как требуемый результат.
Я пробовал просматривать различные руководства в Интернете, но у меня возникают трудности. Может кто поможет?
Таблица у меня выглядит так
TYPE,GROUP,VALUE
Books,Hardback,56
Books,Softcover,34
CDs,Singles,45
CDS,Multis,78
Мне нужен стиль вывода:
<data>
<variable name = "TYPE">
<row>
<column>GROUP</column>
<column>VALUE</column>
</row>
<row>
<column>GROUP</column>
<column>VALUE</column>
</row>
</variable>
<variable name = "TYPE">
<row>
<column>GROUP</column>
<column>VALUE</column>
</row>
<row>
<column>GROUP</column>
<column>VALUE</column>
</row>
</variable>
</data>
Редактировать: Насколько я могу судить, мне нужно несколько значений. Я генерирую XML для использования с Xcelsius (Связывание XML и Xcelsius), поэтому не могу контролировать форматирование XML. Я могу сгенерировать XML с помощью ASP согласно связанному руководству, но я надеялся получить его прямо из SQL Server.
Изменить 2: Я надеялся на что-то элегантное и аккуратное ... но пример Годеке оказался ближе всего. Немного возился с SQL, и я придумал:
select
"type" as '@name',
"group" as 'row/column',
null as 'row/tmp',
"value" as 'row/column'
from tableName
for xml path('variable'), root('data')
Выводит почти так, как я хотел. Строка null / tmp даже не выводится; это просто предотвращает конкатенацию. Тем не менее тег <variable name = "TYPE"> повторяется для каждой строки, чего у меня нет.
Ответы 3
Я предпочитаю использовать для XML PATH, он обеспечивает более удобный способ управления вашими элементами и т. д.
Но это довольно сложно
/*
create table #tablename
(
[type] varchar(20),
[group] varchar(20),
[value] varchar(20)
)
insert into #tablename select 'type1','group11','value111'
insert into #tablename select 'type1','group11','value112'
insert into #tablename select 'type1','group12','value121'
insert into #tablename select 'type1','group12','value122'
insert into #tablename select 'type2','group21','value211'
insert into #tablename select 'type2','group21','value212'
insert into #tablename select 'type2','group22','value221'
insert into #tablename select 'type2','group22','value222'
alter table #tablename add id uniqueidentifier
update #tablename set id = newid()
*/
select [type] as '@name',
(select
(select [column] from
(
select [group] as 'column', tbn1.type, tbn2.[group]
from #tablename tbn3 WHERE tbn3.type = tbn1.type and tbn2.[group] = tbn3.[group]
union
select [value], tbn1.type, tbn2.[group]
from #tablename tbn3 WHERE tbn3.type = tbn1.type and tbn2.[group] = tbn3.[group]
) as s
for xml path(''),type
)
from #tablename tbn2
where tbn2.type = tbn1.type
for xml path('row3'), type
)
from #tableName tbn1
GROUP BY [type]
for xml path('variable'), root('data')
дает вам то, о чем вы просите I, но элегантно и аккуратно это не так.
Насколько я могу понять, это:
select "type" as '@name', "group" as 'row/column1', "value" as 'row/column2'
from tableName
for xml path('variable'), root('data')
Я не знаю, как назвать два одинаковых элемента («столбец» и «столбец») за один проход, но, с другой стороны, это странный выбор схемы XML; обычно элементы имеют уникальные имена, если они содержат разные данные. Очевидный выбор (назовите их оба «строка / столбец») просто объединяет их в выводе в одно значение.
Также обратите внимание, что каждая возвращенная строка будет «переменным» элементом, отличным от других. Чтобы получить вложение без лишних записей, потребуется подзапрос:
select distinct "type" as '@name'
from Agent
for xml path('variable'), root('data')
была моя первая мысль, но внятность мешает вложиться.
Все это заставляет меня думать, что для получения нужного вам вывода точный вам, возможно, придется использовать режим EXPLICIT. Возможно, моя проблема в том, что я использую DOMDocument в коде для чего-то вроде этого :).
Приведенный ниже сценарий создает желаемый формат.
<DATA>
<VARIABLE TYPE = "Books">
<row TYPE = "Books">
<GROUP>Hardback</GROUP>
<VALUE>56</VALUE>
</row>
<row TYPE = "Books">
<GROUP>Softcover</GROUP>
<VALUE>34</VALUE>
</row>
</VARIABLE>
<VARIABLE TYPE = "CDs">
<row TYPE = "CDs">
<GROUP>Singles</GROUP>
<VALUE>45</VALUE>
</row>
<row TYPE = "CDS">
<GROUP>Multis</GROUP>
<VALUE>78</VALUE>
</row>
</VARIABLE>
</DATA>
Вызвать
DECLARE @tblItems table (
[TYPE] varchar(50)
,[GROUP] varchar(50)
,[VALUE] int
)
DECLARE @tblShredded table (
[TYPE] varchar(50)
,[XmlItem] xml
)
DECLARE @xmlGroupValueTuples xml
insert into @tblItems([TYPE],[GROUP],[VALUE]) values( 'Books','Hardback',56)
insert into @tblItems([TYPE],[GROUP],[VALUE]) values( 'Books','Softcover',34)
insert into @tblItems([TYPE],[GROUP],[VALUE]) values( 'CDs','Singles',45)
insert into @tblItems([TYPE],[GROUP],[VALUE]) values( 'CDS','Multis',78)
SET @xmlGroupValueTuples =
(
SELECT
"@TYPE" = [TYPE]
,[GROUP]
,[VALUE]
FROM @tblItems
FOR XML PATH('row'), root('Root')
)
INSERT @tblShredded([TYPE], XmlItem)
SELECT
[TYPE] = XmlItem.value('./row[1]/@TYPE', 'varchar(50)')
,XmlItem
FROM dbo.tvfShredGetOneColumnedTableOfXmlItems(@xmlGroupValueTuples)
SELECT
(
SELECT
VARIABLE =
(
SELECT
"@TYPE" = t.[TYPE]
,(
SELECT
tInner.XmlItem.query('./child::*')
FROM @tblShredded tInner
WHERE tInner.[TYPE] = t.[TYPE]
FOR XML PATH(''), ELEMENTS, type
)
FOR XML PATH('VARIABLE'),type
)
)
FROM @tblShredded t
GROUP BY
t.[TYPE]
FOR XML PATH(''), ROOT('DATA')
где
-- Example Inputs
/*
DECLARE @xmlListFormat xml
SET @xmlListFormat =
'
<XmlListRoot>
<Item>004421UB7</Item>
<Item>59020UH24</Item>
<Item>542514NA8</Item>
</XmlListRoot>
'
*/
-- =============================================
-- Author: 6eorge Jetson
-- Create date: 01/22/3003
-- Description: Shreds an input XML list conforming to the expected list schema
-- =============================================
CREATE FUNCTION [dbo].[tvfShredGetOneColumnedTableOfXmlItems] (@xmlListFormat xml)
RETURNS
@tblResults TABLE (XmlItem xml)
AS
BEGIN
INSERT @tblResults
SELECT
tblShredded.colXmlItem.query('.') as XmlItem
FROM
@xmlListFormat.nodes('/child::*/child::*') as tblShredded(colXmlItem)
RETURN
END