我的建议是使用select语句,使用bulk collect获取变量。我写这篇精选文章是想告诉你我的意思:
with the as (select xmltype('<NewDataSet><Table1><RowNo>1</RowNo><STF_N>STF_1</STF_N><MOD_C>MOD_1</MOD_C><COORD_TP_C>COORD_1</COORD_TP_C><COORD_REMARK>REMARK_1</COORD_REMARK></Table1><Table1><RowNo>2</RowNo><STF_N>STF_2</STF_N><MOD_C>MOD_2</MOD_C><COORD_TP_C>COORD_2</COORD_TP_C><COORD_REMARK>REMARK_2</COORD_REMARK></Table1><Table1><RowNo>3</RowNo><STF_N>STF_3</STF_N><MOD_C>MOD_3</MOD_C><COORD_TP_C>COORD_3</COORD_TP_C><COORD_REMARK>REMARK_3</COORD_REMARK></Table1><Table1><RowNo>4</RowNo><STF_N>STF_4</STF_N><MOD_C>MOD_4</MOD_C><COORD_TP_C>COORD_4</COORD_TP_C><COORD_REMARK>REMARK_4</COORD_REMARK></Table1></NewDataSet>') xml_ from dual)
select tt.*
from the t,
xmltable('/NewDataSet/Table1'
PASSING t.xml_
columns
rowNo varchar2(10) path 'RowNo',
stfNo varchar2(10) path 'STF_N',
modC varchar2(10) path 'MOD_C',
coordTpC varchar2(10) path 'COORD_TP_C',
coordRemark VARCHAR2(10) path 'COORD_REMARK'
) tt;
结果:
"ROWNO","STFNO","MODC" ,"COORDTPC","COORDREMARK"
"1" ,"STF_1","MOD_1","COORD_1" ,"REMARK_1"
"2" ,"STF_2","MOD_2","COORD_2" ,"REMARK_2"
"3" ,"STF_3","MOD_3","COORD_3" ,"REMARK_3"
"4" ,"STF_4","MOD_4","COORD_4" ,"REMARK_4"
如果你需要更多的意见,请告诉我。HTH公司
编辑:
回答下面的问题。是的,不需要硬编码。它是标准的XQuery。如果你更换
xmltable('/NewDataSet/Table1'
具有
xmltable('/*/*'
,得到相同的结果。HTH公司