#xml #oracle #xmltable
#xml #Oracle #xmltable
Вопрос:
Преобразование XML в строки и столбцы в Oracle:
C12 и c13 имеют несколько значений, поэтому этот запрос не выполняется c12 и c13 имеют несколько значений в m1, m2, m3 и так далее до m42, Я хочу получить все эти значения в строках и столбцах Oracle. Столбцы.
12:53:06 SYS@r17dev11> select xt.* from T24.FBNK_AA_ACCOUNT_DETAILS x ,
13:08:58 2 XMLTABLE('/row' PASSING x.XMLRECORD
13:08:58 3 COLUMNS
13:08:58 4 "CONTRACT.DATE" DATE PATH 'c1',
13:08:58 5 "VALUE.DATE" DATE PATH 'c2',
13:08:58 6 "PAYMENT.START.DATE" DATE PATH 'c5',
13:08:58 7 "MATURITY.DATE" DATE PATH 'c6',
13:08:58 8 "BILL.PAY.DATE"DATE PATH 'c12',
13:08:58 9 "BILL.ID" VARCHAR2(20) PATH 'c13'
13:08:58 10 ) xt where rownum<5;
XMLTABLE('/row' PASSING x.XMLRECORD
*
ERROR at line 2:
ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
Elapsed: 00:00:00.03
ниже приведен XML-код
c12 и c13 содержат несколько значений, и мне нужны все эти значения в таблице
1* select x.XMLRECORD.getClobVal() from T24.FBNK_AA_ACCOUNT_DETAILS x where rownum<2
16:17:31 SYS@r17dev11> /
X.XMLRECORD.GETCLOBVAL()
--------------------------------------------------------------------------------
<row id='AA132364NK43'><c1>20130824</c1><c2>20130824</c2><c3>20130828</c3><c5>20
130927</c5><c6>20380831</c6><c7>CUR</c7><c8>20180827</c8><c11>20130828</c11><c12
>20130927</c12><c12 m='2'>20131027</c12><c12 m='3'>20131127</c12><c12 m='4'>2013
1227</c12><c12 m='5'>20140127</c12><c12 m='6'>20140227</c12><c12 m='7'>20140327<
/c12><c12 m='8'>20140427</c12><c12 m='9'>20140527</c12><c12 m='10'>20140627</c12
><c12 m='11'>20140727</c12><c12 m='12'>20140827</c12><c12 m='13'>20140927</c12><
c12 m='14'>20141027</c12><c12 m='15'>20141127</c12><c12 m='16'>20141227</c12><c1
2 m='17'>20150127</c12><c12 m='18'>20150227</c12><c12 m='19'>20150327</c12><c12
m='20'>20150427</c12><c12 m='21'>20150527</c12><c12 m='22'>20150627</c12><c12 m=
'23'>20150727</c12><c12 m='24'>20150827</c12><c12 m='25'>20150927</c12><c12 m='2
6'>20151027</c12><c12 m='27'>20151127</c12><c12 m='28'>20151227</c12><c12 m='29'
>20160127</c12><c12 m='30'>20160227</c12><c12 m='31'>20160327</c12><c12 m='32'>2
0160427</c12><c12 m='33'>20160527</c12><c12 m='34'>20160627</c12><c12 m='35'>201
60727</c12><c12 m='36'>20160827</c12><c12 m='37'>20160927</c12><c12 m='38'>20161
027</c12><c12 m='39'>20161127</c12><c12 m='40'>20161227</c12><c12 m='41'>2017012
7</c12><c12 m='42'>20170227</c12><c13>AABILL13265WLWSR</c13><c13 m='2'>AABILL132
95PD9RK</c13><c13 m='3'>AABILL133263FXPS</c13><c13 m='4'>AABILL133568M6DK</c13><
c13 m='5'>AABILL14022VH2JB</c13><c13 m='6'>AABILL14053G95RF</c13><c13 m='7'>AABI
LL140814YYC3</c13><c13 m='8'>AABILL14112M0MZ4</c13><c13 m='9'>AABILL14142RRH3P</
c13><c13 m='10'>AABILL14173D6GZJ</c13><c13 m='11'>AABILL14203HY9V6</c13><c13 m='
12'>AABILL142341G2BW</c13><c13 m='13'>AABILL142651P9D9</c13><c13 m='14'>AABILL14
295FC8G1</c13><c13 m='15'>AABILL14326LY4DN</c13><c13 m='16'>AABILL14356GTNDX</c1
3><c13 m='17'>AABILL15022FPV3V</c13><c13 m='18'>AABILL1505397QRV</c13><c13 m='19
'>AABILL15081R8PYH</c13><c13 m='20'>AABILL15112NPQ80</c13><c13 m='21'>AABILL1514
2RS60L</c13><c13 m='22'>AABILL1517394B3C</c13><c13 m='23'>AABILL1520399HHS</c13>
<c13 m='24'>AABILL15234G8QQ6</c13><c13 m='25'>AABILL15265G9TT8</c13><c13 m='26'>
AABILL1529522SSF</c13><c13 m='27'>AABILL15326DJW22</c13><c13 m='28'>AABILL15356B
XFX4</c13><c13 m='29'>AABILL16022LVK4B</c13><c13 m='30'>AABILL160535VKDY</c13><c
13 m='31'>AABILL16082999WH</c13><c13 m='32'>AABILL16113HDW4T</c13><c13 m='33'>AA
BILL16143QBV97</c13><c13 m='34'>AABILL16174QWS79</c13><c13 m='35'>AABILL16204Y60
RR</c13><c13 m='36'>AABILL16235V98PJ</c13><c13 m='37'>AABILL16266XSY2W</c13><c13
m='38'>AABILL16296GGLQG</c13><c13 m='39'>AABILL16327720SX</c13><c13 m='40'>AABI
LL16357PBM9X</c13><c13 m='41'>AABILL17022R86MS</c13><c13 m='42'>AABILL1705305GMP
</c13><c14>AAACT13265080Q418K</c14><c14 m='2'>AAACT132952XTB10G3</c14><c14 m='3'
я могу получать значения, подобные этому, одно за другим
1 select xt.* from T24.FBNK_AA_ACCOUNT_DETAILS x ,
2 XMLTABLE('/row' PASSING x.XMLRECORD
3 COLUMNS
4 "CONTRACT.DATE" DATE PATH 'c1',
5 "VALUE.DATE" DATE PATH 'c2',
6 "PAYMENT.START.DATE" DATE PATH 'c5',
7 "MATURITY.DATE" DATE PATH 'c6',
8 "BILL.PAY.DATE" DATE PATH 'c12[@m=8]',
9 "BILL.ID" VARCHAR2(512) PATH 'c13[@m=8]'
10* ) xt where rownum<5
16:13:51 SYS@r17dev11> /
CONTRACT. VALUE.DAT PAYMENT.S MATURITY. BILL.PAY.DATE BILL.ID
--------- --------- --------- --------- ------------------------------ ----------------------------------------
24-AUG-13 24-AUG-13 27-SEP-13 31-AUG-38 27-APR-14 AABILL14112M0MZ4
08-FEB-13 08-FEB-13 13-FEB-13 08-FEB-14
08-FEB-13 08-FEB-13 01-MAR-13 01-AUG-46 01-OCT-13 AABILL132695KKSX
19-FEB-13 19-FEB-13 01-MAR-13 19-FEB-14 01-OCT-13 AABILL13269YF6XQ
а затем для других значений я могу изменить атрибут «m» и получить другие значения. но это решение не настолько практично
1 select xt.* from T24.FBNK_AA_ACCOUNT_DETAILS x ,
2 XMLTABLE('/row' PASSING x.XMLRECORD
3 COLUMNS
4 "CONTRACT.DATE" DATE PATH 'c1',
5 "VALUE.DATE" DATE PATH 'c2',
6 "PAYMENT.START.DATE" DATE PATH 'c5',
7 "MATURITY.DATE" DATE PATH 'c6',
8 "BILL.PAY.DATE" DATE PATH 'c12[@m=7]',
9 "BILL.ID" VARCHAR2(512) PATH 'c13[@m=7]'
10* ) xt where rownum<5
16:13:23 SYS@r17dev11> /
CONTRACT. VALUE.DAT PAYMENT.S MATURITY. BILL.PAY.DATE BILL.ID
--------- --------- --------- --------- ------------------------------ ----------------------------------------
24-AUG-13 24-AUG-13 27-SEP-13 31-AUG-38 27-MAR-14 AABILL140814YYC3
08-FEB-13 08-FEB-13 13-FEB-13 08-FEB-14
08-FEB-13 08-FEB-13 01-MAR-13 01-AUG-46 01-SEP-13 AABILL13239DK15P
19-FEB-13 19-FEB-13 01-MAR-13 19-FEB-14 01-SEP-13 AABILL132391SQT3
я проверял еще несколько документов, где мы можем получить все значения в формате xml в одной строке. я попробовал этот приведенный ниже код, но не смог его завершить, поскольку он выдает всевозможные ошибки
документ, который я нашел -> https://www.ibm.com/developerworks/data/library/techarticle/dm-0708nicola/index.html
1 select xt.* from T24.FBNK_AA_ACCOUNT_DETAILS x ,
2 XMLTABLE('/row' PASSING x.XMLRECORD
3 COLUMNS
4 "CONTRACT.DATE" DATE PATH 'c1',
5 "VALUE.DATE" DATE PATH 'c2',
6 "PAYMENT.START.DATE" DATE PATH 'c5',
7 "MATURITY.DATE" DATE PATH 'c6',
8 "BILL.PAY.DATE" '<strong/>XML</strong>' PATH 'c12',
9 "BILL.ID" '<strong>XML</strong>' PATH 'c13'
10* ) xt where rownum<5;
16:43:50 11 /
"BILL.PAY.DATE" '<strong/>XML</strong>' PATH 'c12',
*
ERROR at line 8:
ORA-00902: invalid datatype
я хочу, чтобы все множественные значения из c13 были только в одной строке, и она содержит более 20 множественных значений..
Привет, Алекс ,
мое фактическое требование — перевести все данные из этого xml в формат csv с каждым значением в отдельном столбце, но эти c12 и c13 сильно усложняют задачу, поскольку они содержат атрибуты до 42, такие как m1, m2, .,., m42, и теперь, когда я использовал ur query, он выдает мне помещенный в один столбец, но перенос затрудняет чтение в Excel дерьмо. вывод в Excel дерьмовый. возможно ли получить значения каждого из «m» в разных столбцах или какой-либо другой метод для достижения этой цели? . я подумал, что будет неплохо получить его в формате xml. таблица содержит 300 тысяч строк.
так будет ли лучше получить данные для c12 и c13 в формате xml или varchar с каждым значением «m» в отдельном столбце. единственное условие — это то, что он должен быть в удобочитаемом формате в Excel shit. нужен ваш совет по этому поводу. таблица содержит 300 тыс. строк
Комментарии:
1. Возможно, полезно включить пример XML-записи.
2. Вы сказали, что хотите, чтобы они были в строках и столбцах; в вашей правке говорится, что они нужны (но теперь только c13?) в одной строке, но не объясняется, как. Вы упоминаете XML, но неясно, является ли это тем, что вы хотите или думаете, что у вас должно быть, или какую форму это должно принимать. Пожалуйста, поясните и покажите результат, которого вы хотите добиться для этого примера данных.
3. нет, я хочу c12 в другом столбце и c13 также в другом столбце. приношу свои извинения, я не смог должным образом объяснить проблемы.
Ответ №1:
Вы могли бы использовать цикл в вашем выражении XPath для преобразования вашего XML в отдельную фиктивную строку для каждой пары дата / идентификатор счета, что-то вроде:
select xt.*
from t24.fbnk_aa_account_details x
cross join xmltable('for $r in /row, $i in (1 to count(row/c12))
return (
element {"row"} {
element {"c1"} {$r/c1},
element {"c2"} {$r/c2},
element {"c5"} {$r/c5},
element {"c6"} {$r/c6},
element {"c12"} {$r/c12[$i]},
element {"c13"} {$r/c13[$i]}
}
)'
passing x.xmlrecord
columns
"CONTRACT.DATE" date path 'c1',
"VALUE.DATE" date path 'c2',
"PAYMENT.START.DATE" date path 'c5',
"MATURITY.DATE" date path 'c6',
"BILL.PAY.DATE" date path 'c12',
"BILL.ID" varchar2(20) path 'c13'
) xt
Демонстрация с тем же составленным XML-документом, который выдает ту же ошибку, которую вы видите в исходном запросе:
-- CTE for sample data
with fbnk_aa_account_details (xmlrecord) as (
select xmltype('<row>
<c1>2010-01-02</c1>
<c2>2010-02-03</c2>
<c5>2010-03-04</c5>
<c6>2019-12-31</c6>
<c12>2010-04-05</c12>
<c13>m1</c13>
<c12>2010-05-05</c12>
<c13>m2</c13>
<c12>2010-06-05</c12>
<c13>m3</c13>
</row>') from dual
)
-- actual query
select xt.*
from fbnk_aa_account_details x
cross join xmltable('for $r in /row, $i in (1 to count(row/c12))
return (
element {"row"} {
element {"c1"} {$r/c1},
element {"c2"} {$r/c2},
element {"c5"} {$r/c5},
element {"c6"} {$r/c6},
element {"c12"} {$r/c12[$i]},
element {"c13"} {$r/c13[$i]}
}
)'
passing x.xmlrecord
columns
"CONTRACT.DATE" date path 'c1',
"VALUE.DATE" date path 'c2',
"PAYMENT.START.DATE" date path 'c5',
"MATURITY.DATE" date path 'c6',
"BILL.PAY.DATE" date path 'c12',
"BILL.ID" varchar2(20) path 'c13'
) xt;
который получает:
CONTRACT. VALUE.DAT PAYMENT.S MATURITY. BILL.PAY. BILL.ID
--------- --------- --------- --------- --------- --------------------
02-JAN-10 03-FEB-10 04-MAR-10 31-DEC-19 05-APR-10 m1
02-JAN-10 03-FEB-10 04-MAR-10 31-DEC-19 05-MAY-10 m2
02-JAN-10 03-FEB-10 04-MAR-10 31-DEC-19 05-JUN-10 m3
Я полагаю, вы также могли бы сделать это с помощью преобразования XLST, но я не совсем знаком с ними.
С немного урезанной версией образца XML, который вы добавили в вопрос, этот код получает:
CONTRACT. VALUE.DAT PAYMENT.S MATURITY. BILL.PAY. BILL.ID
--------- --------- --------- --------- --------- --------------------
24-AUG-13 24-AUG-13 27-SEP-13 31-AUG-38 27-SEP-13 AABILL13265WLWSR
24-AUG-13 24-AUG-13 27-SEP-13 31-AUG-38 27-OCT-13 AABILL13295PD9RK
24-AUG-13 24-AUG-13 27-SEP-13 31-AUG-38 27-NOV-13 AABILL133263FXPS
24-AUG-13 24-AUG-13 27-SEP-13 31-AUG-38 27-DEC-13 AABILL133568M6DK
24-AUG-13 24-AUG-13 27-SEP-13 31-AUG-38 27-JAN-14 AABILL14022VH2JB
24-AUG-13 24-AUG-13 27-SEP-13 31-AUG-38 27-FEB-14 AABILL14053G95RF
24-AUG-13 24-AUG-13 27-SEP-13 31-AUG-38 27-MAR-14 AABILL140814YYC3
24-AUG-13 24-AUG-13 27-SEP-13 31-AUG-38 27-APR-14 AABILL14112M0MZ4
24-AUG-13 24-AUG-13 27-SEP-13 31-AUG-38 27-MAY-14 AABILL14142RRH3P
24-AUG-13 24-AUG-13 27-SEP-13 31-AUG-38 27-JUN-14 AABILL14173D6GZJ
24-AUG-13 24-AUG-13 27-SEP-13 31-AUG-38 27-JUL-14 AABILL14203HY9V6
24-AUG-13 24-AUG-13 27-SEP-13 31-AUG-38 27-AUG-14 AABILL142341G2BW
24-AUG-13 24-AUG-13 27-SEP-13 31-AUG-38 27-SEP-14 AABILL142651P9D9
24-AUG-13 24-AUG-13 27-SEP-13 31-AUG-38 27-OCT-14 AABILL14295FC8G1
24-AUG-13 24-AUG-13 27-SEP-13 31-AUG-38 27-NOV-14 AABILL14326LY4DN
24-AUG-13 24-AUG-13 27-SEP-13 31-AUG-38 27-DEC-14 AABILL14356GTNDX
но в этой правке также упоминалось, что все эти значения bill по-прежнему сохраняются в виде одного фрагмента XML, так что вы также могли бы сделать:
select xt.*
from t24.fbnk_aa_account_details x
cross join xmltable('/row'
passing x.xmlrecord
columns
"CONTRACT.DATE" date path 'c1',
"VALUE.DATE" date path 'c2',
"PAYMENT.START.DATE" date path 'c5',
"MATURITY.DATE" date path 'c6',
"BILL.PAY.DATE" xmltype path 'c12',
"BILL.ID" xmltype path 'c13'
) xt;
который генерирует (с неудобным переносом для отображения!):
CONTRACT. VALUE.DAT PAYMENT.S MATURITY. BILL.PAY.DATE BILL.ID
--------- --------- --------- --------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
24-AUG-13 24-AUG-13 27-SEP-13 31-AUG-38 <c12>20130927</c12><c12 m="2">20131027</c12><c12 m="3">20131127</c12><c12 m="4"> <c13>AABILL13265WLWSR</c13><c13 m="2">AABILL13295PD9RK</c13><c13 m="3">AABILL133
20131227</c12><c12 m="5">20140127</c12><c12 m="6">20140227</c12><c12 m="7">20140 263FXPS</c13><c13 m="4">AABILL133568M6DK</c13><c13 m="5">AABILL14022VH2JB</c13><
327</c12><c12 m="8">20140427</c12><c12 m="9">20140527</c12><c12 m="10">20140627< c13 m="6">AABILL14053G95RF</c13><c13 m="7">AABILL140814YYC3</c13><c13 m="8">AABI
/c12><c12 m="11">20140727</c12><c12 m="12">20140827</c12><c12 m="13">20140927</c LL14112M0MZ4</c13><c13 m="9">AABILL14142RRH3P</c13><c13 m="10">AABILL14173D6GZJ<
12><c12 m="14">20141027</c12><c12 m="15">20141127</c12><c12 m="16">20141227</c12 /c13><c13 m="11">AABILL14203HY9V6</c13><c13 m="12">AABILL142341G2BW</c13><c13 m=
> "13">AABILL142651P9D9</c13><c13 m="14">AABILL14295FC8G1</c13><c13 m="15">AABILL1
4326LY4DN</c13><c13 m="16">AABILL14356GTNDX</c13>
Другие варианты заключаются в агрегировании значений из предыдущего запроса; или иметь пару столбцов на m
значение (при условии, что у вас есть разумный верхний предел для этого), либо непосредственно в вызове XMLTable, либо позже путем поворота; или манипулировать информацией о счете в одном новом узле на дату / идентификатор и возвращать все это по-прежнему в формате XML… это зависит от того, какой вывод вам действительно нужен.