Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Создание XML сложной структуры из данных таблицы / 4 сообщений из 4, страница 1 из 1
30.04.2019, 12:35
    #39808260
Akel
Akel
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Создание XML сложной структуры из данных таблицы
Здравствуйте!
Есть задача из данных в одной таблице составить относительно сложный XML.

Хочу отметить, что со своей стороны опыта составления XML в PLSQL не имею и при попытке решить поставленную задачу столкнулся с проблемой, что никак не выходит у меня «Каменный цветок» в нужном формате. Либо куча мелких xml файлов, либо один, и все не в нужном формате. Никак не могу разобраться с правильной группировкой.

Ниже пример данных которые мы имеем и текст XML который нужно получить:

 
Данные:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
with lst as(
select 1 as DealNumber, 'Blank' as TypeDoc, 'RUR' as Currency, 'Our' as Owner, 10 as Quantity, 1000 as Nominal, 'Sell' as TypeDeal, to_date('30.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 23 as DealNumber, 'Bill' as TypeDoc, 'RUR' as Currency, 'Not ours ' as Owner, 20 as Quantity, 100 as Nominal, 'Buy' as TypeDeal, to_date('29.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 72 as DealNumber, 'Coin' as TypeDoc, 'USD' as Currency, 'Not ours' as Owner, 5 as Quantity, 1 as Nominal, 'Sell' as TypeDeal, to_date('30.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 21 as DealNumber, 'Blank' as TypeDoc, 'USD' as Currency, 'Our' as Owner, 10 as Quantity, 10 as Nominal, 'Buy' as TypeDeal, to_date('30.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 65 as DealNumber, 'Blank' as TypeDoc, 'RUR' as Currency, 'Not ours' as Owner, 100 as Quantity, 1000 as Nominal, 'Buy' as TypeDeal, to_date('30.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 17 as DealNumber, 'Bill' as TypeDoc, 'RUR' as Currency, 'Our' as Owner, 100 as Quantity, 1000 as Nominal, 'Sell' as TypeDeal, to_date('30.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 37 as DealNumber, 'Coin' as TypeDoc, 'RUR' as Currency, 'Our' as Owner, 100 as Quantity, 25 as Nominal, 'Sell' as TypeDeal, to_date('29.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 25 as DealNumber, 'Coin' as TypeDoc, 'RUR' as Currency, 'Our' as Owner, 100 as Quantity, 25 as Nominal, 'Sell' as TypeDeal, to_date('30.04.2019','dd.mm.yyyy') as DateDeal from dual
)
Select * from lst;



 
XML:
Код: xml
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
<TESTXML>
            <CURRENCY Currency="RUR">
                        <OWNER Owner="Our">
                                   <TYPEDOC TypeDoc="Bill">
                                               <RECORDS DealNumber="17" Quantity="100" Nominal="1000" TypeDeal="Sell" DateDeal="30.04.2019"/>
                                   </TYPEDOC>
                                   <TYPEDOC TypeDoc="Blank">
                                               <RECORDS DealNumber="23" Quantity="10" Nominal="1000" TypeDeal="Sell" DateDeal="30.04.2019"/>
                                   </TYPEDOC>                    
                                   <TYPEDOC TypeDoc="Coin">
                                               <RECORDS DealNumber="37" Quantity="100" Nominal="25" TypeDeal="Sell" DateDeal="29.04.2019"/>
                                               <RECORDS DealNumber="65" Quantity="100" Nominal="25" TypeDeal="Sell" DateDeal="30.04.2019"/>                                                    
                                   </TYPEDOC>           
                        </OWNER>
                        <OWNER Owner="Not ours">
                                   <TYPEDOC TypeDoc="Bill">
                                               <RECORDS DealNumber="1" Quantity="20" Nominal="100" TypeDeal="Buy" DateDeal="29.04.2019"/>
                                   </TYPEDOC>
                                   <TYPEDOC TypeDoc="Blank">
                                               <RECORDS DealNumber="21" Quantity="100" Nominal="1000" TypeDeal="Buy" DateDeal="30.04.2019"/>
                                   </TYPEDOC>                      
                        </OWNER>                             
            </CURRENCY>                               
            <CURRENCY Currency="USD">
                        <OWNER Owner="Our">
                                   <TYPEDOC TypeDoc="Blank">
                                               <RECORDS DealNumber="25" Quantity="10" Nominal="10" TypeDeal="Buy" DateDeal="30.04.2019"/>
                                   </TYPEDOC>                      
                        </OWNER>
                        <OWNER Owner="Not ours">
                                   <TYPEDOC TypeDoc="Coin">
                                               <RECORDS DealNumber="72" Quantity="5" Nominal="1" TypeDeal="Buy" DateDeal="30.04.2019"/>
                                   </TYPEDOC>           
                        </OWNER>                                         
            </CURRENCY>                   
</TESTXML>




 
Что пытался сделать:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
with lst as(
select 1 as DealNumber, 'Blank' as TypeDoc, 'RUR' as Currency, 'Our' as Owner, 10 as Quantity, 1000 as Nominal, 'Sell' as TypeDeal, to_date('30.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 23 as DealNumber, 'Bill' as TypeDoc, 'RUR' as Currency, 'Not ours ' as Owner, 20 as Quantity, 100 as Nominal, 'Buy' as TypeDeal, to_date('29.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 72 as DealNumber, 'Coin' as TypeDoc, 'USD' as Currency, 'Not ours' as Owner, 5 as Quantity, 1 as Nominal, 'Sell' as TypeDeal, to_date('30.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 21 as DealNumber, 'Blank' as TypeDoc, 'USD' as Currency, 'Our' as Owner, 10 as Quantity, 10 as Nominal, 'Buy' as TypeDeal, to_date('30.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 65 as DealNumber, 'Blank' as TypeDoc, 'RUR' as Currency, 'Not ours' as Owner, 100 as Quantity, 1000 as Nominal, 'Buy' as TypeDeal, to_date('30.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 17 as DealNumber, 'Bill' as TypeDoc, 'RUR' as Currency, 'Our' as Owner, 100 as Quantity, 1000 as Nominal, 'Sell' as TypeDeal, to_date('30.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 37 as DealNumber, 'Coin' as TypeDoc, 'RUR' as Currency, 'Our' as Owner, 100 as Quantity, 25 as Nominal, 'Sell' as TypeDeal, to_date('29.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 25 as DealNumber, 'Coin' as TypeDoc, 'RUR' as Currency, 'Our' as Owner, 100 as Quantity, 25 as Nominal, 'Sell' as TypeDeal, to_date('30.04.2019','dd.mm.yyyy') as DateDeal from dual
)
select
  xmlelement("TESTXML",
      xmlagg(xmlelement("CURRENCY",
      xmlattributes(lst.Currency as Currency),
        xmlagg(xmlelement("OWNER",
        xmlattributes(lst.owner as Owner),
          xmlelement("TYPEDOC",
          xmlattributes(lst.typedoc as TypeDoc),
            xmlelement("RECORDS",
            xmlattributes(lst.dealnumber as DealNumber,
                          lst.quantity as Quantity,
                          lst.nominal as Nominal,
                          lst.typedeal as TypeDeal,
                          to_char(lst.datedeal,'dd.mm.yyyy') as DateDeal)
            )))))))
from lst
group by lst.dealnumber, lst.typedoc, lst.owner, lst.quantity, lst.nominal, lst.typedeal, lst.datedeal, lst.Currency;



В итоге получаю группировку по всем полям и файл не правильной структуры
 
XML
Код: xml
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
<TESTXML>
            <CURRENCY CURRENCY="RUR">
                        <OWNER OWNER="Our">
                                   <TYPEDOC TYPEDOC="Blank">
                                               <RECORDS DEALNUMBER="1" QUANTITY="10" NOMINAL="1000" TYPEDEAL="Sell" DATEDEAL="30.04.2019"/>
                                   </TYPEDOC>
                        </OWNER>
            </CURRENCY>
            <CURRENCY CURRENCY="RUR">
                        <OWNER OWNER="Our">
                                   <TYPEDOC TYPEDOC="Bill">
                                               <RECORDS DEALNUMBER="17" QUANTITY="100" NOMINAL="1000" TYPEDEAL="Sell" DATEDEAL="30.04.2019"/>
                                   </TYPEDOC>
                        </OWNER>
            </CURRENCY>
            <CURRENCY CURRENCY="USD">
                        <OWNER OWNER="Our">
                                   <TYPEDOC TYPEDOC="Blank">
                                               <RECORDS DEALNUMBER="21" QUANTITY="10" NOMINAL="10" TYPEDEAL="Buy" DATEDEAL="30.04.2019"/>
                                   </TYPEDOC>
                        </OWNER>
            </CURRENCY>
            <CURRENCY CURRENCY="RUR">
                        <OWNER OWNER="Not ours ">
                                   <TYPEDOC TYPEDOC="Bill">
                                               <RECORDS DEALNUMBER="23" QUANTITY="20" NOMINAL="100" TYPEDEAL="Buy" DATEDEAL="29.04.2019"/>
                                   </TYPEDOC>
                        </OWNER>
            </CURRENCY>
            <CURRENCY CURRENCY="RUR">
                        <OWNER OWNER="Our">
                                   <TYPEDOC TYPEDOC="Coin">
                                               <RECORDS DEALNUMBER="25" QUANTITY="100" NOMINAL="25" TYPEDEAL="Sell" DATEDEAL="30.04.2019"/>
                                   </TYPEDOC>
                        </OWNER>
            </CURRENCY>
            <CURRENCY CURRENCY="RUR">
                        <OWNER OWNER="Our">
                                   <TYPEDOC TYPEDOC="Coin">
                                               <RECORDS DEALNUMBER="37" QUANTITY="100" NOMINAL="25" TYPEDEAL="Sell" DATEDEAL="29.04.2019"/>
                                   </TYPEDOC>
                        </OWNER>
            </CURRENCY>
            <CURRENCY CURRENCY="RUR">
                        <OWNER OWNER="Not ours">
                                   <TYPEDOC TYPEDOC="Blank">
                                               <RECORDS DEALNUMBER="65" QUANTITY="100" NOMINAL="1000" TYPEDEAL="Buy" DATEDEAL="30.04.2019"/>
                                   </TYPEDOC>
                        </OWNER>
            </CURRENCY>
            <CURRENCY CURRENCY="USD">
                        <OWNER OWNER="Not ours">
                                   <TYPEDOC TYPEDOC="Coin">
                                               <RECORDS DEALNUMBER="72" QUANTITY="5" NOMINAL="1" TYPEDEAL="Sell" DATEDEAL="30.04.2019"/>
                                   </TYPEDOC>
                        </OWNER>
            </CURRENCY>
</TESTXML>



Если группировать только lst.Currency и убрать первый xmlagg на CURRENCY , то получаю два файла XML (по разным валютам) и снова не правильной структуры.
 
Запрос с группировкой только по Currency
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
with lst as(
select 1 as DealNumber, 'Blank' as TypeDoc, 'RUR' as Currency, 'Our' as Owner, 10 as Quantity, 1000 as Nominal, 'Sell' as TypeDeal, to_date('30.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 23 as DealNumber, 'Bill' as TypeDoc, 'RUR' as Currency, 'Not ours ' as Owner, 20 as Quantity, 100 as Nominal, 'Buy' as TypeDeal, to_date('29.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 72 as DealNumber, 'Coin' as TypeDoc, 'USD' as Currency, 'Not ours' as Owner, 5 as Quantity, 1 as Nominal, 'Sell' as TypeDeal, to_date('30.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 21 as DealNumber, 'Blank' as TypeDoc, 'USD' as Currency, 'Our' as Owner, 10 as Quantity, 10 as Nominal, 'Buy' as TypeDeal, to_date('30.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 65 as DealNumber, 'Blank' as TypeDoc, 'RUR' as Currency, 'Not ours' as Owner, 100 as Quantity, 1000 as Nominal, 'Buy' as TypeDeal, to_date('30.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 17 as DealNumber, 'Bill' as TypeDoc, 'RUR' as Currency, 'Our' as Owner, 100 as Quantity, 1000 as Nominal, 'Sell' as TypeDeal, to_date('30.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 37 as DealNumber, 'Coin' as TypeDoc, 'RUR' as Currency, 'Our' as Owner, 100 as Quantity, 25 as Nominal, 'Sell' as TypeDeal, to_date('29.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 25 as DealNumber, 'Coin' as TypeDoc, 'RUR' as Currency, 'Our' as Owner, 100 as Quantity, 25 as Nominal, 'Sell' as TypeDeal, to_date('30.04.2019','dd.mm.yyyy') as DateDeal from dual
)
select
  xmlelement("TESTXML",
      xmlelement("CURRENCY",
      xmlattributes(lst.Currency as Currency),
        xmlagg(xmlelement("OWNER",
        xmlattributes(lst.owner as Owner),
          xmlelement("TYPEDOC",
          xmlattributes(lst.typedoc as TypeDoc),
            xmlelement("RECORDS",
            xmlattributes(lst.dealnumber as DealNumber,
                          lst.quantity as Quantity,
                          lst.nominal as Nominal,
                          lst.typedeal as TypeDeal,
                          to_char(lst.datedeal,'dd.mm.yyyy') as DateDeal)
            ))))))
from lst
group by lst.Currency




 
XML1 не верная структура
<TESTXML>
<CURRENCY CURRENCY="RUR">
<OWNER OWNER="Our">
<TYPEDOC TYPEDOC="Blank">
<RECORDS DEALNUMBER="1" QUANTITY="10" NOMINAL="1000" TYPEDEAL="Sell" DATEDEAL="30.04.2019"/>
</TYPEDOC>
</OWNER>
<OWNER OWNER="Not ours">
<TYPEDOC TYPEDOC="Blank">
<RECORDS DEALNUMBER="65" QUANTITY="100" NOMINAL="1000" TYPEDEAL="Buy" DATEDEAL="30.04.2019"/>
</TYPEDOC>
</OWNER>
<OWNER OWNER="Our">
<TYPEDOC TYPEDOC="Coin">
<RECORDS DEALNUMBER="37" QUANTITY="100" NOMINAL="25" TYPEDEAL="Sell" DATEDEAL="29.04.2019"/>
</TYPEDOC>
</OWNER>
<OWNER OWNER="Our">
<TYPEDOC TYPEDOC="Coin">
<RECORDS DEALNUMBER="25" QUANTITY="100" NOMINAL="25" TYPEDEAL="Sell" DATEDEAL="30.04.2019"/>
</TYPEDOC>
</OWNER>
<OWNER OWNER="Not ours ">
<TYPEDOC TYPEDOC="Bill">
<RECORDS DEALNUMBER="23" QUANTITY="20" NOMINAL="100" TYPEDEAL="Buy" DATEDEAL="29.04.2019"/>
</TYPEDOC>
</OWNER>
<OWNER OWNER="Our">
<TYPEDOC TYPEDOC="Bill">
<RECORDS DEALNUMBER="17" QUANTITY="100" NOMINAL="1000" TYPEDEAL="Sell" DATEDEAL="30.04.2019"/>
</TYPEDOC>
</OWNER>
</CURRENCY>
</TESTXML>

 
XML2 не верная структура
<TESTXML>
<CURRENCY CURRENCY="USD">
<OWNER OWNER="Our">
<TYPEDOC TYPEDOC="Blank">
<RECORDS DEALNUMBER="21" QUANTITY="10" NOMINAL="10" TYPEDEAL="Buy" DATEDEAL="30.04.2019"/>
</TYPEDOC>
</OWNER>
<OWNER OWNER="Not ours">
<TYPEDOC TYPEDOC="Coin">
<RECORDS DEALNUMBER="72" QUANTITY="5" NOMINAL="1" TYPEDEAL="Sell" DATEDEAL="30.04.2019"/>
</TYPEDOC>
</OWNER>
</CURRENCY>
</TESTXML>

Прошу помощи или совета в решение данной задачи.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.04.2019, 13:15
    #39808278
-2-
-2-
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Создание XML сложной структуры из данных таблицы
Akelполучаю группировку по всем полямНу так и делай группировку не по всем полям сразу, а в соответствии с иерархией.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.04.2019, 16:30
    #39808410
Akel
Akel
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Создание XML сложной структуры из данных таблицы
В общем сделав несколько танцев с бубнами мне все таки удалось решить данную задачу, хотя не знаю насколько мое решение корректно и оптимально, но все же оно работает.
 
Рабочий запрос
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
with lst as(
select 1 as DealNumber, 'Blank' as TypeDoc, 'RUR' as Currency, 'Our' as Owner, 10 as Quantity, 1000 as Nominal, 'Sell' as TypeDeal, to_date('30.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 23 as DealNumber, 'Bill' as TypeDoc, 'RUR' as Currency, 'Not ours' as Owner, 20 as Quantity, 100 as Nominal, 'Buy' as TypeDeal, to_date('29.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 72 as DealNumber, 'Coin' as TypeDoc, 'USD' as Currency, 'Not ours' as Owner, 5 as Quantity, 1 as Nominal, 'Sell' as TypeDeal, to_date('30.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 21 as DealNumber, 'Blank' as TypeDoc, 'USD' as Currency, 'Our' as Owner, 10 as Quantity, 10 as Nominal, 'Buy' as TypeDeal, to_date('30.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 65 as DealNumber, 'Blank' as TypeDoc, 'RUR' as Currency, 'Not ours' as Owner, 100 as Quantity, 1000 as Nominal, 'Buy' as TypeDeal, to_date('30.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 17 as DealNumber, 'Bill' as TypeDoc, 'RUR' as Currency, 'Our' as Owner, 100 as Quantity, 1000 as Nominal, 'Sell' as TypeDeal, to_date('30.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 37 as DealNumber, 'Coin' as TypeDoc, 'RUR' as Currency, 'Our' as Owner, 100 as Quantity, 25 as Nominal, 'Sell' as TypeDeal, to_date('29.04.2019','dd.mm.yyyy') as DateDeal from dual
union
select 25 as DealNumber, 'Coin' as TypeDoc, 'RUR' as Currency, 'Our' as Owner, 100 as Quantity, 25 as Nominal, 'Sell' as TypeDeal, to_date('30.04.2019','dd.mm.yyyy') as DateDeal from dual
),
lrec as (
select lst.Currency, lst.owner, lst.typedoc,
      xmlelement("TYPEDOC",
       xmlattributes(lst.typedoc as TypeDoc),
       xmlagg(xmlelement("RECORDS",
            xmlattributes(lst.dealnumber as DealNumber,
                          lst.quantity as Quantity,
                          lst.nominal as Nominal,
                          lst.typedeal as TypeDeal,
                          to_char(lst.datedeal,'dd.mm.yyyy') as DateDeal
        )))) as xmlrec
from lst
group by lst.Currency, lst.owner, lst.typedoc
),
lown as (
select lrec.Currency,
      xmlelement("OWNER",
      xmlattributes(lrec.owner as Owner),
       xmlagg(  
       lrec.xmlrec
       )) xmlown
from lrec
group by lrec.Currency, lrec.owner
),
lcur as (
select
         xmlelement("CURRENCY",
          xmlattributes(lown.Currency as Currency),
           xmlagg(
           lown.xmlown
           )) xmllcur
from lown
group by lown.Currency
)
select
      xmlelement("TESTXML",
      xmlagg(lcur.xmllcur)
      )
from lcur;



 
Полученная XML
Код: xml
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
<TESTXML>
            <CURRENCY CURRENCY="RUR">
                        <OWNER OWNER="Our">
                                   <TYPEDOC TYPEDOC="Bill">
                                               <RECORDS DEALNUMBER="17" QUANTITY="100" NOMINAL="1000" TYPEDEAL="Sell" DATEDEAL="30.04.2019"/>
                                   </TYPEDOC>
                                   <TYPEDOC TYPEDOC="Blank">
                                               <RECORDS DEALNUMBER="1" QUANTITY="10" NOMINAL="1000" TYPEDEAL="Sell" DATEDEAL="30.04.2019"/>
                                   </TYPEDOC>
                                   <TYPEDOC TYPEDOC="Coin">
                                               <RECORDS DEALNUMBER="25" QUANTITY="100" NOMINAL="25" TYPEDEAL="Sell" DATEDEAL="30.04.2019"/>
                                               <RECORDS DEALNUMBER="37" QUANTITY="100" NOMINAL="25" TYPEDEAL="Sell" DATEDEAL="29.04.2019"/>
                                   </TYPEDOC>
                        </OWNER>
                        <OWNER OWNER="Not ours">
                                   <TYPEDOC TYPEDOC="Bill">
                                               <RECORDS DEALNUMBER="23" QUANTITY="20" NOMINAL="100" TYPEDEAL="Buy" DATEDEAL="29.04.2019"/>
                                   </TYPEDOC>
                                   <TYPEDOC TYPEDOC="Blank">
                                               <RECORDS DEALNUMBER="65" QUANTITY="100" NOMINAL="1000" TYPEDEAL="Buy" DATEDEAL="30.04.2019"/>
                                   </TYPEDOC>
                        </OWNER>
            </CURRENCY>
            <CURRENCY CURRENCY="USD">
                        <OWNER OWNER="Our">
                                   <TYPEDOC TYPEDOC="Blank">
                                               <RECORDS DEALNUMBER="21" QUANTITY="10" NOMINAL="10" TYPEDEAL="Buy" DATEDEAL="30.04.2019"/>
                                   </TYPEDOC>
                        </OWNER>
                        <OWNER OWNER="Not ours">
                                   <TYPEDOC TYPEDOC="Coin">
                                               <RECORDS DEALNUMBER="72" QUANTITY="5" NOMINAL="1" TYPEDEAL="Sell" DATEDEAL="30.04.2019"/>
                                   </TYPEDOC>
                        </OWNER>
            </CURRENCY>
</TESTXML>




Если кто-то знает более оптимальный запрос, прошу поделиться.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.04.2019, 16:33
    #39808416
Akel
Akel
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Создание XML сложной структуры из данных таблицы
-2-Akelполучаю группировку по всем полямНу так и делай группировку не по всем полям сразу, а в соответствии с иерархией.
Спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Создание XML сложной структуры из данных таблицы / 4 сообщений из 4, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 416ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1882540]:
 0ms
get settings:
 9ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 42ms
get topic data:
 12ms
get forum data:
 2ms
get page messages:
 40ms
get tp. blocked users:
 1ms
others: 291ms
total:  416ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]