即使表达式值为空,如何执行xmlforest creat元素 [英] how to enforce xmlforest creat elements even if expression value is null
问题描述
我想使用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?
推荐答案
You can' use XMLFOREST
:
如果value_expr为null,则不会为该value_expr创建任何元素.
If value_expr is null, then no element is created for that value_expr.
也没有类似的 XMLCOLATTVAL
功能:
Nor the similar XMLCOLATTVAL
function:
您必须为value_expr指定一个值.如果value_expr为null,则不返回任何元素.
You must specify a value for value_expr. If value_expr is null, then no element is returned.
另一方面,
为了完整起见,还可以使用另外两个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元素的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!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>