问题描述
我想使用oracle XMLFOREST
函数. (定义链接)
I would like to use the oracle XMLFOREST
function. (Link for definition)
语法示例:
XMLFOREST( [value expression AS alias], [...])
我的问题是,如果值表达式为NULL,则不会为该值表达式创建任何元素.
My problem is that if value expression is NULL, then no element is created for that value expression.
如何强制它添加一个空的XML片段?
How can I enforce it to add an empty XML fragment?
推荐答案
您可以使用 XMLFOREST
:
You can' use XMLFOREST
:
也没有类似的 XMLCOLATTVAL
功能:
Nor the similar XMLCOLATTVAL
function:
另一方面, 为了完整起见,还可以使用另外两个Oracle工具(灵感来自这篇文章在OTN论坛上).首先,您可以使用 For completeness, two more Oracle tools can also be used (inspired by this post on the OTN forums). First you can use an 您还可以使用 You can also use the 这篇关于即使表达式值为空,如何执行xmlforest creat元素的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!XMLELEMENT
手将按要求返回一个空元素:XMLELEMENT
on the other hand returns an empty element as requested:SQL> select xmlelement("EMP", xmlelement("ENAME", ename),
2 xmlelement("JOB", job),
3 xmlelement("MGR", mgr)
4 ).getclobval() xml
5 from scott.emp;
XML
----------------------------------------------------------------
<EMP><ENAME>KING</ENAME><JOB>PRESIDENT</JOB><MGR></MGR></EMP>
<EMP><ENAME>BLAKE</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR></EMP>
...
XMLQUERY
(在11.2上吗?):XMLQUERY
(on 11.2? ):SQL> select
2 xmlquery(
3 '(#ora:view_on_null empty #) {
4 <EMPS> {
5 for $c in fn:collection("oradb:/SCOTT/EMP")/ROW
6 return element EMP {
7 $c/ENAME
8 , $c/JOB
9 , $c/MGR
10 }
11 }</EMPS>
12 }'
13 passing cast(10 as number) as "mid"
14 returning content
15 ).getClobval() as result
16 from dual;
RESULT
----------------------------------------------------------------------
<EMPS>
<EMP><ENAME>KING</ENAME><JOB>PRESIDENT</JOB><MGR></MGR></EMP>
<EMP><ENAME>BLAKE</ENAME>...
DBMS_XMLGEN
包:DBMS_XMLGEN
package:SQL> DECLARE
2 ctx dbms_xmlgen.ctxHandle;
3 sqlstr varchar2(4000)
4 := 'SELECT ename, job, mgr FROM scott.emp WHERE ename=''KING''';
5 res clob;
6 BEGIN
7 ctx := dbms_xmlgen.newContext(sqlstr);
8 dbms_xmlgen.setNullHandling(ctx, dbms_xmlgen.EMPTY_TAG);
9 res := dbms_xmlgen.getXML(ctx);
10 dbms_xmlgen.closeContext(ctx);
11 dbms_output.put_line(res);
12 END;
13 /
<?xml version="1.0"?>
<ROWSET>
<ROW>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
<MGR/>
</ROW>
</ROWSET>