Thanks for the question, Elie.

Asked: December 15, 2002 - 11:29 am UTC

Last updated: September 29, 2022 - 10:13 am UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

Hi, Tom.

I recently saw an answer you gave with respect to creating XML from a SQL query.

Here is the code that I copied from your answer.

==========================================================
create or replace package xml_handler_pack is
procedure xml_data( p_sql in varchar2,
p_max_rows in number );
end xml_handler_pack;
/

create or replace package body xml_handler_pack is

procedure print_xml(p_xml in out nocopy clob) is
l_xml varchar2(30000);
l_line varchar2(2000);
l_amt pls_integer := 30000;
l_idx pls_integer := 1;
begin
for i in 1 .. ceil(dbms_lob.getlength(p_xml)/l_amt) loop
l_xml := dbms_lob.substr(p_xml, l_amt, l_idx);
loop
exit when l_xml is null;
l_line := substr(l_xml, 1, instr(l_xml, chr(10))-1);
dbms_output.put_line('|' || l_line);
l_xml := substr(l_xml, instr(l_xml, chr(10))+1);
end loop;
end loop;
end print_xml;

procedure xml_data( p_sql in varchar2,
p_max_rows in number ) is
l_ctx dbms_xmlquery.ctxType;
l_xml clob;
begin
dbms_lob.createtemporary( l_xml, true, dbms_lob.session );
l_ctx := dbms_xmlquery.newContext( p_sql );

-- setup the context
dbms_xmlquery.setRaiseNoRowsException( l_ctx,true );
dbms_xmlquery.setMaxRows( l_ctx, p_max_rows );

-- get the xml fm the context
l_xml := dbms_xmlquery.getXML( l_ctx );

-- print out the xml result set
print_xml( l_xml );

-- determine the no of rows processed
dbms_output.put_line( '' );
dbms_output.put_line( 'No. of rows processed: ' ||
dbms_xmlquery.getnumrowsprocessed( l_ctx ) );

dbms_xmlquery.closeContext(l_ctx);
exception
when others then
dbms_xmlquery.closeContext(l_ctx);
end xml_data;

end xml_handler_pack;
/
==========================================================

I then ran this code as in the following:

set serveroutput on size 1000000

begin
xml_handler_pack.xml_data('&query', &num_rows_desired);
end;
/

This code displayed an XML listing of my SQL result set.

However, I've noticed that only non-null columns are being
displayed. Any columns that are NULL are not shown.

Here's an example of what I mean.

==========================================================
SQL> create table t (c1 number, c2 number);

SQL> Table created

SQL> insert into t values (1,1);
SQL> insert into t values (2,2);
SQL> insert into t values (3,3);
SQL> insert into t values (NULL,NULL);
SQL> insert into t values (5,NULL);
SQL> insert into t values (NULL,6);

SQL> commit;

SQL> select * from t;

C1 C2
---------- ----------
1 1
2 2
3 3

5
6

6 rows selected.

SQL> @run_xml_handler_pack
Enter value for query: select * from t
Enter value for num_rows_desired: 1000
|<?xml version = '1.0'?>
|<ROWSET>
| <ROW num="1">
| <C1>1</C1>
| <C2>1</C2>
| </ROW>
| <ROW num="2">
| <C1>2</C1>
| <C2>2</C2>
| </ROW>
| <ROW num="3">
| <C1>3</C1>
| <C2>3</C2>
| </ROW>
| <ROW num="4"/>
| <ROW num="5">
| <C1>5</C1>
| </ROW>
| <ROW num="6">
| <C2>6</C2>
| </ROW>
|</ROWSET>
No. of rows processed: 6

PL/SQL procedure successfully completed.
==========================================================

ROW num="4" shows no values at all because both columns in this row are NULL.
And ROW num="5" shows only the non-NULL C1 column whereas ROW num="6" shows
only the non-NULL C2 column.

1) Is there some way to have Oracle display the NULL columns as well as the non-NULL columns
in the generated XML listing?

2) Is there a way to re-name the titles "ROWSET" and "ROW" and "num=" in the XML listing?

3) I saw on your website how to create an ASCII file from this generated XML listing.
I have a need to automatically FTP such an XML file to some address. I realize
that I can do this via a Bourne-Shell script (on UNIX) but I was wondering if
Oracle is able to FTP this XML file directly?

Thank you in advance for your help.

and Tom said...

I asked Sean Dillon, our local XML Technologist, to take a look at this and here's what he had to say:
---------------------------

Most of what you are asking is INCREDIBLY easy to do. Not only can you do what you asked for but there are a variety of other features available to DBMS_XMLQuery, just check out Chapter 20 in the XML API Reference for Oracle9i.

1) Is there some way to have Oracle display the NULL columns as well as the non-NULL columns in the generated XML listing? Yes, there is, although the XDK places a boolean attribute inside the element that has the null value (i.e., <ROW num="4"><C1 NULL="TRUE"><C2 NULL="TRUE"></ROW>)

2) Is there a way to re-name the titles "ROWSET" and/or "ROW" and "num=" in the generated XML? Here's a modified example of your code from above:

SQL> create or replace package xml_handler_pack is
2 procedure xml_data( p_sql in varchar2,
3 p_max_rows in number,
4 p_num_val in varchar2 default 'num',
5 p_show_null_cols in boolean default false,
6 p_rowsettag in varchar2 default 'ROWSET',
7 p_rowtag in varchar2 default 'ROW' );
8 end xml_handler_pack;
9 /
Package created.

SQL> create or replace package body xml_handler_pack is
2
3 procedure print_xml(p_xml in out nocopy clob) is
4 l_xml varchar2(30000);
5 l_line varchar2(2000);
6 l_amt pls_integer := 30000;
7 l_idx pls_integer := 1;
8 begin
9 for i in 1 .. ceil(dbms_lob.getlength(p_xml)/l_amt) loop
10 l_xml := dbms_lob.substr(p_xml, l_amt, l_idx);
11 loop
12 exit when l_xml is null;
13 l_line := substr(l_xml, 1, instr(l_xml, chr(10))-1);
14 dbms_output.put_line('|' || l_line);
15 l_xml := substr(l_xml, instr(l_xml, chr(10))+1);
16 end loop;
17 end loop;
18 end print_xml;
19
20
21 procedure xml_data( p_sql in varchar2,
22 p_max_rows in number,
23 p_num_val in varchar2 default 'num',
24 p_show_null_cols in boolean default false,
25 p_rowsettag in varchar2 default 'ROWSET',
26 p_rowtag in varchar2 default 'ROW' ) is
27 l_ctx dbms_xmlquery.ctxType;
28 l_xml clob;
29 begin
30 dbms_lob.createtemporary( l_xml, true, dbms_lob.session );
31 l_ctx := dbms_xmlquery.newContext( p_sql );
32
33 -- setup the context
34 dbms_xmlquery.setRaiseNoRowsException( l_ctx,true );
35 dbms_xmlquery.setMaxRows( l_ctx, p_max_rows );
36 dbms_xmlquery.setRowsetTag( l_ctx, p_rowsettag );
37 dbms_xmlquery.setRowTag( l_ctx, p_rowtag );
38 dbms_xmlquery.useNullAttributeIndicator( l_ctx, p_show_null_cols );
39 if p_num_val is null then
40 dbms_xmlquery.useNullAttributeIndicator( l_ctx, p_show_null_cols );
41 elsif p_num_val = 'num' then
42 null;
43 else
44 dbms_xmlquery.setRowIdAttrName( l_ctx, p_num_val );
45 end if;
46
47 -- get the xml fm the context
48 l_xml := dbms_xmlquery.getXML( l_ctx );
49
50 -- print out the xml result set
51 print_xml( l_xml );
52
53 -- determine the no of rows processed
54 dbms_output.put_line( '' );
55 dbms_output.put_line( 'No. of rows processed: ' ||
56 dbms_xmlquery.getnumrowsprocessed( l_ctx ) );
57
58 dbms_xmlquery.closeContext(l_ctx);
59 exception
60 when others then
61 dbms_xmlquery.closeContext(l_ctx);
62 end xml_data;
63
64 end xml_handler_pack;
65 /
Package body created.

SQL> drop table t;
Table dropped.

SQL> create table t (c1 number, c2 number);
Table created.

SQL> insert into t values (1,1);
1 row created.

SQL> insert into t values (2,2);
1 row created.

SQL> insert into t values (3,3);
1 row created.

SQL> insert into t values (NULL,NULL);
1 row created.

SQL> insert into t values (5,NULL);
1 row created.

SQL> insert into t values (NULL,6);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from t;

C1 C2
---------- ----------
1 1
2 2
3 3

5
6
6 rows selected.

SQL> -- two examples, first your original then a new one w/ some of your requested functionality
SQL> begin
2 xml_handler_pack.xml_data( 'select * from t', 1000 );
3 xml_handler_pack.xml_data( p_sql => 'select * from t',
4 p_max_rows => 1000,
5 p_num_val => null,
6 p_show_null_cols => true,
7 p_rowsettag => 'VALUESET',
8 p_rowtag => 'VALUES' );
9 end;
10 /
|<?xml version = '1.0'?>
|<ROWSET>
| <ROW num="1">
| <C1>1</C1>
| <C2>1</C2>
| </ROW>
| <ROW num="2">
| <C1>2</C1>
| <C2>2</C2>
| </ROW>
| <ROW num="3">
| <C1>3</C1>
| <C2>3</C2>
| </ROW>
| <ROW num="4"/>
| <ROW num="5">
| <C1>5</C1>
| </ROW>
| <ROW num="6">
| <C2>6</C2>
| </ROW>
|</ROWSET>
No. of rows processed: 6
|<?xml version = '1.0'?>
|<VALUESET>
| <VALUES num="1">
| <C1>1</C1>
| <C2>1</C2>
| </VALUES>
| <VALUES num="2">
| <C1>2</C1>
| <C2>2</C2>
| </VALUES>
| <VALUES num="3">
| <C1>3</C1>
| <C2>3</C2>
| </VALUES>
| <VALUES num="4">
| <C1 NULL="TRUE"/>
| <C2 NULL="TRUE"/>
| </VALUES>
| <VALUES num="5">
| <C1>5</C1>
| <C2 NULL="TRUE"/>
| </VALUES>
| <VALUES num="6">
| <C1 NULL="TRUE"/>
| <C2>6</C2>
| </VALUES>
|</VALUESET>
No. of rows processed: 6

3) Can Oracle FTP files automatically? Absolutely, you can use a Java Stored Procedure to do this for you. Mark Piermarini, a teammate of mine, co-authored a book entitled "Oracle9i Java Programming" by Wrox Press Ltd. In this book, there's an example of FTP'ing from the database using a Java stored procedure. You could simply write a PL/SQL wrapper to this Java stored procedure, and then you can FTP any time you need to. You could also mix this with DBMS_JOB to automate the sending of files to another FTP site right out of the database with no user intervention whatsoever!

One other thing I wanted to mention, since you are generating XML from the database, and you are using Oracle9iR2, you can also use SQLX to generate instead of the XML SQL Utility (XSU). SQLX is an emerging standard which consists of SQL operators for generating XML from relational databases. The XSU will work just fine, but SQLX gives you more granular control of what is generated.

SQL> select xmlelement("ROWSET",
2 xmlagg(xmlelement("ROW",
3 xmlattributes(rownum as "num"),
4 xmlforest(c1, c2)))) as "RESULT"
5 from t;

RESULT
----------------------------------------------------------------------------------------------------
<ROWSET>
<ROW num="1">
<C1>1</C1>
<C2>1</C2>
</ROW>
<ROW num="2">
<C1>2</C1>
<C2>2</C2>
</ROW>
<ROW num="3">
<C1>3</C1>
<C2>3</C2>
</ROW>
<ROW num="4"/>
<ROW num="5">
<C1>5</C1>
</ROW>
<ROW num="6">
<C2>6</C2>
</ROW>
</ROWSET>

You can read more about SQLX and SQLX operators here: </code> http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.920/a96620/xdb12gen.htm#1027862


Hope that helps!

_smd_
_____________________________________________________________________
Sean Dillon - Principal Technologist, Oracle Corporation
Author "Beginning Oracle Programming"

http://www.amazon.com/exec/obidos/ASIN/186100690X <code>

Rating

(32 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

XML Questions

Elie Grunhaus, December 19, 2002 - 9:07 am UTC

Thanks so much. This response is the kind of platform I can jump off into learning more about how Oracle handles XML.

You and your merry helpers (Sean, in this case) are quite an asset to the Oracle community.

Few simple XML Doubts

A friend of you (Shall I?), March 30, 2003 - 4:46 am UTC

I know how to write an XML file an how to represent data.

My doubts are:

1. Why do we need XML. What is its advantage than a comma separated text file, if it defines the field names on top? An XML file do contain Junk field description with each datum. Right?

2. Where is the security for XML if everything is readable?

3. Where exactly is the XML used? What is its advantage over other database mechanisms? Is it only the simplicity of representation?

4. Even now, we are able to transfer data by various means. If so, where is the use of XML?

Thanks a bunch!

March 30, 2003 - 8:35 am UTC

1) represent for me a purchase order in a reasonable fashion that any program could at least parse and represent it on screen as you could with XML.

Variation on this question -- Why do we need HTML?

Now, going a step further with this purchase order which has a header record and many detail records -- it also has structured fields such as "ship to address" and "bill to address" that must conform to a set of rules (a schema or DTD). Flat files have no such "rules". Hence, given an XML input from a trading partner that is to conform to a certain DTD implies something about its content. You can in fact run a tool against the XML document to verify it conforms to the agreed upon standard interchange format.

And so on.

2) is it? or might encryption play a role?

3) I don't really see XML as a database mechanism, not any more then JPEG, Raster, Tiff, Video, etc. It is a data exchange format. We are all talking the same language. Look at common webservices implementations. What a great standard way to format a request (give inputs) and recieve a response.

4) it brings ordered structure to chaos. Why do we need HTML? Answer that and you have most of the answer for why XML.

I understood!

TH, March 30, 2003 - 9:36 am UTC

I understood! I understood!

I like the 3rd point in your answer.

So XML is not a Wonder or Magic. It's a requirement. Right?

March 30, 2003 - 10:00 am UTC

it is not a requirement -- it is a technical solution to a requirement.

requirement "easy exchange of data"
possible solution "XML"

Does Oracle use this technique?

TH, March 30, 2003 - 9:52 am UTC

Dear Tom,

Does Oracle use some mechanism to search an XML and replace the repeating Field Description with a rather small description(say id) and tie the id and description at the end of the XML - While transfering from one location to another? Won't this technique make the XML smaller in size while transfering?

This technique is only for transfering...

Example:

1 means <NAME>
2 means <ADDRESS1>
3 means <ADDRESS2>

like that?

A simple find and replace kind of stuff!

- Thaha Hussain

March 30, 2003 - 10:02 am UTC

No, the xml is stored "as is".

How do you represent Binary Data with XML

TH, April 01, 2003 - 9:50 am UTC

That's OK!

How do you represent Binary Data with XML? Or it is not supported in XML?

April 01, 2003 - 4:24 pm UTC

there is a way, there is a cdata tag but for many reasons -- it should be avoided. You can easily base64 encode the data -- that is what we would suggest.

pros and cons of xml generation types

YK from India, April 26, 2003 - 5:39 am UTC

Tom/Sean Dillon,

>since you are generating XML from the database, and you are using Oracle9iR2, you can also use SQLX to generate instead of the XML SQL Utility (XSU).

i see so many ways of generating xml from db,

Generating XML from the Database Using SQLX Functions
Generating XML from Oracle9i Database Using DBMS_XMLGEN
Generating XML Using Oracle-Provided SQL Functions
Generating XML Using XSQL Pages Publishing Framework
Generating XML Using XML SQL Utility (XSU)
.
.
.
.
since i am new to XML, i find it confusing to proceed with,..
1. can you give me the different ways of generating xml (high level view), if the above one is not correct.
2. which one i should choose for generating xml from 9iR2. pros and cons of each
3. which is the most flexible and scalable way of generating xml.

Thanks a lot

April 26, 2003 - 8:35 am UTC

have you read the application developers guide for XML supplied in the documentation set? It'll help you through these issues.

there is no single answer (or there would be but one API). It depends on your needs, your language, your application development environment.

just getting frustrated

George Liblick, December 11, 2003 - 6:18 pm UTC

Tom,

This XML stuff seems like it should be easy enough, but then I  can't even get your examples to work with my data :-(   I have reduced my problem to a simple example and ask, please, that you help me to understand what I'm doing wrong (I suspect I don't truly understand the XMLAGG function.)

I have the table:

SQL> select * from acct_feature_list
  2  /

   ACCT_NO FEATURE_NO FEATURE_IND
---------- ---------- -----------
      1107          1           1
      1107          2           0
      1107          3           1
      1107          7           0

and I need to generate an XML document that looks like:

XML_DOC
----------------------------------------------------
<account>
  <ariaacctno>1107</ariaacctno>
  <service>
    <serviceno>1</serviceno>
    <serviceprovind>1</serviceprovind>
  </service>
  <service>
    <serviceno>2</serviceno>
    <serviceprovind>0</serviceprovind>
  </service>
  <service>
    <serviceno>3</serviceno>
    <serviceprovind>1</serviceprovind>
  </service>
  <service>
    <serviceno>7</serviceno>
    <serviceprovind>0</serviceprovind>
  </service>
</account>


but the best I have been able to do (afer several days), is:

SQL> get latest
  1  select xmlelement( "account",
  2                             xmlforest( acct_no "acctno",
  3          xmlagg(c2) "service" )) XML_DOC
  4  from ( select acct_no,
  5         xmlforest( feature_no "serviceno", feature_ind "serviceprovind" ) c2
  6         from acct_feature_list ) src
  7* group by acct_no
SQL> /

XML_DOC
----------------------------------------------------
<account>
  <acctno>1107</acctno>
  <service>
    <serviceno>1</serviceno>
    <serviceprovind>1</serviceprovind>
    <serviceno>2</serviceno>
    <serviceprovind>0</serviceprovind>
    <serviceno>3</serviceno>
    <serviceprovind>1</serviceprovind>
    <serviceno>7</serviceno>
    <serviceprovind>0</serviceprovind>
  </service>
</account>


my database is 9.2

thanks for this site, thanks for your help.

George

December 13, 2003 - 10:18 am UTC

I'll ask Sean to take a peek at this as well, but my first thoughts were dbms_xmlgen (its the one I keep falling back to for simplicity :)

this is almost what you want -- and I'm sure using dbms_xmlgen, you can get it dead on

ops$tkyte@ORA920> select dbms_xmlgen.getxml
  2  ( 'select acct_no, cursor(select feature_no "serviceno", feature_ind "serviceprovind"
  3       from t t2
  4      where t2.acct_no = t1.acct_no) "service"
  5       from (select distinct acct_no from t) t1' )
  6    from dual;
 
DBMS_XMLGEN.GETXML('SELECTACCT_NO,CURSOR(SELECTFEATURE_NO"SERVICENO",FEATURE_IND
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <ACCT_NO>1107</ACCT_NO>
  <service>
   <service_ROW>
    <serviceno>1</serviceno>
    <serviceprovind>1</serviceprovind>
   </service_ROW>
   <service_ROW>
    <serviceno>2</serviceno>
    <serviceprovind>0</serviceprovind>
   </service_ROW>
   <service_ROW>
    <serviceno>3</serviceno>
    <serviceprovind>1</serviceprovind>
   </service_ROW>
   <service_ROW>
    <serviceno>7</serviceno>
    <serviceprovind>0</serviceprovind>
   </service_ROW>
  </service>
 </ROW>
</ROWSET>
 
 
ops$tkyte@ORA920>



------------------------------------------------------------------

Hi George, Sean Dillon here.

You were close.  Instead of burying the XMLAGG inside your element, I simply query it as a separate column and it gets placed into the resulting XML document following the <acct_no> element.  By breaking that out as a separate query (much the same way Tom did in his earlier example), we're able to query the <service> element's data separately, keying it back to the distinct acct_no outer query:

select xmlelement( "account" ,
         xmlforest( acct_no as "ariaacctno" ),
         ( select xmlagg( xmlelement( "service",
                            xmlforest( feature_no as "serviceno",
                                       feature_ind as "serviceprovind" )
                          )
                  )
             from t
            where t.acct_no = t2.acct_no
         )
       ) xmldoc
  from ( select distinct acct_no from t ) t2
/

...yields...

<account>
  <ariaacctno>1107</ariaacctno>
  <service>
    <serviceno>1</serviceno>
    <serviceprovind>1</serviceprovind>
  </service>
  <service>
    <serviceno>2</serviceno>
    <serviceprovind>0</serviceprovind>
  </service>
  <service>
    <serviceno>3</serviceno>
    <serviceprovind>1</serviceprovind>
  </service>
  <service>
    <serviceno>7</serviceno>
    <serviceprovind>0</serviceprovind>
  </service>
</account>

Plenty of examples and good information on XMLAGG can be found in the Database Developer's Guide - Oracle XML DB in Chapter 10 Generating XML Data from the Database (page 10-17 XMLAgg() Function).

Hope that helps!

ORDER BY

Justin, May 14, 2004 - 10:41 am UTC

Can you ORDER BY the results of one of the columns in an XMLAGG ?

SELECT XMLELEMENT("rs:data"
, XMLAGG( XMLELEMENT( "z:row"
, XMLATTRIBUTES ( t1.t043_id AS "T043_ID"
, t1.t067_id AS "T067_ID"
, t1.effective_dt AS "EFFECTIVE_DT"
, t1.t043_status_cd AS "T043_STATUS_CD"
, t1.imz_exemption_cd AS "IMZ_EXEMPTION_CD"
, t1.imz_assmnt_src_cd AS "IMZ_ASSMNT_SRC_CD"
, t1.shot_card_seen_in AS "SHOT_CARD_SEEN_IN"
, t1.shot_card_read_in AS "SHOT_CARD_READ_IN"
, t1.referral_in AS "REFERRAL_IN"
, t1.shots_complete_in AS "SHOTS_COMPLETE_IN"
, t1.education_in AS "EDUCATION_IN"
, t1.bring_shot_record_in AS "BRING_SHOT_RECORD_IN"
, t1.permission_to_share_in AS "PERMISSION_TO_SHARE_IN"
, newid() AS "Id"))))
FROM t043_imz_enctr t1
WHERE t1.t067_id IN (12132)
ORDER BY EFFECTIVE_DT DESC;

<<< RESULT >>>

<rs:data><z:row T043_ID="11158" T067_ID="12132" EFFECTIVE_DT="04/30/2001" T043_STATUS_CD="A" IMZ_ASSMNT_SRC_CD="I" SHOT_CARD_SEEN_IN="N" SHOT_CARD_READ_IN="N" REFERRAL_IN="N" SHOTS_COMPLETE_IN="Y" EDUCATION_IN="N" BRING_SHOT_RECORD_IN="N" Id="FC66C64B02974A13BCE3EE6CA91CE7E7"></z:row><z:row T043_ID="11159" T067_ID="12132" EFFECTIVE_DT="10/08/2001" T043_STATUS_CD="A" IMZ_ASSMNT_SRC_CD="S" SHOT_CARD_SEEN_IN="Y" SHOT_CARD_READ_IN="Y" REFERRAL_IN="N" SHOTS_COMPLETE_IN="Y" EDUCATION_IN="N" BRING_SHOT_RECORD_IN="N" Id="954CD49785E44A7A90FD354C280F3B6E"></z:row><z:row T043_ID="789845" T067_ID="12132" EFFECTIVE_DT="05/09/2002" T043_STATUS_CD="T" IMZ_ASSMNT_SRC_CD="I" SHOT_CARD_SEEN_IN="N" SHOT_CARD_READ_IN="N" REFERRAL_IN="Y" SHOTS_COMPLETE_IN="N" EDUCATION_IN="N" BRING_SHOT_RECORD_IN="N" Id="8839DCCE65464D9AA52D38C3C67FD8E6"></z:row><z:row T043_ID="1146617" T067_ID="12132" EFFECTIVE_DT="11/04/2002" T043_STATUS_CD="T" IMZ_ASSMNT_SRC_CD="S" SHOT_CARD_SEEN_IN="Y" SHOT_CARD_READ_IN="Y" REFERRAL_IN="Y" SHOTS_COMPLETE_IN="N" EDUCATION_IN="Y" BRING_SHOT_RECORD_IN="N" PERMISSION_TO_SHARE_IN="N" Id="8532462E798B4DA1B88CF73643F2B77C"></z:row></rs:data>

I'm not sure what I should use to get the dates to return highest first.

PS. I know this select is wacko, but lets just say I live in a wacko world at work.

Thanks so much for your time!

May 15, 2004 - 10:37 am UTC

order by orders rows. If you have more than one row, that'll order them.

data within that row -- an order by of that row won't work.

Aggregates -- they do not "sort", they work on sets, unordered sets. You would have to write your own user define aggregate (your own xmlagg) in order to "sort".

Thanks very much!

Justin, May 17, 2004 - 8:07 am UTC

what about pagination with SQLX?

Ilya, May 18, 2004 - 6:12 pm UTC

This is all extremely useful. I have a question: what is the best way to paginate the results when using SQLX? With dbms_xmlgen (or with XMLQuery using XSU) I can specify how many rows I want returned at a time and save them into separate files, but is there an easy way to do it with a sql query using SQLX?

Also, a separate question: is there an easy way to rename elements after xml is generated? That is, if I cannot alias column names inside a query, but want to do it later using a stylesheet? Are there any examples for that?

thanks,
ilya

May 19, 2004 - 7:40 am UTC

sorry, never used sqlx....

ditto with a stylesheet but at least I can say the answer is "yes, you can do that with a stylesheet as that is just a mini program to read and reformat data"

transform and paginate.

bob, July 29, 2004 - 3:10 pm UTC

Iyla,

Try google for simple xml/xsl questions:
</code> http://www.xml.com/pub/a/2000/06/07/transforming/ <code>

simple example of how to change an element name.

For paging via sqlx, how is it any different than paging via sql? they both are simple queries, sqlx just wraps columns in xml functions. the where clause is unaffected.

WEB service

Branka, August 20, 2004 - 9:57 am UTC

I want to make web service in PL/sql to accept XML, parse the XML into data elements, call SPs to process that data and to return XML.
Last part will be with SQLX. I do not want to store XML in database.
Database is Oracle 9.2
Is this posible to make in pl/sql?

August 21, 2004 - 10:21 am UTC

if you want to expose a plsql procedure as a web service, jdeveloper has a wizard to do so, it'll automate the entire process.

XMLTransform problem

vll, August 25, 2004 - 11:15 am UTC

Tom, great thanks for all you are doing for us!
Question: Oracle 9.2.0.3. Query below returns ORA-03113. Am I doing something wrong?

select XMLTransform
( XMLTYPE( '<tables/>')
, XMLTYPE( '<xsl:stylesheet version="1.0" xmlns:xsl="</code> http://www.w3.org/1999/XSL/Transform" ><xsl:template <code>match="tables">test</xsl:template></xsl:stylesheet>')
).getstringval()
from dual

August 25, 2004 - 11:36 am UTC

3113 = please contact support.

XMLTransform problem

vll, August 25, 2004 - 11:19 am UTC

Wasn't posted correctly - trying to add spaces...
select XMLTransform
( XMLType( '<tables/>')
, XMLTYPE( '<xsl:stylesheet version="1.0" xmlns:xsl=" h t t p : / / www.w3.org/1999/XSL/Transform" >< xsl:template match="tables">test</xsl:template></xsl:stylesheet>')
).getstringval()
from dual

Some XML questions

A reader, September 14, 2004 - 9:31 am UTC

Tom,
I have some simple XML questions. I tried the XML guides but they are far too advanced for this simple situation.
a) What is XML data type and how can I use XML data type just for storage and retrieval of XML data? The XML data will be generated by other applications for storage and will be processed by other applications after retrieval.
b) Is XML data type available by default in a database or is there a setup required?

Thanks

September 14, 2004 - 9:36 am UTC

the docs were WRITTEN EXPRESSELY TO ANSWER this question.

Everything XML:
</code> http://www.oracle.com/pls/db92/db92.docindex?remark=homepage#index-XML


answer to #1

http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96620/xdb01int.htm#1047171 <code>

and #2 is upto your dba when they created the database -- they either UNchecked the box or left it on.

Some XML questions

A reader, September 14, 2004 - 10:09 am UTC

Tom,
Thanks for the quick response. Actually I am a DBA and not familiar with XML at all. The application team feels that they have a right to NOT read any documentation and throw everything that has the string "Oracle" in it at the DBA.

IF INSTR(their_thoughts,'oracle')>0 THEN
problem_goes_to_DBA;
END IF;

I had gone through the Application Developer's Guide, PL/SQL Guide, XML SDK Guide but looks like I missed the XML Developer's Guide which answers my questions, particularly chapter 4. I have one more question:
a) Can I use XML data type without installing XML DB? I don't think XML DB is installed on this database. Since I did not install this database, is there a way to check ?

Thanks again for your help and patience.

September 14, 2004 - 11:12 am UTC

SQL> desc sys.xmltype

if that works, it is there.

format-number() problem

A. Semenov, November 03, 2004 - 1:53 pm UTC

Hi Tom,

Is the XSLT format-number() function supported by XMLType.transform() ???

I am trying to use the XSLT format-number function in a stylesheet via myXML.transform(myXSL) and get ORA-03113 end-of-file on communication channel error.

For example, <xsl:value-of select="format-number($myNum,'#,###.00')/>.

Is format-number supported by XML DB?

I am running 9.2.0.5.

Thanx.

November 05, 2004 - 10:52 am UTC

ora-(3113, 7445, 600) means "please contact support, something bad happened that should not have"

Vijayakumar, November 11, 2004 - 8:14 am UTC

Excellant!!

Spaces, Nulls and XML

Jon, February 09, 2005 - 6:20 am UTC

Excellent site Tom.
I'm generating xml from a table, then eventually extracting the information back to table format. Thanks to your site I now have everything I need using dbms_xmlgen.
I have hit an issue when the db column contains a single space (' '). The space is reflected in the xml but when it is extracted again the value is null. Is there a way around this? Any help appreciated.
Many thanks,
Jon

create table xmltest (id number, xmlpayload xmltype);

insert into xmltest(id, xmlpayload)
values(1, xmltype(
'<?xml version="1.0"?>
<ROWSET>
<ROW>
<FORENAME> </FORENAME>
<SURNAME>kelly</SURNAME>
</ROW>
</ROWSET>'));

select nvl(FORENAME, '**NULL**'), SURNAME from (
SELECT
extract(value(d),'/ROW/FORENAME/text()').getStringVal() FORENAME,
extract(value(d),'/ROW/SURNAME/text()').getStringVal() SURNAME
FROM xmltest
t,TABLE(XMLSequence(t.xmlpayload.extract('ROWSET/ROW'))) d);

NVL(FORENAME,'**NULL**')
---------------------------
SURNAME
---------------------------
**NULL**
kelly

March 04, 2005 - 9:42 am UTC

I asked Sean Dillon to take a look at this, here's what he had to say:

--

Hi Jon,

This is a whitespace issue.  You'll have to use a parser that can deal with whitespace.  When you query the CLOB value out, you'll see it but once you use EXTRACT the document is functionally evaluated and the whitespace is consumed:

SQL> insert into xmltest(id, xmlpayload)
  2  values(1, xmltype(
  3  '<?xml version="1.0"?>
  4  <ROWSET>
  5   <ROW>
  6    <FORENAME> </FORENAME>
  7    <SURNAME>kelly</SURNAME>
  8   </ROW>
  9  </ROWSET>'))
 10  /
1 row created.

SQL> select x.xmlpayload.getClobVal() from xmltest x
  2  /
X.XMLPAYLOAD.GETCLOBVAL()
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <FORENAME> </FORENAME>
  <SURNAME>kelly</SURNAME>
 </ROW>
</ROWSET>

SQL> select nvl(x.xmlpayload.extract('/ROWSET/ROW/FORENAME/text()').getStringVal(),'*NULL*') forename
  2    from xmltest x
  3  /

FORENAME
----------------------------------------------------------------------------------------------------
*NULL*

_smd_

XML encryption- SEcuriing XML content

Suhail, February 20, 2005 - 9:51 pm UTC

Tom,

Is there a way to encrypt the XML, We need to exchange XML data over the internet, what is the best possible way to make it secure?

Could you please shed me some light on this issue.

Thanks in advance.

February 21, 2005 - 10:02 am UTC

well, inside the database there is dbms_obfuscation_toolkit or dbms_crypto in 10g. you can certainly encrypt it.

but most people would use ssl perhaps as the transport mechanism. ssl is an encrypted protocol (or even just ssh tunneling) if you just want it encrypted on the 'wire'

(or a vpn -- virtual private network)

Need help resolving this XML tree structure.

R, April 20, 2005 - 10:29 am UTC

Hi Tom / Sean,

I am trying to achieve the following XML tree structure (sub headers inside another sub header) and I am not able to finish it. We use Oracle 8i (8.1.7) and using "dbms_xmlquery". Could you please help me resolve this? Also Is there a dynamic way (same query help) of adding XML sub columns/headers.

<?xml version="1.0" encoding="UTF-8"?>
<file xsi:noNamespaceSchemaLocation="</code> http://xml.mysite.com/order_schema.xsd"

xmlns:xsi="

http://www.w3.org/2001/XMLSchema-instance" > <code>
<order>
<customer_order_number>123</customer_order_number>
<order_header>
<merchant_id>1</merchant_id>
<merchant_name>Name1</merchant_name>
<merchant_address1>123 My Street</merchant_address1>
<merchant_address2></merchant_address2>
<merchant_city>My City</merchant_city>
<merchant_state>MA</merchant_state>
<merchant_zip>80231</merchant_zip>
<merchant_country>USA</merchant_country>
<merchant_phone>508-111-1111</merchant_phone>
<order_type>S</order_type>
<order_date></order_date>
<customer_id>01</customer_id>
<rep_id>REP1</rep_id>
<merchant_email_address></merchant_email_address>
<secondary_address>
<ship_name>Name1</ship_name>
<ship_address1>345 My Ship Street</ship_address1>
<ship_address2></ship_address2>
<ship_city>Ship City</ship_city>
<ship_state>LA</ship_state>
<ship_zip>70211</ship_zip>
<ship_country>USA</ship_country>
<ship_phone>781-123-1234</ship_phone>
</secondary_address>
</order_header>
<order_billing>
<line_item_total>0.00</line_item_total>
<order_total>0.00</order_total>
</order_billing>
</order_user_defined>
<line_item>
<line_item_number>1</line_item_number>
<part_id>A-1234</part_id>
<quantity>1</quantity>
<ship_method>PN</ship_method>
<method_of_payment>1</method_of_payment>
<line_item_billing>
<extended_price>0.00</extended_price>
<item_price>0.00</item_price>
</line_item_billing>
</line_item>
<line_item>
<line_item_number>3</line_item_number>
<part_id>CB-321-00</part_id>
<quantity>1</quantity>
<ship_method>PN</ship_method>
<method_of_payment>0</method_of_payment>
<line_item_billing>
<extended_price>0.00</extended_price>
<item_price>0.00</item_price>
</line_item_billing>
</line_item>
</order>
</file>

Thanks a lot in advance.

April 25, 2005 - 10:23 am UTC

Hi R Sean here,

What you're trying to do is very easy to do using SQLXML w/ 9iR2 or greater versions of the database. Using DBMS_XMLQUERY, you *might* be able to get this format but it's gonna be a stretch. You'd have to create an object view that mirrors your data format (i.e. the XML nesting structure). Then you'll have to query your data such that it populates those objects and pass that query to DBMS_XMLQUERY for it to create THIS particular output. What I've found it this is terribly difficult to do, and in general you are going to end up getting something close to your desired output, then having to use XSLT to transform the results of the DBMS_XMLQUERY to get rid of row/rowset tags, etc.

I know upgrades aren't something trivial, but you should put this into that "reasons an upgrade would be good" bucket. Ultimately, if you can't generate this w/ DBMS_XMLQUERY natively, try using XSLT to transform what you DO get into what you need. After that, I might look into string concatenation or using DOM until I can get some SQLXML support (again, 9iR2+)

Hope that helps,

_smd_

How to retain whitespace

A reader, June 21, 2005 - 6:27 pm UTC

Question to Tom/Sean,

Two posts above Sean mentioned that we need to use a parser that can deal with whitespace. I'm wondering if you can provide an example of a parser that keeps whitespace intact instead of showing null while extracting the document.

Thanks.

June 21, 2005 - 9:09 pm UTC

you'd have to supply the parser was Sean's point I believe.

Whitespace issue

A reader, June 22, 2005 - 11:48 am UTC

Being a newbie, it's really hard for me to visualize how the parser would look like. Any examples?

June 23, 2005 - 1:26 pm UTC

you would be finding some *other* parser is the point...

Nice

Raj, July 10, 2005 - 1:33 pm UTC

Hello Tom,
How to load an XML file into Oracle database??

July 10, 2005 - 1:43 pm UTC

</code> https://docs.oracle.com#index-XML <code>
lots of ways.

Sub headers inside another subheader

Reed, January 26, 2006 - 10:44 am UTC

I am just learning how to get XML out of the database and have enjoyed the learning process. I have read some of the Oracle documentation on XML but have not yet come across an example that will get me where I need to be.

I noticed a review on this thread on April 20, 2005 about an XML tree structure. The poster was using Oracle 8i. The structure was similar to what I am trying to get. Sean mentioned it would be easy to do using SQLXML in 9iR2. Can you provide an example of how this could be done with Oracle 9iR2?

This will help with one of my issues. I am still reviewing the documentation to see if I can get around my other road blocks. It's great to learn new things.

Thanks

Found Sub headers solution

Reed, January 26, 2006 - 4:06 pm UTC

WooHoo! I found out how to get the subheaders working. Right after I posted the review I saw something that got me started in the right direction. It also resolved a couple of other hurdles I was trying to get over.

Now the issue I have is getting the xml output to display in a nicely indented format instead of all on one line. Any clues there?

Thanks

January 27, 2006 - 8:20 am UTC

open it in xmlspy ;)

is it xml, just text, you want it formatted with tabs/spaces/whatever - use a viewing tool.

A reader, April 20, 2006 - 10:42 am UTC

Tom,

I am generating XML using SQLX function. It's working fine for me. I want to add

<?xml version="1.0" encoding="UTF-8"?>

in the headder. Is there any function in oracle to add this on the header.

We are using oracle 9i r2

Thanks

Loop thru XML elements

Rahul, September 05, 2006 - 3:34 pm UTC

Hi Tom,
I found your example very useful. I have a question regarding looping thru similar named elements in an XML. Below is an example -
<CreditInformation
<Credit_agency_ind>Y</Credit_agency_ind>
<OUtside_Source_IND>N</OUtside_Source_IND>
<DenialReasons>
<DenialType_cd>10012</DenialType_cd>
<DenialReason_OTHR_TXT/>
</DenialReasons>
<DenialReasons>
<DenialType_cd>60008</DenialType_cd>
<DenialReason_OTHR_TXT>Some Reason</DenialReason_OTHR_TXT>
</DenialReasons>
<LOAN_APPLCTN_TS>2006-04-20T10:08:48</LOAN_APPLCTN_TS>
</CreditInformation>
I want to parse the element DenialReasons. This element has the possibility to be repeated upto 4 times with the 2 child elements being constant. My question is how would you loop thru to extract the values out. Would you count the number of times, the element repeats first and then loop thru? But I'm stuck at just doing that.. needed to check with you on that.
Thanks,

Generate XML from SQL which return millions of record

Mak, October 16, 2010 - 2:30 pm UTC

Hi TOM,
I want to generate the xml file for the huge data(4500000 records). The structure of XML like childs inside child and with multiple values. The TSQL statement is returning the data bu grouping by few parent field and generate child fields based on master.

I am trying to use SQLX to generate the XML but it taking long long time to generate the XML output.
Is there any other way for generating XML for such huge data returning from TSQL statement?

Thanks in advance,
Mak

October 25, 2010 - 7:39 am UTC

one question for you.

why, why would you want to in any reasonable world generate an XML document from 4.5 million records? While there are undoubtedly slower ways to share data - that would rank among the top three I would guess (using XML for this would rank in the top three - it might even BE the top three)

Query about XMLTYPE column structured storage in Oracle Xml db

A reader, August 29, 2013 - 1:37 pm UTC

I have an table having one column as XMLTYPE with Structured storage.

    CREATE TABLE Orders
    (
    Order_id NUMBER NOT NULL,
    Order_etc VARCHAR2(100),
    Order_desc XMLType NOT NULL
    )
    XMLTYPE Order_desc STORE AS OBJECT RELATIONAL XMLSCHEMA  "http://localhost/public/xsd/order_desc_xsd.xsd" ELEMENT "OrderState";

I have then registered the XSD with XML Db schema which is required for Structured storage.

Before this table creation I had created a table (db_objects) of XMLTYPE and was able to use the below query to check for what all objects the XMLTYPE table got broken into when I registered its XSD.

    SELECT column_name,      
           data_type
    FROM   user_tab_cols
    WHERE  table_name = 'DB_OBJECTS';

And used below query to look for data stored in Object-Relational structure for my table (DB_OBJECTS) created with XMLTYPE definition.

    SELECT EXTRACTVALUE(xseq.column_value, '/THISROW/OWNER')       AS owner
    ,      EXTRACTVALUE(xseq.column_value, '/THISROW/OBJECT_NAME') AS object_name
    ,      EXTRACTVALUE(xseq.column_value, '/THISROW/OBJECT_TYPE') AS object_type
    ,      EXTRACTVALUE(xseq.column_value, '/THISROW/OBJECT_ID')   AS object_id
    ,      EXTRACTVALUE(xseq.column_value, '/THISROW/CREATED')     AS created
    FROM   db_objects do
     ,      TABLE(XMLSEQUENCE(EXTRACT(VALUE(do), '/ROWSET/THISROW'))) xseq  
    WHERE  ROWNUM <= 10;

Could you guide me how to find how the column (Order_desc) of XMLTYPE was broken down into further objects just like I did for the Table with XMLTYPE (as shown above). I mean which tables to query in XML Db to find about how this column got shredded by XML DB?

Many Thanks.

questions about XMLQUERY function calls.

Rajeshwaran, Jeyabal, August 30, 2022 - 7:42 am UTC

Team,

Here is my sample xml documents

demo@PDB1> create table demo_xml of xmltype ;

Table created.

demo@PDB1> insert into demo_xml values('<PurchaseOrder
  2    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  3    xsi:noNamespaceSchemaLocation=
  4      "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd">
  5    <Reference>SBELL-2002100912333601PDT</Reference>
  6    <Actions>
  7      <Action>
  8        <User>SVOLLMAN</User>
  9      </Action>
 10    </Actions>
 11    <Reject/>
 12    <Requestor>Sarah J. Bell</Requestor>
 13    <User>SBELL</User>
 14    <CostCenter>S30</CostCenter>
 15    <ShippingInstructions>
 16      <name>Sarah J. Bell</name>
 17      <address>400 Oracle Parkway
 18        Redwood Shores
 19        CA
 20        94065
 21        USA</address>
 22      <telephone>650 506 7400</telephone>
 23    </ShippingInstructions>
 24    <SpecialInstructions>Air Mail</SpecialInstructions>
 25    <LineItems>
 26      <LineItem ItemNumber="1">
 27        <Description>A Night to Remember</Description>
 28        <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
 29      </LineItem>
 30      <LineItem ItemNumber="2">
 31        <Description>The Unbearable Lightness Of Being</Description>
 32        <Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
 33      </LineItem>
 34      <LineItem ItemNumber="3">
 35        <Description>Sisters</Description>
 36        <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
 37      </LineItem>
 38    </LineItems>
 39  </PurchaseOrder>');

1 row created.

demo@PDB1> commit;

Commit complete.

and when i apply the filter on the part/@Id predicate it works, but not on the LineItem/@ItemNumber.

can you help us to understand what was missing in the below query block?

demo@PDB1> select xmlcast( xmlquery( '$x/PurchaseOrder/LineItems/LineItem[Part/@Id="37429140222"]/Description/text()'
  2      passing object_value as "x" returning content ) as varchar2(80)) col
  3  from demo_xml;

COL
--------------------------------------------------------------------------------
The Unbearable Lightness Of Being

demo@PDB1> select xmlcast( xmlquery( '$x/PurchaseOrder/LineItems[LineItem/@ItemNumber="2"]/Description/text()'
  2      passing object_value as "x" returning content ) as varchar2(80)) col
  3  from demo_xml;

COL
--------------------------------------------------------------------------------


demo@PDB1>

questions about XMLQUERY function calls.

Rajeshwaran, Jeyabal, September 29, 2022 - 5:02 am UTC

when i modified the above query to get all the linelines and apply filter predicates to a specific "ItemNumber" it works.

demo@PDB1> select xmlcast( xmlquery( '$x/PurchaseOrder/LineItems/LineItem[@ItemNumber="2"]/Description/text()'
  2     passing object_value as "x" returning content ) as varchar2(80)) col
  3  from demo_xml;

COL
--------------------------------------------------------------------------------
The Unbearable Lightness Of Being

1 row selected.

September 29, 2022 - 10:13 am UTC

Glad you figured it out

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

XML Questions

Few simple XML Doubts

I understood!

Does Oracle use this technique?

How do you represent Binary Data with XML

pros and cons of xml generation types

just getting frustrated

ORDER BY

Thanks very much!

what about pagination with SQLX?

transform and paginate.

WEB service

XMLTransform problem

XMLTransform problem

Some XML questions

Some XML questions

format-number() problem

Spaces, Nulls and XML

XML encryption- SEcuriing XML content

Need help resolving this XML tree structure.

How to retain whitespace

Whitespace issue

Nice

Sub headers inside another subheader

Found Sub headers solution

Loop thru XML elements

Generate XML from SQL which return millions of record

Query about XMLTYPE column structured storage in Oracle Xml db

questions about XMLQUERY function calls.

questions about XMLQUERY function calls.

More to Explore

CLOB/BLOB/etc