使用oracle解析xml
现有表A,表A里面a1字段类型为XMLTYPE,能否使用sql将此字段xml的value值解析出来....
现有表A,表A里面a1字段类型为XMLTYPE,能否使用sql将此字段xml的value值解析出来.
展开
2个回答
展开全部
DECLARE
v_xmlstr VARCHAR2(1000);
v_xml XMLTYPE;
v_title VARCHAR2(50);
v_language VARCHAR2(50);
v_body VARCHAR2(50);
BEGIN
v_xmlstr := '<html>
<head>
<title>This IS title.</title>
<LANGUAGE>JavaScript</LANGUAGE>
</head>
<body>I am BODY.</body>
</html>';
v_xml := XMLTYPE(v_xmlstr); -- 生成XML
SELECT EXTRACTVALUE(VALUE(t),'/html/head/title')
,EXTRACTVALUE(VALUE(t),'/html/head/LANGUAGE')
,EXTRACTVALUE(VALUE(t),'/html/body')
INTO v_title ,v_language ,v_body
FROM TABLE(XMLSEQUENCE(EXTRACT(v_xml, '/html'))) t; -- 提取XML节点值
dbms_output.put_line(v_title);
dbms_output.put_line(v_language);
dbms_output.put_line(v_body);
END;
/
结果：
This IS title.
JavaScript
I am BODY.
v_xmlstr VARCHAR2(1000);
v_xml XMLTYPE;
v_title VARCHAR2(50);
v_language VARCHAR2(50);
v_body VARCHAR2(50);
BEGIN
v_xmlstr := '<html>
<head>
<title>This IS title.</title>
<LANGUAGE>JavaScript</LANGUAGE>
</head>
<body>I am BODY.</body>
</html>';
v_xml := XMLTYPE(v_xmlstr); -- 生成XML
SELECT EXTRACTVALUE(VALUE(t),'/html/head/title')
,EXTRACTVALUE(VALUE(t),'/html/head/LANGUAGE')
,EXTRACTVALUE(VALUE(t),'/html/body')
INTO v_title ,v_language ,v_body
FROM TABLE(XMLSEQUENCE(EXTRACT(v_xml, '/html'))) t; -- 提取XML节点值
dbms_output.put_line(v_title);
dbms_output.put_line(v_language);
dbms_output.put_line(v_body);
END;
/
结果：
This IS title.
JavaScript
I am BODY.
本回答由提问者推荐
已赞过
已踩过<
评论
收起
你对这个回答的评价是？
推荐律师服务：
若未解决您的问题，请您详细描述您的问题，通过百度律临进行免费专业咨询
