使用XMLTABLE的Xpath

Question

使用此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很不在行，我被困在这里了，但是下面这段代码包含了所有的值：

XMLTABLE('/attrs'
    PASSING XMLTYPE(XML)
    COLUMNS LETTER VARCHAR2(50) PATH 'attr[@name="LETTER"]',
            NUMBER VARCHAR2(50) PATH 'attr[@name="NUMBER"]'
) X

谢谢。

Answer 1

这里是更简单的情况的解决方案，它需要固定数量的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 

Answer 2

谢谢你Marmite轰炸机，这是非常棘手的。

下面是我使用的工作请求：

WITH Q1 AS (
    SELECT * FROM (
        SELECT X.*
        FROM PRODUCT P,
        XMLTABLE('for $i in /attrs/attr return $i'
            PASSING XMLTYPE(P.XML)
            COLUMNS ATTRIBUTENAME VARCHAR2(50) PATH '//@name',
                    S1 VARCHAR2(50) path '//string[1]',
                    S2 VARCHAR2(50) path '//string[2]',
                    S3 VARCHAR2(50) path '//string[3]',
                    S4 VARCHAR2(50) path '//string[4]',
                    S5 VARCHAR2(50) path '//string[5]',
                    S6 VARCHAR2(50) path '//string[6]',
                    S7 VARCHAR2(50) path '//string[7]',
                    S8 VARCHAR2(50) path '//string[8]',
                    S9 VARCHAR2(50) path '//string[9]',
                    S10 VARCHAR2(50) path '//string[10]'
        ) X
        WHERE X.ATTRIBUTENAME IN ('LETTER', 'NUMBER')
        AND P.REF = 'REF1')
    UNPIVOT (
        X
        FOR SRC 
        IN (
            S1 AS 'Q', 
            S2 AS 'R',
            S3 as 'S',
            S4 as 'T',
            S5 as 'U',
            S6 as 'V',
            S7 as 'W',
            S8 as 'X',
            S9 as 'Y',
            S10 as 'Z'
        )
    )
), Q2 AS (
    SELECT * FROM Q1
    PIVOT (
        MAX(X) "VAL" 
        FOR (ATTRIBUTENAME)
        IN (
            'LETTER' as "LETTER",
            'NUMBER' as "NUMBER"
        )
    )
) SELECT LETTER_VAL AS "LETTER", NUMBER_VAL AS "NUMBER"
FROM Q2
ORDER BY 1;