sql - Xpath 与 XMLTABLE
问题描述
使用这个 XML:
<attrs>
<attr multiple="true" name="LETTER">
<string>A</string>
<string>B</string>
<string>C</string>
<string>D</string>
</attr>
<attr multiple="true" name="NUMBER">
<string>1</string>
<string>2</string>
<string>3</string>
<string>4</string>
</attr>
...
</attrs>
我正在尝试使用 XMLTABLE 获得此结果:
+------+--------+--------+
| REF | LETTER | NUMBER |
+------+--------+--------+
| REF1 | A | 1 |
| REF1 | B | 2 |
| REF1 | C | 3 |
| ... | ... | ... |
+------+--------+--------+
我对 Xpath 很不满意,我被困在这里,但是这个 concat all values :
XMLTABLE('/attrs'
PASSING XMLTYPE(XML)
COLUMNS LETTER VARCHAR2(50) PATH 'attr[@name="LETTER"]',
NUMBER VARCHAR2(50) PATH 'attr[@name="NUMBER"]'
) X
谢谢。
解决方案
这里是需要固定数量string
元素的更简单情况的解决方案。
在第一步中以转置形式在表中获取数据
create table tab as
with doc as (
select
xmltype(q'{<attrs>
<attr multiple="true" name="LETTER">
<string>A</string>
<string>B</string>
<string>C</string>
<string>D</string>
</attr>
<attr multiple="true" name="NUMBER">
<string>1</string>
<string>2</string>
<string>3</string>
<string>4</string>
</attr>
...
</attrs>}') as doc from DUAL)
select x.* from doc,
XMLTable(
'for $i in /attrs/attr
return $i'
passing (doc.doc)
columns
col_name varchar2(10) path '//attr/@name',
s1 varchar2(10) path '//string[1]',
s2 varchar2(10) path '//string[2]',
s3 varchar2(10) path '//string[3]',
s4 varchar2(10) path '//string[4]'
) x
;
COL_NAME S1 S2 S3 S4
---------- ---------- ---------- ---------- ----------
LETTER A B C D
NUMBER 1 2 3 4
这几乎是您所期望的,但必须转置。我确信有一个简单的方法,但是这个查询有效。应用UNPIVOT
比PIVOT
列重命名。
with q1 as (
select * from tab
UNPIVOT (
x
FOR src
IN (
s1 AS 'X',
s2 AS 'Y',
s3 as 'Z',
s4 as 'U'
)
)),
q2 as (
select * from q1
PIVOT (max(x) "VAL" for (col_name) in
('LETTER' as "LETTER",
'NUMBER' as "NUMBER")
))
select LETTER_VAL as "LETTER", NUMBER_VAL as "NUMBER"
from q2
order by 1;
UNPIVOT 的结果
COL_NAME S X
---------- - ----------
LETTER X A
LETTER Y B
LETTER Z C
LETTER U D
NUMBER X 1
NUMBER Y 2
NUMBER Z 3
NUMBER U 4
PIVOT 的结果
S LETTER_VAL NUMBER_VAL
- ---------- ----------
X A 1
Y B 2
Z C 3
U D 4
最后结果
LETTER NUMBER
---------- ----------
A 1
B 2
C 3
D 4
推荐阅读
- realm - javascript ream.close() not closing, stopped
- string - Why does open with :w and :append still overwrite the file?
- assembly - ASSUME directive using masm
- html - 悬停时无法更改css中的背景图像
- android - Using AsyncTask with Runnable vs Creating AsyncTask Class
- angularjs - Push notifications in Android 8 using IONIC v1
- google-sheets - 如何在 Google 表格中设置双向或三向交易?
- xamarin.ios - 如何在 MDHTMLLABEL 链接中应用可访问性?
- swift - 如何将 viewModel 类注入 WKInterfaceController?
- regex - 如何设置单元格中的最大字符数并在 Google 表格中达到最大字符数时自动跳过 2 行?