今天遇到了关于oracle里面处理xml,查阅了网上很多资料,①有提到使用dom来处理xml或者②直接使用xmltype、extract、extractvalue等来处理,我测试整理了下第二个方案,因此在这里作个记录。
- 测试表
为了方便测试,我新建了一张测试表,其中包含个clob格式的字段,一个xmltype格式字段,一个id,下面的数据我暂时只录入了RE_STR这个clob字段,而xmltype格式字段未录入值。
实际运用中看你这个xml到底有好大,需要建clob即建。
分别展示哈数据:id=2 (不带前缀 或者命名空间)
这里我并没有<?xml ?>这个头部,但是暂时没的问题,内容是xml节点对称的。
id=3(不带前缀 或者命名空间)
id = 1 (带前缀和命名空间)
- 关于xml的sql
①clob转换为xmltype(提供了xmltype格式转换,类似于to_char,to_date)
SELECT xmltype(t.re_str) FROM tb_parse_xml t where t.id = 2;
②extractvalue函数,它定义只能返回一个节点上的一个值,如果该节点有多个值则会报错,它接受三个参数:第一个是xmltype格式的源,第二个是xpath路径,第三个是命名空间。
SELECT extractvalue(xmltype(t.re_str), '/ROWSET/ROW/LIST/LISTITEM/OUID')
FROM tb_parse_xml t
where t.id = 2;
③extract函数,它定义返回一个节点下的所有值得xmltype格式,用法同2也支持三个参数。
SELECT extract(xmltype(t.re_str), '/ROWSET/ROW/LIST/LISTITEM/OUID')
FROM tb_parse_xml t
where t.id = 2;
④table和xmlsequence函数,两个函数并用一起遍历出该xml下指定路径下的所有值并以表的形式展示。
SELECT extractvalue(value(i), '/OUNAME') F_OUNAME
FROM tb_parse_xml t,
table(xmlsequence(extract(xmltype(t.re_str),
'/ROWSET/ROW/LIST/LISTITEM/OUNAME'))) i
where t.id = 2;
⑤xmltable解析为表且带多个字段。其中columns为想要的字段名,path指xml中的路径到指定标签。
SELECT x.*
FROM tb_parse_xml t,
xmltable('/ROWSET/ROW/LIST/LISTITEM' passing xmltype(t.re_str)
columns F_OUID varchar2(100) path 'OUID',
F_OUNAME varchar2(100) path 'OUNAME') x
where t.id = 2;
⑥这里特别说明下带前缀和命名空间的解析,我例子中解析了第一层带前缀和命名空间。当我如法炮制解析子节点也带前缀和命名空间时不成功。
SELECT xmltype.createxml(t.re_str)
.extract(
'/ENV:ENVELOPE/ENV:BODY/child::node()',
'xmlns:ENV="http://schemas.xmlsoap.org/soap/envelope/"')
FROM tb_parse_xml t
where t.id = 1;
这里使用createxml有点画蛇添足,只是借用这个表数据展示下。这里返回body下面的所有xml标签,但是我这里又有一个带命名空间的标签,如法炮制未能解析成功,卡在这里了,只解析了第一层。
其中oracle解析xml还有种使用dom来解析或还有其他函数,这里未作记录,解析不带前缀和命名空间上面的够用了,解析带前缀和命名空间的,我这里只解析了第一层,若子节点也带,暂时没解决,待后面来补充。