Home » SQL & PL/SQL » SQL & PL/SQL » Xmlattribute not returning tag when the value is null (Oracle 19c)
Xmlattribute not returning tag when the value is null [message #689548] Fri, 09 February 2024 07:56 Go to next message
sss111ind
Messages: 632
Registered: April 2012
Location: India
Senior Member

Hi All,

How to get "DHLCode" as empty value(</DHLCode> ) instead of tag is missing when the value is null. Please help.


sELECT XMLElement("PropertySet",
                  XMLATTRIBUTES('3434' "OrderId",
                                 null "DHLCode")) 
                                   FROM DUAL  ;



Thanks

[Updated on: Fri, 09 February 2024 08:00]

Report message to a moderator

Re: Xmlattribute not returning tag when the value is null [message #689549 is a reply to message #689548] Fri, 09 February 2024 08:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68595
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> sELECT XMLElement("PropertySet",
  2                    XMLATTRIBUTES('3434' "OrderId",
  3                                   null "DHLCode"))
  4                                     FROM DUAL  ;
XMLELEMENT("PROPERTYSET",XMLATTRIBUTES('3434'"ORDERID",NULL"DHLCODE"))
-----------------------------------------------------------------------
<PropertySet OrderId="3434"></PropertySet>

This is the expected behavior as specified in the doc

A simple Google search will give you workarounds.

Re: Xmlattribute not returning tag when the value is null [message #689550 is a reply to message #689549] Fri, 09 February 2024 08:44 Go to previous messageGo to next message
sss111ind
Messages: 632
Registered: April 2012
Location: India
Senior Member

Thank you Michel,

Yes XMLATTRIBUTES does not return tag when the field value is null , but I want to generate the tag (DHLCode="" )as a attribute.

Please help if any workaround is there.
Re: Xmlattribute not returning tag when the value is null [message #689551 is a reply to message #689550] Fri, 09 February 2024 09:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68595
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just click on the links I gave!

Re: Xmlattribute not returning tag when the value is null [message #689552 is a reply to message #689548] Fri, 09 February 2024 13:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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.
Re: Xmlattribute not returning tag when the value is null [message #689562 is a reply to message #689552] Wed, 14 February 2024 03:08 Go to previous message
sss111ind
Messages: 632
Registered: April 2012
Location: India
Senior Member

Thank you Barbara Boehmer for detailed explanation.
Previous Topic: Creating Global vs Local Indexes for Partitioned Table
Next Topic: Read BLOB from Table into CLOB for further processing
Goto Forum:
  


Current Time: Wed Feb 21 02:24:23 CST 2024