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

Откуда: NRW, Deutschland
Сообщений: 6
Добрый день!

Нужна помощь в написании скрипта.

Упрощенная структура исходной задачи:
create table #table (id int, name varchar(50));
create table #pattern (id int, dmin smallint, dmax smallint);
create table #main (patternid int, ttt smallint, www smallint, x smallint, y smallint);
create table #side (ttt smallint, www smallint, price float);

insert #table values(12345, 'test');
insert #pattern values(1, 100, 200);
insert #pattern values(2, 201, 300);
insert #main values(1, 44, 100, 1, 1);
insert #main values(1, 44, 100, 1, 2);
insert #main values(2, 125, 125, 1, 1);
insert #side values(18, 80, 100);
insert #side values(18, 100, 101);
insert #side values(18, 120, 102);

необходимо на выходе получить XML такого вида:
<table id="12345" name="test">
  <pattern id="1" dmin="100" dmax="200">
    <main ttt="44" www="100" x="1" y="1"/>
    <main ttt="44" www="100" x="1" y="2"/>
  </pattern>
  <pattern id="2" dmin="201" dmax="300">
    <main ttt="125" www="125" x="1" y="1"/>
  </pattern>
  <side>
    <row ttt="18" www="80" price="100"/>
    <row ttt="18" www="100" price="101"/>
    <row ttt="18" www="120" price="102"/>
  </side>
</table>

всё в принципе получилось, кроме таблицы "side":
select 
   [table].*, 
   [pattern].*,
   [main].ttt, [main].www, [main].x, [main].y
  from #table as [table] 
    full join #pattern as [pattern] on 1=1 
    full join  #main as [main] on [pattern].id = [main].patternid 
  for xml auto

ума не приложу (бъюсь третий день уже - истина где-то рядом), как её можно запихнуть в запрос. в результате "side" всё время пытается залезть как дочерняя таблица в "main", причём n-раз (в моём случае - 3 раза). Пробовал реализовать через for xml path - результат тот же, через explicit - тоже самое, но гораздо труднее из-за Tag/Parent: их надо предварительно подготавливать и связывать pattern/main.
Идеально, если бы решение можно было реализовать через for xml path.

select @@version
:
Microsoft SQL Server 2005 - 9.00.3077.00 (Intel X86) Dec 17 2008 15:19:45 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

заранее спасибо.
5 июл 09, 02:48    [7377424]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по "for xml" много таблиц  [new]
Dikiy Pelemen`
Member

Откуда:
Сообщений: 7
oash20041234,

http://www.rsdn.ru/article/db/xmlsql.xml
читаем пример про формирование иерархических документов
5 июл 09, 12:16    [7377645]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по "for xml" много таблиц  [new]
oash20041234
Member

Откуда: NRW, Deutschland
Сообщений: 6
Dikiy Pelemen`
oash20041234,

http://www.rsdn.ru/article/db/xmlsql.xml
читаем пример про формирование иерархических документов


прочитал. там через EXPLICIT реализовано. Вот что у меня вышло:

select
  1 as Tag,
  null as Parent,
  id as [table!1!id],
  name as [table!1!name],
  null as [pattern!2!id],
  null as [pattern!2!dmin],
  null as [pattern!2!dmax],
  null as [main!3!ttt],
  null as [main!3!www],
  null as [main!3!x],
  null as [main!3!y],
  null as [side!4!id],
  null as [row!5!ttt],
  null as [row!5!www],
  null as [row!5!price]
from #table
  union all
select 
  2, -- Tag
  1, -- Parent
  null, -- [table!1!id]
  null, -- [table!1!name]
  id, -- [pattern!2!id]
  dmin, -- [pattern!2!dmin]
  dmax, -- [pattern!2!dmax]
  null, -- [main!3!ttt]
  null, -- [main!3!www]
  null, -- [main!3!x]
  null, -- [main!3!y]
  null, -- [side!4!id]
  null, -- [row!5!ttt]
  null, -- [row!5!www]
  null -- [row!5!price]
from #pattern
  union all
select
  3, -- Tag
  2, -- Parent
  null, -- [table!1!id]
  null, -- [table!1!name]
  p.id, -- [pattern!2!id]
  p.dmin, -- [pattern!2!dmin]
  p.dmax, -- [pattern!2!dmax]
  ttt, -- [main!3!ttt]
  www, -- [main!3!www]
  x, -- [main!3!x]
  y, -- [main!3!y]
  null, -- [side!4!id]
  null, -- [row!5!ttt]
  null, -- [row!5!www]
  null -- [row!5!price]
from #main as m join #pattern as p on m.patternid = p.id
  union all
select
  4, -- Tag
  1, -- Parent
  null, -- [table!1!id]
  null, -- [table!1!name]
  null, -- [pattern!2!id]
  null, -- [pattern!2!dmin]
  null, -- [pattern!2!dmax]
  null, -- [main!3!ttt]
  null, -- [main!3!www]
  null, -- [main!3!x]
  null, -- [main!3!y]
  null, -- [side!4!id]
  null, -- [row!5!ttt]
  null, -- [row!5!www]
  null -- [row!5!price]
union all
select
  5, -- Tag
  4, -- Parent
  null, -- [table!1!id]
  null, -- [table!1!name]
  null, -- [pattern!2!id]
  null, -- [pattern!2!dmin]
  null, -- [pattern!2!dmax]
  null, -- [main!3!ttt]
  null, -- [main!3!www]
  null, -- [main!3!x]
  null, -- [main!3!y]
  null, -- [side!4!id]
  ttt, -- [row!5!ttt]
  www, -- [row!5!www]
  price -- [row!5!price]
from #side
for xml explicit

результат:
<table id="12345" name="test">
  <pattern id="1" dmin="100" dmax="200" />
  <pattern id="2" dmin="201" dmax="300">
    <main ttt="44" www="100" x="1" y="1" />  <!-- тут всё кучой свалилось -->
    <main ttt="44" www="100" x="1" y="2" />  <! -- тут всё кучой свалилось -->
    <main ttt="125" www="125" x="1" y="1" /> <!-- тут всё кучой свалилось -->
  </pattern>
  <side>
    <row ttt="18" www="80" price="1.000000000000000e+002" />
    <row ttt="18" www="100" price="1.010000000000000e+002" />
    <row ttt="18" www="120" price="1.020000000000000e+002" />
  </side>
</table>

т.е. неверная группировка/связка таблиц pattern-main :( где чего прошляпил? Должен ли быть идеальный Tag для каждой pattern-строки? А уже потом его как Parent использовать у main-выборки?

вот такую таблицу возвращает SQL-Server без "for xml explicit":

Tag         Parent      table!1!id  table!1!name                                       pattern!2!id pattern!2!dmin pattern!2!dmax main!3!ttt main!3!www main!3!x main!3!y side!4!id   row!5!ttt row!5!www row!5!price
----------- ----------- ----------- -------------------------------------------------- ------------ -------------- -------------- ---------- ---------- -------- -------- ----------- --------- --------- ----------------------
1 NULL 12345 test NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 1 NULL NULL 1 100 200 NULL NULL NULL NULL NULL NULL NULL NULL
2 1 NULL NULL 2 201 300 NULL NULL NULL NULL NULL NULL NULL NULL
3 2 NULL NULL 1 100 200 44 100 1 1 NULL NULL NULL NULL
3 2 NULL NULL 1 100 200 44 100 1 2 NULL NULL NULL NULL
3 2 NULL NULL 2 201 300 125 125 1 1 NULL NULL NULL NULL
4 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
5 4 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 18 80 100
5 4 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 18 100 101
5 4 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 18 120 102

(10 row(s) affected)

Спасибо!

Неужели нельзя как то проще всё это реализовать через FOR XML PATH? Исходная задача сильно упрощена - количество полей будет много больше...
5 июл 09, 16:05    [7377944]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по "for xml" много таблиц  [new]
Glory
Member

Откуда:
Сообщений: 104760
Странно ожидать при отсутствии в запросе ORDER BY, что результат будет иной, чем "тут всё кучой свалилось "
5 июл 09, 18:47    [7378134]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по "for xml" много таблиц  [new]
oash20041234
Member

Откуда: NRW, Deutschland
Сообщений: 6
Glory
Странно ожидать при отсутствии в запросе ORDER BY, что результат будет иной, чем "тут всё кучой свалилось "


Ура, получилось!

Добавил "order by [pattern!2!id], Tag" в конце запроса.


Еще раз благодарю!
5 июл 09, 19:12    [7378165]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по "for xml" много таблиц  [new]
Glory
Member

Откуда:
Сообщений: 104760
Правильный ORDER BY будет такой
Tag, Parent, <а тут уже перечень полей>
5 июл 09, 19:17    [7378172]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по "for xml" много таблиц  [new]
oash20041234
Member

Откуда: NRW, Deutschland
Сообщений: 6
Glory,

хммм, если сделать так "order by Tag, Parent, далее список полей" (в моем случае - [pattern!2!id]), то:

Tag         Parent      table!1!id  table!1!name                                       pattern!2!id pattern!2!dmin pattern!2!dmax main!3!ttt main!3!www main!3!x main!3!y side!4!id   row!5!ttt row!5!www row!5!price
----------- ----------- ----------- -------------------------------------------------- ------------ -------------- -------------- ---------- ---------- -------- -------- ----------- --------- --------- ----------------------
1 NULL 12345 test NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 1 NULL NULL 1 100 200 NULL NULL NULL NULL NULL NULL NULL NULL
2 1 NULL NULL 2 201 300 NULL NULL NULL NULL NULL NULL NULL NULL
3 2 NULL NULL 1 100 200 44 100 1 1 NULL NULL NULL NULL
3 2 NULL NULL 1 100 200 44 100 1 2 NULL NULL NULL NULL
3 2 NULL NULL 2 201 300 125 125 1 1 NULL NULL NULL NULL
4 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
5 4 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 18 80 100
5 4 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 18 100 101
5 4 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 18 120 102

(10 row(s) affected)

и тоже выходит фигня, т.к. сначала выводятся все строки таблицы pattern, а потом уже main. А их надо "прорядить" друг с другом.

А нельзя ли этот результат получить проще (не через Explicit)?
5 июл 09, 19:52    [7378213]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по "for xml" много таблиц  [new]
Glory
Member

Откуда:
Сообщений: 104760
Значит вы неправильно Tag-и и Parent-ы назначили своим наборам
5 июл 09, 19:57    [7378221]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по "for xml" много таблиц  [new]
oash20041234
Member

Откуда: NRW, Deutschland
Сообщений: 6
Glory
Значит вы неправильно Tag-и и Parent-ы назначили своим наборам


очень может быть, но у меня количество main для определенного pattern произвольно, и заранее неизвестно, какой Tag должен быть для следующей pattern-строки, ввиду того, что main идёт следующим select-ом после union...

Есть способы сделать это?
5 июл 09, 20:21    [7378266]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по "for xml" много таблиц  [new]
oash20041234
Member

Откуда: NRW, Deutschland
Сообщений: 6
Glory,

такой еще вопрос: а жизненно ли необходимо, чтоб Tag-и были уникальны? Даже если этот Tag не будет использоваться как Parent для дочерних таблиц? (в моем случае - main, их просто нет)
6 июл 09, 11:01    [7379412]     Ответить | Цитировать Сообщить модератору
 Re: вопрос по "for xml" много таблиц  [new]
Glory
Member

Откуда:
Сообщений: 104760
oash20041234
Glory,

такой еще вопрос: а жизненно ли необходимо, чтоб Tag-и были уникальны? Даже если этот Tag не будет использоваться как Parent для дочерних таблиц? (в моем случае - main, их просто нет)

Не обязательно. Просто странно, что по примеру у вас нормальное дерево, где для каждого Tag-а есть Parent, а не словах выходит наборот
6 июл 09, 13:23    [7380461]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить