我的XML具有复杂的结构，其中多次出现重复节点(多个行生成器(。
查询：
select X.*, A.id
from tbl_with_blob a,
XMLTABLE(
'$XMLDATA//BusData/LOB1/Vehicle' passing XML_COL_TEXT as "XMLDATA"
COLUMNS
--these columns are getting parsed correctly - since they are having only one node
"vehicle_id" varchar(50) PATH '@id'
,"manufacturer" varchar(100) PATH 'let $x := . return $x/Manufacturer/.'
,"loc_ref_id" varchar(100) PATH 'let $x := . return $x/@LocationRef'
--these columns are throwing error - because there are multiple nodes with the same name
,"x_vc1_1" varchar(1000) PATH 'let $x := . return $x/Vehicle_child1/Vehicle_child1_1/.'
,"x_SC1_1" varchar(1000) PATH 'let $x := . return $x/Vehicle_child1/SomeCode1_1/.'
,"x_Vc2_varcd" varchar(1000) PATH 'let $x := . return $x/Vehicle_child2/@varCd'
,"x_vc2_lmitamt" varchar(100) PATH 'let $x := . return $x/Vehicle_child2/Limit/FormatInteger/.'
) as X
where id='<some_id>'
with ur;
.XML：
<BusData>
<LOB1>
<Vehicle id="A333" LocationRef="A194">
<Manufacturer id="A306">TOYT<Manufacturer>
<Vehicle_child1 id="A303">
<Vehicle_child1_1 id="A400">RoadSideInd</Vehicle_child1_1>
</Vehicle_child1>
<Vehicle_child1 id="A399">
<Vehicle_child1_1 id="A407">Commercial</Vehicle_child1_1>
<SomeCode1_1 id="A436">NO</SomeCode1_1>
</Vehicle_child1>
<Vehicle_child1 id="A434">
<Vehicle_child1_1 id="A412">VehAgreedValue</Vehicle_child1_1>
<SomeCode1_1 id="A474"/>
<Num id="A476">1000</Num>
</Vehicle_child1>
<Vehicle_child2 id="A411" varCd="47" varSomeLimit="500">
<Vehicle_child2Cd id="A412">COLL</Vehicle_child2Cd>
</Vehicle_child2>
<Vehicle_child2 id="A413" varCd="08" varSomeLimit="300">
<Vehicle_child2Cd id="A501">UMCSL</Vehicle_child2Cd>
<SomeLimit id="A498">
<FormatInteger id="A499">30000<FormatInteger>
</SomeLimit>
</Vehicle_child2>
<Vehicle_child2 id="A503" varCd="0A" varSomeLimit="450">
<Vehicle_child2Cd id="A517">CLMFG</Vehicle_child2Cd>
<SomeLimit id="A521">
<FormatInteger id="A532">6000<FormatInteger>
</SomeLimit>
</Vehicle_child2>
<Vehicle>
</LOB1>
</BusData>
预期输出：
id vehicle_id manufacturer loc_ref_id x_vc1_1 x_SC1_1 x_Vc2_varcd x_vc2_lmitamt
HT56NU4M A333 TOYT A194 RoadSideInd (null) 47 (null)
HT56NU4M A333 TOYT A194 RoadSideInd (null) 08 30000
HT56NU4M A333 TOYT A194 RoadSideInd (null) 0A 6000
HT56NU4M A333 TOYT A194 Commercial NO 47 (null)
HT56NU4M A333 TOYT A194 Commercial NO 08 30000
HT56NU4M A333 TOYT A194 Commercial NO 0A 6000
HT56NU4M A333 TOYT A194 VehValue (null) 47 (null)
HT56NU4M N333 TOYT A194 VehValue (null) 08 30000
HT56NU4M N333 TOYT A194 VehValue (null) 0A 6000
当我使用上述查询时 - 我收到以下错误：
DB2 SQL Error: SQLCODE= -16003, SQLSTATE=10507,SQLERRMC=(item(), item()+);item(), DRIVER=4.23.42
我尝试修改行生成表达式以包含其中一个行生成器作为Vehicle_child1，它给出了正确的Vehicle_child1结果，但缺少Vehicle_child2。
我需要同时(实时可以多达 10 或 15 个(行生成器，并为每次出现提供多行。
任何人都可以帮助我 - 如何在 DB2 的 XMLTABLE 函数中使用多个生成行的表达式？
谢谢 在
试试这个：
/*
WITH tbl_with_blob (ID, XML_COL_TEXT) AS
(
VALUES
('HT56NU4M', XMLPARSE(DOCUMENT '<BusData>
<LOB1>
<Vehicle id="A333" LocationRef="A194">
<Manufacturer id="A306">TOYT</Manufacturer>
<Vehicle_child1 id="A303">
<Vehicle_child1_1 id="A400">RoadSideInd</Vehicle_child1_1>
</Vehicle_child1>
<Vehicle_child1 id="A399">
<Vehicle_child1_1 id="A407">Commercial</Vehicle_child1_1>
<SomeCode1_1 id="A436">NO</SomeCode1_1>
</Vehicle_child1>
<Vehicle_child1 id="A434">
<Vehicle_child1_1 id="A412">VehAgreedValue</Vehicle_child1_1>
<SomeCode1_1 id="A474"/>
<Num id="A476">1000</Num>
</Vehicle_child1>
<Vehicle_child2 id="A411" varCd="47" varSomeLimit="500">
<Vehicle_child2Cd id="A412">COLL</Vehicle_child2Cd>
</Vehicle_child2>
<Vehicle_child2 id="A413" varCd="08" varSomeLimit="300">
<Vehicle_child2Cd id="A501">UMCSL</Vehicle_child2Cd>
<SomeLimit id="A498">
<FormatInteger id="A499">30000</FormatInteger>
</SomeLimit>
</Vehicle_child2>
<Vehicle_child2 id="A503" varCd="0A" varSomeLimit="450">
<Vehicle_child2Cd id="A517">CLMFG</Vehicle_child2Cd>
<SomeLimit id="A521">
<FormatInteger id="A532">6000</FormatInteger>
</SomeLimit>
</Vehicle_child2>
</Vehicle>
</LOB1>
</BusData>'))
)
*/
select A.id, X1.*, X2.*
from
tbl_with_blob a
, XMLTABLE
(
'$XMLDATA/BusData/LOB1/Vehicle/Vehicle_child1' passing XML_COL_TEXT as "XMLDATA"
COLUMNS
"vehicle_id" varchar(50) PATH '@id'
, "manufacturer" varchar(100) PATH '../Manufacturer'
, "loc_ref_id" varchar(100) PATH '../@LocationRef'
, "x_vc1_1" varchar(1000) PATH 'Vehicle_child1_1'
, "x_SC1_1" varchar(1000) PATH 'SomeCode1_1'
) X1
, XMLTABLE
(
'$XMLDATA/BusData/LOB1/Vehicle/Vehicle_child2' passing XML_COL_TEXT as "XMLDATA"
COLUMNS
"x_Vc2_varcd" varchar(1000) PATH '@varCd'
,"x_vc2_lmitamt" varchar(100) PATH 'SomeLimit/FormatInteger'
) X2;
是的，马克 - 我用你的方法编写了一个这样的查询：
with sub_x1 as
(
select
A.id, X1.*
from tbl_with_blob a,
XMLTABLE('$XMLDATA//BusData/LOB1/Vehicle/Vehicle_child1' passing XML_COL_TEXT as "XMLDATA"
COLUMNS
"vehicle_id" varchar(50) PATH '@id'
-- and other cols --
) as X1
where id='<some_id>'
),
sub_x2 as
(
select
A.id, X2.*
from tbl_with_blob a,
XMLTABLE('$XMLDATA//BusData/LOB1/Vehicle/Vehicle_child2' passing XML_COL_TEXT as "XMLDATA"
COLUMNS
"some_code1" varchar(50) PATH '@id'
-- and other cols --
) as X1
where id='<some_id>'
),
sub_x3 as
(
select
A.id, X3.*
from tbl_with_blob a,
XMLTABLE('$XMLDATA//BusData/LOB1/Vehicle/some_other_node/s_o_n/some_child1' passing XML_COL_TEXT as "XMLDATA"
COLUMNS
"some_other_cd" varchar(50) PATH '@id'
-- and other cols --
) as X3
where id='<some_id>'
),
sub_x4 as
(
select
A.id, X4.*
from tbl_with_blob a,
XMLTABLE('$XMLDATA//BusData/LOB1/Vehicle/yet_other_node/y_a_c_n/yet_another_child2' passing XML_COL_TEXT as "XMLDATA"
COLUMNS
"yet_other_cd" varchar(50) PATH '@id'
-- and other cols --
) as X4
where id='<some_id>'
),
sub_x5 as
(
select
A.id, X5.*
from tbl_with_blob a,
XMLTABLE('$XMLDATA//BusData/LOB1/Vehicle/yet_other_node/y_o_n/yet_another_child2' passing XML_COL_TEXT as "XMLDATA"
COLUMNS
"yet_other_cd2" varchar(50) PATH '@id'
-- and other cols --
) as X5
where id='<some_id>'
)
select
X1.<cols>,
X2.<cols>,
X3.<cols>,
X4.<cols>,
X5.<cols>,
from X1, X2, X3, X4, X5
where
X1.id=X2.id
and (X1.id=X3.id and X2.id=X3.id)
and (X1.id=X4.id and X2.id=X4.id and X3.id=X4.id)
and (X1.id=X5.id and X2.id=X5.id and X3.id=X5.id and X4.id=X5.id)
with ur;
这将继续运行，每个单独的查询都会提供正确的结果集和唯一行。X1 给出 4 行，X2 给出 9 行，X3 给出 11 行，X4 给出 7 行，X5 给出 6 行。 这些子查询中的每一个都包含 XML XPath，这些 XML XPath 在其直接父节点中重复出现 - 多次。
但作为一个整体查询给出了这些的交叉乘积 - 16632 行。 显然我在 JOIN 条件中缺少一些东西？
谢谢 液化天然气工业委员会