oracle 中的 XMLType 不为具有 Null 值的列生成标签

问题描述

我正在尝试将下表行转换为 XMLtype，然后转换为 Clob

I am trying to Convert the below table rows to XMLtype and then to clob

ID, PROJ_NO
1   Proj1
2   (null)
3   Proj5

我使用下面的查询将每一行转换为 xml

I used the below Query to convert each row to xml

 select xmltype( cursor(Select * from PROJ_TEST_DEMO where id= 1  )).getclobval() from  dual

结果是

"<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <ID>1</ID>
  <PROJ_NO>Proj1</PROJ_NO>
 </ROW>
</ROWSET>
"

虽然

select xmltype( cursor(Select * from PROJ_TEST_DEMO where id=3  ) ).getclobval() from  dual

给予

"<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <ID>3</ID>
 </ROW>
</ROWSET>
"

是否有阻止 xmltype 排除空值列的选项

Is there a option to stop xmltype from excluding null value columns

推荐答案

您可以将 dbms_xmlgen 与 dbms_xmlgen.setNullHandling(qryCtx, dbms_xmlgen.EMPTY_TAG) 或 dbms_xmlgen.NULL_ATTR 一起使用:

You can use dbms_xmlgen with dbms_xmlgen.setNullHandling(qryCtx, dbms_xmlgen.EMPTY_TAG) or dbms_xmlgen.NULL_ATTR:

例如创建自己的函数

create or replace function f_get_xmltype_with_nulls (cur sys_refcursor, null_handling int default dbms_xmlgen.null_attr)
  return xmltype
as
  /* null_handling may be:
      DROP_NULLS CONSTANT NUMBER:= 0;  Leaves out the tag for NULL elements.
      NULL_ATTR CONSTANT NUMBER:= 1; (Default) Sets xsi:nil="true".
      EMPTY_TAG CONSTANT NUMBER:= 2; Sets, for example, <foo/>.
  */
  res xmltype;
  lc dbms_xmlgen.ctxhandle;
begin
  lc:=dbms_xmlgen.newcontext(cur);
  -- you can replace null_attr with empty_tag here:
  dbms_xmlgen.setnullhandling(lc, null_handling);
  res:=dbms_xmlgen.getxmltype(lc);
  return res;
end;
/

然后你可以在查询中使用它:

then you can use it in queries:

SQL> select f_get_xmltype_with_nulls(cursor(select null x from dual connect by level<10)) x from dual;

X
------------------------------------------------------------------------
<ROWSET xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance">
 <ROW>
  <X xsi:nil = "true"/>
 </ROW>
 <ROW>
  <X xsi:nil = "true"/>
 </ROW>
 <ROW>
  <X xsi:nil = "true"/>
 </ROW>
 <ROW>
  <X xsi:nil = "true"/>
 </ROW>
 <ROW>
  <X xsi:nil = "true"/>
 </ROW>
 <ROW>
  <X xsi:nil = "true"/>
 </ROW>
 <ROW>
  <X xsi:nil = "true"/>
 </ROW>
 <ROW>
  <X xsi:nil = "true"/>
 </ROW>
 <ROW>
  <X xsi:nil = "true"/>
 </ROW>
</ROWSET>

如您所见，此函数的第二个参数是 null_handling:

As you can see second parameter of this function is null_handling:

• DROP_NULLS 常量数:= 0；省略 NULL 元素的标记.
• NULL_ATTR 常量数:= 1；(默认)设置 xsi:nil=true".
• EMPTY_TAG 常量编号:= 2；设置，例如，.

或者您甚至可以将您的函数内联到查询中:

Or you can even inline your function into the query:

with
   function f_get_xmltype_with_nulls (cur sys_refcursor, null_handling int default dbms_xmlgen.null_attr)
     return xmltype
   as
     /* null_handling may be:
         DROP_NULLS CONSTANT NUMBER:= 0;  Leaves out the tag for NULL elements.
         NULL_ATTR CONSTANT NUMBER:= 1; (Default) Sets xsi:nil="true".
         EMPTY_TAG CONSTANT NUMBER:= 2; Sets, for example, <foo/>.
     */
     res xmltype;
     lc dbms_xmlgen.ctxhandle;
   begin
     lc:=dbms_xmlgen.newcontext(cur);
     -- you can replace null_attr with empty_tag here:
     dbms_xmlgen.setnullhandling(lc, null_handling);
     res:=dbms_xmlgen.getxmltype(lc);
     return res;
   end;
select
   f_get_xmltype_with_nulls(cursor(select null as x from dual)) as xxx
from dual
/

具有默认 NULL_ATTR 的结果:

Result with default NULL_ATTR:

XXX
-----------------------------------------------------------------
<ROWSET xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance">
 <ROW>
  <X xsi:nil = "true"/>
 </ROW>
</ROWSET>

默认 EMPTY_TAG 的结果:

Result with default EMPTY_TAG:

select
   f_get_xmltype_with_nulls(cursor(select null as x from dual),2) as xxx
from dual;

XXX
-------------------------------------
<ROWSET>
 <ROW>
  <X/>
 </ROW>
</ROWSET>

这篇关于oracle 中的 XMLType 不为具有 Null 值的列生成标签的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！