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

Откуда:
Сообщений: 31
Доброго времени суток.
Есть некоторое количество таблиц связанных один ко многим, данные из которых необходимо получить в виде XML типа:
<Main ID="1" Value="a">
    <Cild ID="10" Value="aa" />
    <Cild ID="20" Value="ab" />
</Main>

Вложенность заранее определена и в данный момент значения не имеет
На каждую таблицу будут накладываться фильтры
Исходные данные:
 
DECLARE @Table1 AS TABLE
    (
     Table1ID INT
   , val VARCHAR(8)
    )
INSERT  INTO @Table1 (Table1ID, val)
VALUES  (1, 'a') 
INSERT  INTO @Table1 (Table1ID, val)
VALUES  (2, 'b') 	
DECLARE @Table2 AS TABLE
    (
     Table2ID INT
   , Table1ID INT
   , val2 VARCHAR(8)
    )
INSERT  INTO @Table2
        (
         Table2ID
       , Table1ID
       , val2
        )
VALUES  (10, 1, 'aa') 
INSERT  INTO @Table2
        (
         Table2ID
       , Table1ID
       , val2
        )
VALUES  (20, 1, 'ab') 	
INSERT  INTO @Table2
        (
         Table2ID
       , Table1ID
       , val2
        )
VALUES  (30, 2, 'ba') 	
Пример:
 
SELECT  t1.Table1ID AS [Main/@ID]
      , t1.val AS [Main/@Value]
      , t2.Table2ID AS [Main/Cild/@ID]
      , t2.val2 AS [Main/Cild/@Value]
FROM    @Table1 AS t1
        INNER JOIN @Table2 AS t2
            ON t1.Table1ID = t2.Table1ID
WHERE   t2.Table2ID < 30
FOR     XML PATH('')

В этом случае создается дважды рутовая нода
 
<Main ID="1" Value="a">
  <Cild ID="10" Value="aa" />
</Main>
<Main ID="1" Value="a">
  <Cild ID="20" Value="ab" />
</Main>

 
SELECT  t1.Table1ID AS [Main/@ID]
      , t1.val AS [Main/@Value]
      , (
         SELECT t2.Table2ID AS [Cild/@ID]
              , t2.val2 AS [Cild/@Value]
         FROM   @Table2 AS t2
         WHERE  t1.Table1ID = t2.Table1ID
                AND t2.Table2ID < 30
        FOR
         XML PATH('')
           , TYPE
        ) AS [Main]
FROM    @Table1 AS t1
FOR     XML PATH('') 

А в этом случае данные из таблицы 1 нельзя отфильтровать не приджойнив таблицу 2
Результат:
<Main ID="1" Value="a">
  <Cild ID="10" Value="aa" />
  <Cild ID="20" Value="ab" />
</Main>
<Main ID="2" Value="b" />

Но есть еще опция AUTO которая выдает самый похожий результат но в реальных условиях с ней очень тяжело работать когда используется большое количество таблиц
25 июн 09, 18:42    [7344881]     Ответить | Цитировать Сообщить модератору
 Re: FOR XML  [new]
tsyoma
Member

Откуда:
Сообщений: 31
SELECT @@VERSION
Microsoft SQL Server 2005 - 9.00.3152.00 (Intel X86) 
	Mar  3 2007 03:17:37 
	Copyright (c) 1988-2005 Microsoft Corporation
	Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
25 июн 09, 18:55    [7344916]     Ответить | Цитировать Сообщить модератору
 Re: FOR XML  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3197
tsyoma
Но есть еще опция AUTO которая выдает самый похожий результат но в реальных условиях с ней очень тяжело работать когда используется большое количество таблиц
Угу, сложно, но обычно оказывается можно - есть пара кунштюков, которыми ее можно угаварыть работать так, как требуется
Заставить PATH не плодить родительские ноды вы не сможете, потому что именно таким он и задумывался - в хелпе однозначно сказано, что это всего лишь расширенная версия режима RAW, а он работает именно так.
Если не сумеете договориться с AUTO, то остается только EXPLICIT, заранее сочувствую.
25 июн 09, 19:00    [7344924]     Ответить | Цитировать Сообщить модератору
 Re: FOR XML  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Я не очень спец по xml, только учусь, но вроде то что надо
SELECT  1 as Tag, null as Parent
      , t1.Table1ID AS [Main!1!ID]
      , t1.val AS [Main!1!Value]
      , null AS [Cild!2!ID]
      , null AS [Cild!2!Value]
FROM    @Table1 AS t1
  where exists (select *
                  from @Table2 AS t2
                 where t2.Table1ID = t1.Table1ID
                   and t2.Table2ID < 30)
union all
SELECT  2 as Tag, 1 as Parent
      , t1.Table1ID AS [Main!1!ID]
      , t1.val AS [Main!1!Value]
      , t2.Table2ID AS [Cild!2!ID]
      , t2.val2 AS [Cild!2!Value]
FROM    @Table1 AS t1
        INNER JOIN @Table2 AS t2
            ON t1.Table1ID = t2.Table1ID
WHERE   t2.Table2ID < 30
FOR     XML EXPLICIT

XML_F52E2B61-18A1-11d1-B105-00805F49916B
-------------------------------------------------------------------------------------
<Main ID="1" Value="a"><Cild ID="10" Value="aa"/><Cild ID="20" Value="ab"/></Main>

(3 row(s) affected)
хотя согласен сложновато
25 июн 09, 19:09    [7344956]     Ответить | Цитировать Сообщить модератору
 Re: FOR XML  [new]
tsyoma
Member

Откуда:
Сообщений: 31
Тогда может подскажите как сделать чтоб при джойне второй и третей таблицы на первую он не вкладывал третью во вторую?
SELECT  t1.Table1ID,
		t1.val,
		t2.Table2ID,
		t2.val2,
		t3.Table3ID,
		t3.val2
FROM    @Table1 t1
        INNER JOIN @Table2 t2
            ON t1.Table1ID = t2.Table1ID
        INNER JOIN @Table3 t3
            ON t1.Table1ID = t3.Table1ID
FOR     XML AUTO 
Результат
<t1 Table1ID="1" val="a">
  <t2 Table2ID="10" val2="aa">
    <t3 Table3ID="103" val2="aaa" />
    <t3 Table3ID="104" val2="aaa" />
  </t2>
  <t2 Table2ID="20" val2="ab">
    <t3 Table3ID="103" val2="aaa" />
    <t3 Table3ID="104" val2="aaa" />
  </t2>
</t1>
А необходимо
<t1 Table1ID="1" val="a">
  <t2 Table2ID="10" val2="aa" />
  <t2 Table2ID="20" val2="ab" />
  <t3 Table3ID="103" val2="aaa" />
  <t3 Table3ID="104" val2="aaa" />
</t1>
25 июн 09, 19:10    [7344957]     Ответить | Цитировать Сообщить модератору
 Re: FOR XML  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3197
AUTO не может объединить таблицы t2 и t3 хотя бы потому, что у столбцов разные имена. Попробуйте извратиться как-нибудь так:
select ...
from t1
  inner join (
    select ... from t2
    union all
    select ... from t3
  ) t23 ...
for xml auto;
Еще вам может быть интересна такая возможность режима PATH, как подстановка результатов коррелированного подзапроса в виде XML-фрагмента в произвольное место основного XML:
select ..., (
  select ... from t2 where Id = t1.Id
  union all
  select ... from t3 where Id = t1.Id
  for xml path
  ) as t2
from t1
for xml path;
Во втором случае за синтаксис не ручаюсь, возможны нюансы, но сама возможность такая есть.
25 июн 09, 22:24    [7345304]     Ответить | Цитировать Сообщить модератору
 Re: FOR XML  [new]
tsyoma
Member

Откуда:
Сообщений: 31
Тогда еще вопрос может кто знает
что будет работать быстрее

Таблицы будут большие более 100 000 записей и конструкция чуток посложнее
Возможно ктото сталкивался и уже знает где могут быть подводные камни?
SELECT  t1.Table1ID AS [Main/@ID]
      , t1.val AS [Main/@Value]
      , (
         SELECT t2.Table2ID AS [Cild/@ID]
              , t2.val2 AS [Cild/@Value]
         FROM   @Table2 AS t2
         WHERE  t1.Table1ID = t2.Table1ID
                AND t2.Table2ID < 30
        FOR
         XML PATH('')
           , TYPE
        ) AS [Main]
FROM    @Table1 AS t1
        INNER JOIN @Table2 t2
            ON t1.Table1ID = t2.Table1ID
                 AND t2.Table2ID < 30
GROUP BY t1.Table1ID 
      , t1.val 
FOR     XML PATH('')
или EXPLICIT
SELECT  1 as Tag
      , null as Parent
      , t1.Table1ID AS [Main!1!ID]
      , t1.val AS [Main!1!Value]
      , null AS [Cild!2!ID]
      , null AS [Cild!2!Value]
FROM    @Table1 AS t1
where   exists ( select *
                 from   @Table2 AS t2
                 where  t2.Table1ID = t1.Table1ID
                        and t2.Table2ID < 30 )
union all
SELECT  2 as Tag
      , 1 as Parent
      , t1.Table1ID AS [Main!1!ID]
      , t1.val AS [Main!1!Value]
      , t2.Table2ID AS [Cild!2!ID]
      , t2.val2 AS [Cild!2!Value]
FROM    @Table1 AS t1
        INNER JOIN @Table2 AS t2
            ON t1.Table1ID = t2.Table1ID
WHERE   t2.Table2ID < 30
FOR     XML EXPLICIT

Судя по планам выполняния вариант с PATH предпочтительнее тк 3 скана таблиц вместо 4х в EXPLICIT
26 июн 09, 11:38    [7346892]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить