|
|
|
|
Re: Xmlattribute not returning tag when the value is null [message #689552 is a reply to message #689548] |
Fri, 09 February 2024 13:17   |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
I am assuming that this is a simplification, and that your data comes from a table with multiple rows and that any value in any column in any row may be null. If this is the case, then after reviewing some of the links from the search link that Michel provided, I think what I have demonstrated below is a simple generic solution for new and old versions.
-- test environment:
SCOTT@orcl_12.1.0.2.0> SELECT banner FROM v$version
2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
5 rows selected.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE test_tab
2 (orderidcol VARCHAR2(30),
3 dhlcodecol VARCHAR2(30))
4 /
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT ALL
2 INTO test_tab VALUES ('3434', NULL)
3 INTO test_tab VALUES ('1212', 'A')
4 INTO test_tab VALUES ( NULL, 'B')
5 SELECT * FROM DUAL
6 /
3 rows created.
SCOTT@orcl_12.1.0.2.0> COMMIT
2 /
Commit complete.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM test_tab
2 /
ORDERIDCOL DHLCODECOL
------------------------------ ------------------------------
3434
1212 A
B
3 rows selected.
-- query:
SCOTT@orcl_12.1.0.2.0> SELECT XMLSERIALIZE
2 (CONTENT XMLQUERY
3 ('<Table>
4 {for $i in ora:view("TEST_TAB")/ROW
5 return element PropertySet
6 {attribute OrderId {$i/ORDERIDCOL},
7 attribute DHLCode {$i/DHLCODECOL}}}
8 </Table>'
9 RETURNING CONTENT) AS CLOB INDENT)
10 FROM DUAL
11 /
XMLSERIALIZE(CONTENTXMLQUERY('<TABLE>{FOR$IINORA:VIEW("TEST_TAB")/ROWRETURNELEME
--------------------------------------------------------------------------------
<Table>
<PropertySet OrderId="3434" DHLCode=""/>
<PropertySet OrderId="1212" DHLCode="A"/>
<PropertySet OrderId="" DHLCode="B"/>
</Table>
1 row selected.
|
|
|
|