英文:
Need to parse xmltype values by querying it in Oracle DB
问题
我们需要使用选择语句从xmltype列中捕获值。以下是列"Test"中的值。
<c18>A</c18><c18 m="2">B</c18><c18 m="3">C</c18><c18 m="4"D</c18><c18 m="5">E</c18><c18 m="6">F</c18><c18 m="7">G</c18><c18 m="8">H</c18>
我们需要有8个新列，它们应该命名为"C1"、"C2"等，具有值A、B、C等等。
英文:
We have a requirement to capture the values from xmltype column using select statement. Below is the value we have in the column "Test".
<c18>A</c18><c18 m="2">B</c18><c18 m="3">C</c18><c18 m="4"D</c18><c18 m="5">E</c18><c18 m="6">F</c18><c18 m="7">G</c18><c18 m="8">H</c18>
We need to have 8 new columns which should be named as "C1", "C2", etc having values A,B,C and so on.
答案1
得分: 1
您的XML无效（在m="4"之后缺少闭合>，并且没有根元素）。如果您修复了这个问题，然后您可以使用：
SELECT x.*
FROM table_name t
CROSS APPLY XMLTABLE(
'/root'
PASSING t.test
COLUMNS
c1 VARCHAR2(20) PATH './c18[not(@m)]',
c2 VARCHAR2(20) PATH './c18[@m="2"]',
c3 VARCHAR2(20) PATH './c18[@m="3"]',
c4 VARCHAR2(20) PATH './c18[@m="4"]',
c5 VARCHAR2(20) PATH './c18[@m="5"]',
c6 VARCHAR2(20) PATH './c18[@m="6"]',
c7 VARCHAR2(20) PATH './c18[@m="7"]',
c8 VARCHAR2(20) PATH './c18[@m="8"]'
) x
对于示例数据：
CREATE TABLE table_name (test XMLTYPE);
INSERT INTO table_name (test)
VALUES (XMLTYPE( '<root><c18>A</c18><c18 m="2">B</c18><c18 m="3">C</c18><c18 m="4">D</c18><c18 m="5">E</c18><c18 m="6">F</c18><c18 m="7">G</c18><c18 m="8">H</c18></root>' ) );
输出：
| C1 | C2 | C3 | C4 | C5 | C6 | C7 | C8 |
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H |
英文:
Your XML is invalid (it is missing a closing > after m="4" and does not have a root element). If you fix that then you can use:
SELECT x.*
FROM table_name t
CROSS APPLY XMLTABLE(
'/root'
PASSING t.test
COLUMNS
c1 VARCHAR2(20) PATH './c18[not(@m)]',
c2 VARCHAR2(20) PATH './c18[@m="2"]',
c3 VARCHAR2(20) PATH './c18[@m="3"]',
c4 VARCHAR2(20) PATH './c18[@m="4"]',
c5 VARCHAR2(20) PATH './c18[@m="5"]',
c6 VARCHAR2(20) PATH './c18[@m="6"]',
c7 VARCHAR2(20) PATH './c18[@m="7"]',
c8 VARCHAR2(20) PATH './c18[@m="8"]'
) x
Which, for the sample data:
CREATE TABLE table_name (test XMLTYPE);
INSERT INTO table_name (test)
VALUES (XMLTYPE( '<root><c18>A</c18><c18 m="2">B</c18><c18 m="3">C</c18><c18 m="4">D</c18><c18 m="5">E</c18><c18 m="6">F</c18><c18 m="7">G</c18><c18 m="8">H</c18></root>' ) );
Outputs:
| C1 | C2 | C3 | C4 | C5 | C6 | C7 | C8 |
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论