oracle解析xml
测试数据
create table TEST_XML
(
xmls CLOB
);
insert into TEST_XML (XMLS)
values ('<?xml version="1.0" encoding="GB2312"?>
<messages>
<note id="501">
<to>George</to>
<from type="admin">John</from>
<heading>Reminder</heading>
<body>Dont forget the meeting!</body>
</note>
<note id="502">
<to>John</to>
<from type="leader">George</from>
<heading>Re: Reminder</heading>
<body>I will not</body>
</note>
<note id="503">
<to>HJJ</to>
<from type="user">HJJ</from>
<heading>Re: Reminder</heading>
<body>not</body>
</note>
</messages>');
commit;xml转char
SELECT TO_CHAR(T.XMLS)
FROM TEST_XML T;这个方法只适合clob比较短的xml，如果xml字段过大会报错需要用SUBSTR()函数来切割
解析整个XML
SELECT B.*
FROM TEST_XML T,
XMLTABLE('/messages/note' PASSING XMLTYPE(T.XMLS)
COLUMNS "ID" VARCHAR2(50) PATH '@id',
"TO" VARCHAR2(50) PATH '/note/to',
"FROM" VARCHAR2(50) PATH '/note/from' ,
"FROM_TYPE" VARCHAR2(50) PATH '/note/from/@type' ,
"HAEDING" VARCHAR2(50) PATH '/note/heading',
"BODY" VARCHAR2(50) PATH '/note/body') B;读取特定ID的记录
可以在PASSING路径中指定,或者在where条件中
SELECT B.*
FROM TEST_XML T,
XMLTABLE('/messages/note[@id=501]' PASSING XMLTYPE(T.XMLS)
COLUMNS "ID" VARCHAR2(50) PATH '@id',
"TO" VARCHAR2(50) PATH '/note/to',
"FROM" VARCHAR2(50) PATH '/note/from' ,
"FROM_TYPE" VARCHAR2(50) PATH '/note/from/@type' ,
"HAEDING" VARCHAR2(50) PATH '/note/heading',
"BODY" VARCHAR2(50) PATH '/note/body') B;
SELECT B.*
FROM TEST_XML T,
XMLTABLE('/messages/note' PASSING XMLTYPE(T.XMLS)
COLUMNS "ID" VARCHAR2(50) PATH '@id',
"TO" VARCHAR2(50) PATH '/note/to',
"FROM" VARCHAR2(50) PATH '/note/from' ,
"FROM_TYPE" VARCHAR2(50) PATH '/note/from/@type' ,
"HAEDING" VARCHAR2(50) PATH '/note/heading',
"BODY" VARCHAR2(50) PATH '/note/body') B
where B.ID = 501获取特定属性的特定元素
也可也使用XMLTABLE
SELECT
EXTRACTVALUE(XMLTYPE(T.XMLS),
'/messages/note[@id=502]/from') AS "form"
FROM TEST_XML T;本站文章除注明转载/出处外，均为本站原创，转载前请务必署名,转载请标明出处 最后编辑时间为: 2021/04/14 16:31:17
举报
腾讯云开发者
