通過這個查詢，我將所有內容都放在標簽 ROW 中。但我想把所有的東西都放在標簽 <field_without_interest> 之外。
select xmltype(
'<?xml version="1.0"?>
<ROWSET>
<ROW>
<PERSON_NUMBER>1000142</PERSON_NUMBER>
<LOAN_1>25000</LOAN_1>
</ROW>
<ROW>
<PERSON_NUMBER>1000142</PERSON_NUMBER>
<LOAN_1>25000</LOAN_1>
</ROW>
</ROWSET>'
).extract( '/ROWSET/ROW' ) .getstringval() p#
from dual;
我可以選擇子標簽
select xmltype(
'<?xml version="1.0"?>
<ROWSET>
<ROW>
<PERSON_NUMBER>1000142</PERSON_NUMBER>
<LOAN_1>25000</LOAN_1>
<field_without_interest>f</field_without_interest>
</ROW>
<ROW>
<PERSON_NUMBER>1000142</PERSON_NUMBER>
<LOAN_1>25000</LOAN_1>
</ROW>
</ROWSET>'
).extract( '/ROWSET/ROW/PERSON_NUMBER' ) .getstringval() p#
from dual;
但我只選擇了我感興趣的一半標簽。
有沒有辦法寫extract( '/ROWSET/ROW/PERSON_NUMBER' AND '/ROWSET/ROW/LOAN_1' )
或
extract( '/ROWSET/ROW/ except'/ROWSET/ROW/field_without_interest' )
代碼
uj5u.com熱心網友回復：
您可以使用XMLQUERYFLOWR。
如果你只想要第一個ROW，那么：
SELECT XMLQUERY(
'copy $NEWHTML := /ROWSET/ROW[1]
modify (
for $i in $NEWHTML/field_without_interest
return delete node $i
)
return $NEWHTML/*'
PASSING xmltype(
'<?xml version="1.0"?>
<ROWSET>
<ROW>
<PERSON_NUMBER>1000142</PERSON_NUMBER>
<LOAN_1>25000</LOAN_1>
<field_without_interest>f</field_without_interest>
</ROW>
<ROW>
<PERSON_NUMBER>1000142</PERSON_NUMBER>
<LOAN_1>25000</LOAN_1>
</ROW>
</ROWSET>'
)
RETURNING CONTENT
).getStringVal() AS interested_elements
FROM DUAL;
哪個輸出：
INTERESTED_ELEMENTS <PERSON_NUMBER>1000142</PERSON_NUMBER><LOAN_1>25000</LOAN_1>
或者，如果你想要整個ROWSET，你可以使用：
SELECT XMLQUERY(
'copy $NEWHTML := .
modify (
for $i in $NEWHTML/ROWSET/ROW/field_without_interest
return delete node $i
)
return $NEWHTML'
PASSING xmltype(
'<?xml version="1.0"?>
<ROWSET>
<ROW>
<PERSON_NUMBER>1000142</PERSON_NUMBER>
<LOAN_1>25000</LOAN_1>
<field_without_interest>f</field_without_interest>
</ROW>
<ROW>
<PERSON_NUMBER>1000142</PERSON_NUMBER>
<LOAN_1>25000</LOAN_1>
</ROW>
</ROWSET>'
)
RETURNING CONTENT
).getStringVal() AS interested_elements
FROM DUAL;
哪個輸出：
INTERESTED_ELEMENTS <?xml version="1.0"?><ROWSET><ROW><PERSON_NUMBER>1000142</PERSON_NUMBER><LOAN_1>25000</LOAN_1></ROW><ROW><PERSON_NUMBER>1000142</PERSON_NUMBER><LOAN_1> 25000</LOAN_1></ROW></ROWSET>
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/478387.html
上一篇：統計odbcxml匯出（超時）
下一篇：如何僅提取此xml元素中的子標簽