|
Разбор XML с указанием значения узла в путях
#40054176
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
|
andrey_anonymous,
чем поможет Multitable Inserts при 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.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
<?xml version="1.0" encoding="UTF-8"?>
<ED_Container>
<Object>
<ROW1>
<Line1>Line1</Line1>
<Line2>Line2</Line2>
<Line3>Line3</Line3>
<GoodsCost>
<SheetNumber>1</SheetNumber>
<GoodsNumber>1</GoodsNumber>
<MethodNumberCode>1</MethodNumberCode>
<Group1>
<Line_g1>Lineg11</Line_g1>
</Group1>
<Group1>
<Line_g1>Line_line</Line_g1>
</Group1>
</GoodsCost>
<GoodsCost>
<SheetNumber>2</SheetNumber>
<GoodsNumber>2</GoodsNumber>
<MethodNumberCode>2</MethodNumberCode>
<Group1>
<Line_g1>Lineg1</Line_g1>
<Line_g12>Lineg12</Line_g12>
</Group1>
<Group2>
<Line_g2>Lineg2</Line_g2>
</Group2>
</GoodsCost>
<GoodsCost>
<SheetNumber>3</SheetNumber>
<GoodsNumber>3</GoodsNumber>
<MethodNumberCode>2</MethodNumberCode>
</GoodsCost>
</ROW1>
</Object>
<Object>
<ROW1>
<Line1>Line1</Line1>
<GoodsCost>
<SheetNumber>5</SheetNumber>
<GoodsNumber>1</GoodsNumber>
<MethodNumberCode>5</MethodNumberCode>
<Group2>
<Line_g2>Lineg22</Line_g2>
</Group2>
</GoodsCost>
<GoodsCost>
<SheetNumber>6</SheetNumber>
<GoodsNumber>2</GoodsNumber>
<MethodNumberCode>6</MethodNumberCode>
</GoodsCost>
<GoodsCost>
<SheetNumber>7</SheetNumber>
<GoodsNumber>3</GoodsNumber>
<MethodNumberCode>7</MethodNumberCode>
</GoodsCost>
</ROW1>
</Object>
<Object>
<ROW2>
<NUM_PP>1</NUM_PP>
<Comments>a1</Comments>
<Group_r1>
<Line_r1>Liner1</Line_r1>
</Group_r1>
<GoodsMark>
<GoodsNumber>1</GoodsNumber>
<Decision>
<DecisionCode>a1</DecisionCode>
<DateInf>2021-02-20</DateInf>
<DecisionDescription>Description1</DecisionDescription>
</Decision>
</GoodsMark>
<GoodsMark>
<GoodsNumber>2</GoodsNumber>
<Decision>
<DecisionCode>a2</DecisionCode>
<DateInf>2021-02-22</DateInf>
<DecisionDescription>Description2</DecisionDescription>
</Decision>
</GoodsMark>
</ROW2>
</Object>
</ED_Container>
И вставки
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.
59.
60.
61.
62.
63.
64.
insert into t1 select a.*,b.prim
from tx,xmltable ('/ED_Container/Object/ROW1'
PASSING xml_file COLUMNS
NUM_PP FOR ORDINALITY ,
Line1 VARCHAR2(10) PATH 'Line1',
Line2 VARCHAR2(10) PATH 'Line2',
Line3 VARCHAR2(10) PATH 'Line3') a,
xmltable('/ED_Container/Object/ROW2'
PASSING xml_file COLUMNS
NUM_PP_2 number PATH 'NUM_PP' ,
prim VARCHAR2(10) PATH 'Comments') where NUM_PP=NUM_PP_2(+) ;
insert into t2
select NUM_PP,SHEET_N,GOOD_N, NMET, DC,DC_DATE, DC_DESC
from tx,xmltable ('/ED_Container/Object/ROW1'
PASSING xml_file COLUMNS
NUM_PP FOR ORDINALITY ,
tab_xml_ XMLTYPE PATH 'GoodsCost') ,
xmltable ('GoodsCost'
PASSING tab_xml_ COLUMNS
SHEET_N NUMBER(5) PATH 'SheetNumber',
GOOD_N NUMBER(5) PATH 'GoodsNumber',
NMET VARCHAR2(2) PATH 'MethodNumberCode') ,
xmltable('/ED_Container/Object/ROW2[NUM_PP=$p1]/GoodsMark[GoodsNumber=$p2]'
PASSING xml_file, NUM_PP as "p1",GOOD_N as "p2" COLUMNS
DC VARCHAR2(2) PATH 'Decision/DecisionCode',
DC_DATE DATE PATH 'Decision/DateInf',
DC_DESC VARCHAR2(50) PATH 'Decision/DecisionDescription')(+);
insert into t3
select a.* from tx, xmltable('/ED_Container/Object/ROW2'
PASSING xml_file COLUMNS
NUM_PP number PATH 'NUM_PP' ,
Line_r1 VARCHAR2(10) PATH 'Group_r1/Line_r1') a;
insert into t4
select NUM_PP,SHEET_N ,GOOD_N,b.* from tx,xmltable ('/ED_Container/Object/ROW1'
PASSING xml_file COLUMNS
NUM_PP FOR ORDINALITY ,
tab_xml_ XMLTYPE PATH 'GoodsCost') ,
xmltable ('GoodsCost'
PASSING tab_xml_ COLUMNS
SHEET_N NUMBER(5) PATH 'SheetNumber',
GOOD_N NUMBER(5) PATH 'GoodsNumber',
tab_xml_2 XMLTYPE PATH 'Group1') ,
xmltable ('Group1'
PASSING tab_xml_2 COLUMNS
Line_g1 VARCHAR2(10) PATH 'Line_g1',
Line_g12 VARCHAR2(10) PATH 'Line_g12') b;
insert into t5
select NUM_PP,SHEET_N ,GOOD_N,b.* from tx,xmltable ('/ED_Container/Object/ROW1'
PASSING xml_file COLUMNS
NUM_PP FOR ORDINALITY ,
tab_xml_ XMLTYPE PATH 'GoodsCost') ,
xmltable ('GoodsCost'
PASSING tab_xml_ COLUMNS
SHEET_N NUMBER(5) PATH 'SheetNumber',
GOOD_N NUMBER(5) PATH 'GoodsNumber',
tab_xml_2 XMLTYPE PATH 'Group2') ,
xmltable ('Group2'
PASSING tab_xml_2 COLUMNS
Line_g2 VARCHAR2(10) PATH 'Line_g2',
Line_g22 VARCHAR2(10) PATH 'Line_g22') b;
и еще несколько десятков таблиц |
|
|
