Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Формирование xml из большой таблицы  [new]
.Anatoly.
Member

Откуда: Екатеринбург
Сообщений: 310
Добрый день!
Есть таблица
CREATE TABLE [Data4Xml](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[ParamA] [int] NOT NULL,
	[ParamB] [int] NOT NULL,
	[ParamC] [int] NOT  NULL,
	[innerParam1] [int] NOT NULL,
	[innerParam2] [int] NOT NULL,
	[innerParam3] [int] NOT NULL,
	[field4Filter] [int] NOT NULL,
)

Она содержит более 400 миллионов записей.
Нужно часть данных выгрузить в xml, что бы получить следующий код.
<root>
   <globalParametrs paramA="1" paramB="2" paramC="3">
       <innerParametr id="4" innerParam1="43" innerParam2="65" innerParam3="78" />
       <innerParametr id="5" innerParam1="1" innerParam2="2" innerParam3="3" />
   </globalParametrs>
   <globalParametrs paramA="1" paramB="2" paramC="4">
       <innerParametr id="6" innerParam1="430" innerParam2="650" innerParam3="780" />
       <innerParametr id="7" innerParam1="10" innerParam2="20" innerParam3="30" />
   </globalParametrs>
</root>

То есть группируем данные по полям paramA,paramB и paramC

Для этого я использую запрос типа.
	select
	 [Data4Xml].[paramA]
	,[Data4Xml].[paramB]
	,[Data4Xml].[paramC]
	,(
		select 
			 [InnerData4Xml].[id]
			,[InnerData4Xml].[innerParam1]
			,[InnerData4Xml].[innerParam2]
			,[InnerData4Xml].[innerParam3]
		from [Data4Xml] [InnerData4Xml]
		where [InnerData4Xml].[field4Filter] = @ID
		   and [InnerData4Xml].[paramA] = [Data4Xml].[paramA]
		   and [InnerData4Xml].[paramB] = [Data4Xml].[paramB]
		   and [InnerData4Xml].[paramC] = [Data4Xml].[paramC]
		for xml raw ('innerParametr'), type
	)
from (
	select distinct 
		 [Data4Xml].[paramA]
		,[Data4Xml].[paramB]
		,[Data4Xml].[paramC]
	from [Data4Xml] [Data4Xml]
	where [Data4Xml].[field4Filter] = @ID
) [HotelService]
for xml raw ('globalParametrs'), type , root ('root')


Но в таком случае по этой большой таблице приходится пробегать дважды! Что конечно очень печально.
У кого-нибудь была такая же проблема? Как красиво можно выйти из этой ситуации?
22 май 12, 10:47    [12592562]     Ответить | Цитировать Сообщить модератору
 Re: Формирование xml из большой таблицы  [new]
.Anatoly.
Member

Откуда: Екатеринбург
Сообщений: 310
Небольшое исправление в запросе.
Вот так верно.
	select
	 [Data4Xml].[paramA]
	,[Data4Xml].[paramB]
	,[Data4Xml].[paramC]
	,(
		select 
			 [InnerData4Xml].[id]
			,[InnerData4Xml].[innerParam1]
			,[InnerData4Xml].[innerParam2]
			,[InnerData4Xml].[innerParam3]
		from [Data4Xml] [InnerData4Xml]
		where [InnerData4Xml].[field4Filter] = @ID
		   and [InnerData4Xml].[paramA] = [Data4Xml].[paramA]
		   and [InnerData4Xml].[paramB] = [Data4Xml].[paramB]
		   and [InnerData4Xml].[paramC] = [Data4Xml].[paramC]
		for xml raw ('innerParametr'), type
	)
from (
	select distinct 
		 [Data4Xml].[paramA]
		,[Data4Xml].[paramB]
		,[Data4Xml].[paramC]
	from [Data4Xml] [Data4Xml]
	where [Data4Xml].[field4Filter] = @ID
) [Data4Xml]
for xml raw ('globalParametrs'), type , root ('root')


@ID - это некоторый входной параметр, который определяет какие именно данные нужно выгружать.
22 май 12, 10:56    [12592644]     Ответить | Цитировать Сообщить модератору
 Re: Формирование xml из большой таблицы  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Для этого нет встроеных средств.
Аля Grouping, оконные функции и ORDER BY не действует на XML.
Для этого надо расширять язык (а это ещё непонятно как).
22 май 12, 19:52    [12596987]     Ответить | Цитировать Сообщить модератору
 Re: Формирование xml из большой таблицы  [new]
Zarus Jamer
Member

Откуда:
Сообщений: 4
ТС, будьте любезны предоставить запросы для формирования таблиц с данными, по которым формируется выборка. Возможно, спасти Вас еще удастся.
23 май 12, 08:21    [12598268]     Ответить | Цитировать Сообщить модератору
 Re: Формирование xml из большой таблицы  [new]
.Anatoly.
Member

Откуда: Екатеринбург
Сообщений: 310
Zarus Jamer
CREATE TABLE [Data4Xml](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[ParamA] [int] NOT NULL,
	[ParamB] [int] NOT NULL,
	[ParamC] [int] NOT  NULL,
	[innerParam1] [int] NOT NULL,
	[innerParam2] [int] NOT NULL,
	[innerParam3] [int] NOT NULL,
	[field4Filter] [int] NOT NULL,
)

INSERT INTO [dbo].[Data4Xml](
	    [ParamA],[ParamB],[ParamC]
           ,[innerParam1],[innerParam2],[innerParam3]
           ,[field4Filter]
)
	select 1,2,3,  5,6,7	,10
union	select 1,2,3,  6,6,7	,10
union	select 1,2,3,  6,6,70	,20
union	select 1,2,3,  6,6,7	,20
union	select 2,2,4,  9,8,1	,10
union	select 2,2,4,  9,6,7	,20


Ну и параметр объявим
DECLARE @ID int;SET @ID = 10;


В итоге должны получить
<root>
  <globalParametrs paramA="1" paramB="2" paramC="3">
    <innerParametr id="1" innerParam1="5" innerParam2="6" innerParam3="7" />
    <innerParametr id="2" innerParam1="6" innerParam2="6" innerParam3="7" />
  </globalParametrs>
  <globalParametrs paramA="2" paramB="2" paramC="4">
    <innerParametr id="6" innerParam1="9" innerParam2="8" innerParam3="1" />
  </globalParametrs>
</root>
23 май 12, 09:57    [12598595]     Ответить | Цитировать Сообщить модератору
 Re: Формирование xml из большой таблицы  [new]
.Anatoly.
Member

Откуда: Екатеринбург
Сообщений: 310
Mnior, очень жаль, если это действительно так.
23 май 12, 10:06    [12598653]     Ответить | Цитировать Сообщить модератору
 Re: Формирование xml из большой таблицы  [new]
Zarus Jamer
Member

Откуда:
Сообщений: 4
.Anatoly.,

В случае работы с XML аггрегирование данных недоступно, поэтому придется использовать подзапросы. Ваш запрос подходит.

Однако стоит обратить внимание на индексы в таблице по полям [field4Filter], [ParamA], [ParamB], [ParamC] или же, если количество данных по фильтру небольшое, использовать табличную переменную / CTE.
DECLARE @ID INT = 10	
DECLARE @tmp TABLE (
   [id] [int] NOT NULL PRIMARY KEY
  ,[ParamA] [int] NOT NULL
  ,[ParamB] [int] NOT NULL
  ,[ParamC] [int] NOT  NULL
  ,[innerParam1] [int] NOT NULL
  ,[innerParam2] [int] NOT NULL
  ,[innerParam3] [int] NOT NULL
)
INSERT INTO 
  @tmp
SELECT
   [id]
  ,[paramA]
  ,[paramB]
  ,[paramC]
  ,[innerParam1]
  ,[innerParam2]
  ,[innerParam3]
FROM 
  [Data4Xml]
WHERE 
  [field4Filter] = @ID
  
SELECT
   [T1].[paramA] [@paramA]
  ,[T1].[paramB] [@paramB]
  ,[T1].[paramC] [@paramC]
  ,(
      SELECT 
         [id] [@id]
        ,[innerParam1] [@innerParam1]
        ,[innerParam2] [@innerParam2]
        ,[innerParam3] [@innerParam3]
      FROM 
        @tmp [T2]
      WHERE 
              [T2].[paramA] = [T1].[paramA]
        AND [T2].[paramB] = [T1].[paramB]
        AND [T2].[paramC] = [T1].[paramC]
      FOR XML PATH( 'innerParametr' ), TYPE
   ) [*]
FROM (
  SELECT DISTINCT 
     [paramA]
    ,[paramB]
    ,[paramC]
  FROM 
    @tmp
) [T1]
FOR XML PATH( 'globalParametrs' ), ROOT( 'root' ), TYPE 
23 май 12, 10:25    [12598802]     Ответить | Цитировать Сообщить модератору
 Re: Формирование xml из большой таблицы  [new]
.Anatoly.
Member

Откуда: Екатеринбург
Сообщений: 310
Zarus Jamer,к сожалению много. В самом плохом случае, около 500 000 строк.(Хорошо, что таких случаев не так много)
Для себя нашел способ как улучшить ситуацию, вместо вложенного запроса использую inner join и for xml auto.
Вот что получилось.
select
	   [globalParametrs].[ParamA]
	  ,[globalParametrs].[ParamB]
	  ,[globalParametrs].[ParamC]
	  ,[innerParam1],[innerParam2],[innerParam3],[id]
from (
	select distinct 
	   [ParamA],[ParamB],[ParamC]
	from [Data4Xml]
	where [field4Filter] = @ID
) [globalParametrs]
inner join (
	select 
	   [id]
	  ,[ParamA],[ParamB],[ParamC]
	  ,[innerParam1],[innerParam2],[innerParam3]
	from [Data4Xml]
	where [field4Filter] = @ID
)[innerParametr]
 on [innerParametr].[ParamA] = [globalParametrs].[ParamA]
and [innerParametr].[ParamB] = [globalParametrs].[ParamB]
and [innerParametr].[ParamC] = [globalParametrs].[ParamC]
for xml auto, type, root ('root')

Может есть практики, как еще улучшить запрос?
23 май 12, 11:30    [12599391]     Ответить | Цитировать Сообщить модератору
 Re: Формирование xml из большой таблицы  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
.Anatoly., есть одна заковырка, но она тут не подходит. Разве что в качестве костыля:
Если количество innerParametr ограничего по количеству для каждой группы, то можно PIVOT + мега-пупер конструкция.

А ещё, вы можете настроить индексированное представление для группы для скорости при JOIN.
23 май 12, 12:38    [12600198]     Ответить | Цитировать Сообщить модератору
 Re: Формирование xml из большой таблицы  [new]
.Anatoly.
Member

Откуда: Екатеринбург
Сообщений: 310
Mnior, индексированное представление - это хорошая идея! Обязательно нужно сделать!

А вот насчет PIVOT, не очень понял, можете поподробней описать свою идею.
23 май 12, 13:27    [12600707]     Ответить | Цитировать Сообщить модератору
 Re: Формирование xml из большой таблицы  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
.Anatoly.
А вот насчет PIVOT, не очень понял, можете поподробней описать свою идею.
Допустим, что у вас максимум 3 innerParametr в группе. Тогда делам PIVOT (CASE-ами) на эти 3 по каждой колонке и получаем в итоге 15 колонок: 3 + 3 * 4
SELECT	 paramA	AS [@paramA]
	,paramB	AS [@paramB]
	,paramC	AS [@paramC]

	,id_1		AS [innerParametr/@id]
	,innerParam1_1	AS [innerParametr/@innerParam1]
	,innerParam2_1	AS [innerParametr/@innerParam2]
	,innerParam3_1	AS [innerParametr/@innerParam3]
	,NULL		AS [text()]
	,id_2		AS [innerParametr/@id]
	,innerParam1_2	AS [innerParametr/@innerParam1]
	,innerParam2_2	AS [innerParametr/@innerParam2]
	,innerParam3_2	AS [innerParametr/@innerParam3]
	,NULL		AS [text()]
	,id_3		AS [innerParametr/@id]
	,innerParam1_3	AS [innerParametr/@innerParam1]
	,innerParam2_3	AS [innerParametr/@innerParam2]
	,innerParam3_3	AS [innerParametr/@innerParam3]
-- FROM	<Pivoted Table>
FROM	(VALUES (1,2,3
	,4,43,65,78
	,5,1,2,3
	,NULL,NULL,NULL,NULL)	-- Нет данных для 3-го случая
	) X(paramA, paramB, paramC
	,id_1, innerParam1_1, innerParam2_1, innerParam3_1
	,id_2, innerParam1_2, innerParam2_2, innerParam3_2
	,id_3, innerParam1_3, innerParam2_3, innerParam3_3)
FOR XML Path('globalParametrs'),Root('root'),Type
<root>
  <globalParametrs paramA="1" paramB="2" paramC="3">
    <innerParametr id="4" innerParam1="43" innerParam2="65" innerParam3="78" />
    <innerParametr id="5" innerParam1="1" innerParam2="2" innerParam3="3" />
  </globalParametrs>
</root>
Если у вас максимум 10 то надо делать 43 колонки. Но это не дело. Костыль.
23 май 12, 17:15    [12603296]     Ответить | Цитировать Сообщить модератору
 Re: Формирование xml из большой таблицы  [new]
.Anatoly.
Member

Откуда: Екатеринбург
Сообщений: 310
Mnior, да.. в данном случае это не подойдет(
24 май 12, 08:46    [12605451]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить