XML creation using SQL/XML functions

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

XML creation using SQL/XML functions

Postby Balaji Krishnan » Tue Jun 26, 2012 3:16 pm

Hi Tim,

I need your help to solve the below problem which i have. I am using SQL/XML functions to create XML files from database tables. Below is the table structure with sample data and code snippet for creating XML CLOB. I need to exclude the element if it doesn't have a value for the column.

Code: Select all
create table test_table (key number(20), name varchar2(50), description varchar2(100));
insert into test_table (key, name, description) values (1,'sam','desc1');
insert into test_table (key, name, description) values (2,'max','desc2');
insert into test_table (key, name, description) values (3,'peter',null);
insert into test_table (key, name, description) values (4,'andrew',null);
commit;
select
      XMLROOT(
        xmlelement("Root",
         xmlagg(
           xmlelement("Cat",
           xmlelement("Name",name),           
           xmlelement("Desc",description)                 
           )
         )
       )).getClobVal()
    from test_table;

My desired xml output should not contain the element description for keys 3 & 4 as it description is null. Also please suggest if we use case statements here to exclude an element for certain conditions.
Balaji Krishnan
Member
 
Posts: 20
Joined: Fri Mar 30, 2012 3:03 pm

Re: XML creation using SQL/XML functions

Postby Tim... » Tue Jun 26, 2012 7:04 pm

Hi.

I would use XMLFOREST. It automatically gets rid of empty elements for you.

Code: Select all
SELECT XMLROOT(
         xmlelement("Root",
           XMLAGG(
             XMLELEMENT("Cat",
               XMLFOREST(
                 name AS "Name",
                 description AS "Desc"
               )
             )
           )
         )
       ).getClobVal()
FROM   test_table;

XMLROOT(XMLELEMENT("ROOT",XMLAGG(XMLELEMENT("CAT",XMLFOREST(NAMEAS"NAME",DESCRIP
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?><Root><Cat><Name>sam</Name><Desc>desc1</De
sc></Cat><Cat><Name>max</Name><Desc>desc2</Desc></Cat><Cat><Name>peter</Name></C
at><Cat><Name>andrew</Name></Cat></Root>


1 row selected.

SQL>


http://www.oracle-base.com/articles/mis ... ng-sql.php

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17952
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: XML creation using SQL/XML functions

Postby Balaji Krishnan » Wed Jun 27, 2012 5:45 am

Thanks Tim. But however i face a major issue now, my leads were suggesting to get rid of SQL/XML functions to get the XML CLOB as they are deprecated SQL functions from oracle standard and just used for backward compatibility.
Balaji Krishnan
Member
 
Posts: 20
Joined: Fri Mar 30, 2012 3:03 pm

Re: XML creation using SQL/XML functions

Postby Tim... » Wed Jun 27, 2012 6:55 am

Hi.

That is not true. Check out all the documentation links at the bottom of this article:

http://www.oracle-base.com/articles/mis ... ng-sql.php

Not one of these function called is listed as deprecated in the 11gR2 docs.

SQL/XML is a standard that is continuing to be developed, like all other open standards. The latest additions to the standard were in 2011. It is far from dead.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17952
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: XML creation using SQL/XML functions

Postby Balaji Krishnan » Wed Jun 27, 2012 7:31 am

Thanks for the information Tim, but i can see the XMLROOT is the only deprecated XML functions, which i should exclude to use.
SQL function XMLRoot was at one time part of the SQL/XML standard, but it is deprecated as a standard function as of SQL/XML 2005. It remains available in Oracle XML DB, as an Oracle function.


Also if remove XMLROOT from the above select statement, i get the entire XML in a single line. I need to get the XML CLOB in standard XML structure, as i use SPOOL command to get the XML from Oracle engine to client end (UNIX). Please let me know your thoughts.
Balaji Krishnan
Member
 
Posts: 20
Joined: Fri Mar 30, 2012 3:03 pm

Re: XML creation using SQL/XML functions

Postby Tim... » Wed Jun 27, 2012 11:11 am

Hi.

Yes. It is removed from the standard, but it will not be removed from Oracle's implementation, so it is safe to use it.

If you want to avoid it, read this:

http://docs.oracle.com/cd/B19306_01/app ... #sthref469

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17952
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: XML creation using SQL/XML functions

Postby Balaji Krishnan » Wed Jun 27, 2012 2:46 pm

Thank you for the support Tim. I will not be using XMLROOT as it is deprecated and will continue to use other functions.
Balaji Krishnan
Member
 
Posts: 20
Joined: Fri Mar 30, 2012 3:03 pm

Re: XML creation using SQL/XML functions

Postby Tim... » Wed Jun 27, 2012 3:37 pm

Hi.

Just to confirm, XMLROOT is not deprecated. There is no deprecation notice by Oracle and the XMLROOT function will continue to be supported indefinitely by Oracle.

The "XMLRoot" function of the "SQL/XML standard" is deprecated, but this has nothing to do with Oracle's implementation of SQL/XML. There are many situations where Oracle's implementation and support of a standard is not exact. There are no DB engine providers that truly support ANSI SQL for example. Not even Oracle.

By all means avoid XMLROOT is you want, but do so based on fact not fiction. Using the XMLROOT function in Oracle is fine. It will not disappear any time soon because it is not deprecated in Oracle's implementation of SQL/XML.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17952
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: XML creation using SQL/XML functions

Postby Balaji Krishnan » Thu Jun 28, 2012 5:57 am

Tim,

Thanks for the brief clarifications.
Balaji Krishnan
Member
 
Posts: 20
Joined: Fri Mar 30, 2012 3:03 pm

Re: XML creation using SQL/XML functions

Postby Tim... » Thu Jun 28, 2012 6:21 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17952
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 1 guest