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 ответа
Насколько я могу получить это:
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 в коде:).
Я предпочитаю использовать для 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')
дает вам то, что вы просите у меня, но элегантно и аккуратно это не так.
Сценарий ниже производит желаемый формат
|
Вече
<Значение>56
|
Softcover
<Значение>34
|
Синглы
45
|
Multis
<Значение>78
взывать
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