I recently saw a demo of XDB - I guess there are two names still going around? One thing I picked up from the demo was that an FTP interface is supported, so you can upload and download documents with that too.

Hello Tom,

I need to rollup the sum in the xml output. Can this be achieved using sys_xmlgen as a single query? If not is there any other way to achieve that? Can you please explain with an example. I am using Oracle 8.1.7

Senario
--------

Sno employee_id branch_id salary
1 10 10 100
2 11 10 100
3 12 11 50
4 13 11 50
5 14 12 200

Reqires XML format
------------------

Employee_id branch_id salary
10 10 100
11 10 100
-----
200
12 11 50
13 11 50
-----
100
14 12 200

Thank you

Would that Sean Dillon?

I don't know why I care about these things, but for some reason it seems nicer when you know little things about the people on the other end of the wire...

- Mark

Hi Tom,
   I wanted to insert an XML data in XMLTYPE table.
The content of my xml file is
    <Employee>
        <Name>Shailesh</Name>
        <Age>23</Age>
    </Employee>
Shown below are the steps that I followed as per oracle documentation. However I get error.
Please advise.

SQL> conn system@vks2_ora9i2
Connected.
SQL> create directory XMLDIR as 'D:\oracle\ora92\utl_file_dir\';

Directory created.

SQL> grant read on directory xmldir to public with grant option;

Grant succeeded.

SQL> conn scott@vks2_ora9i2
Connected.
SQL> create table xmltable of xmltype;

Table created.

SQL> create or replace function get_Document(filename varchar2) return clob is
  2     xbfile bfile;
  3     xclob clob;
  4  begin
  5    xbfile  := bfilename('XMLDIR',filename);
  6    dbms_lob.open(xbfile);
  7  
  8    dbms_lob.createtemporary(xclob,TRUE,dbms_lob.session);
  9    dbms_lob.loadfromfile(xclob,xbfile, dbms_lob.getlength(xbfile));
 10    dbms_lob.close(xbfile);
 11    return xclob;
 12  end;
 13  /

Function created.

SQL> INSERT INTO XMLTABLE
  2  VALUES(XMLTYPE(get_Document('Shailesh.xml')));
VALUES(XMLTYPE(get_Document('Shailesh.xml')))
       *
ERROR at line 2:
ORA-31011: XML parsing failed 
ORA-19202: Error occurred in XML processing 
LPX-00210: expected '<' instead of '<' 
Error at line 1 
ORA-06512: at "SYS.XMLTYPE", line 0 
ORA-06512: at line 1 


SQL> spool off


P.S :-  If I insert the XML in following way it does not give me any error.
    INSERT INTO XMLTABLE
    VALUES(XMLTYPE('<Employee><Name>Shailesh</Name><Age>23</Age></Employee>'));
    But this is not what I want. I want to read an XML file and then insert it in XMLTABLE. 

ops$tkyte@ORA920.US.ORACLE.COM> host cat tkyte.xml
    <Employee>
        <Name>Shailesh</Name>
        <Age>23</Age>
    </Employee>

ops$tkyte@ORA920.US.ORACLE.COM> 
ops$tkyte@ORA920.US.ORACLE.COM> create or replace directory xmldir as '/export/home/tkyte/'
  2  /

Directory created.

ops$tkyte@ORA920.US.ORACLE.COM> 
ops$tkyte@ORA920.US.ORACLE.COM> drop table xmltable;

Table dropped.

ops$tkyte@ORA920.US.ORACLE.COM> create table xmltable of xmltype;

Table created.

ops$tkyte@ORA920.US.ORACLE.COM> 
ops$tkyte@ORA920.US.ORACLE.COM> create or replace function get_Document(filename varchar2) return clob is
  2     xbfile bfile;
  3     xclob clob;
  4  begin
  5    xbfile  := bfilename('XMLDIR',filename);
  6    dbms_lob.open(xbfile);
  7  
  8    dbms_lob.createtemporary(xclob,TRUE,dbms_lob.session);
  9    dbms_lob.loadfromfile(xclob,xbfile, dbms_lob.getlength(xbfile));
 10    dbms_lob.close(xbfile);
 11    return xclob;
 12  end;
 13  /

Function created.

ops$tkyte@ORA920.US.ORACLE.COM> 
ops$tkyte@ORA920.US.ORACLE.COM> insert into xmltable values ( xmltype( get_document( 'tkyte.xml' ) ) );

1 row created.

ops$tkyte@ORA920.US.ORACLE.COM> select * from xmltable;

SYS_NC_ROWINFO$
-----------------------------------------------------------------------------------------------------------------------------------
<Employee>
  <Name>Shailesh</Name>
  <Age>23</Age>
</Employee>


ops$tkyte@ORA920.US.ORACLE.COM>


It worked for me -- Are your sure the file has what you think in it?  Also, this could be character set related -- if you have a UTF8 instance on windows -- it might be (and in that case, contact support and reference in progress bug #2535898) 

Tom,

Can you able to do the exprot of this XDB schema? I am trying in serveral ways and I could not get it done all the time the error comes is exp-00010 xdb is not a valid user.

Can you tell me what kind of export do we need on this to get the things to pass to another database.

Hi Tom,
I recently installed Oracle 9iR2 (Standard Sdition). When I read Oracle docmentation and other examples, there are many packages related to XML, such as, DBMS_XDB, DBMS_XMLSchema, DBMS_XDB_VERSION, DBMS_XMLDOM,DBMS_XMLGEN, DBMS_XMLQUERY etc. Howevery, in my database, I can only see DBMS_XMLGEN. Oracle documentation says XML DB is installed while installation. Should I do manual installation or my first installation went wrong? Other features seem to be fine.
Thank you for your help.

Hi Tom,

Regarding the bug: #2535898 as you mentioned above, if I install XDK 9.2.0.6, will it resolve the bug automatically?

for example, if I use that get_document() to convert a non-utf-8 xml file into a clob and print it, it shows a lot of reverse question mark, if I install XDK 9.2.0.6, Does this problem will go away automatically?

Please advise. Thanks for help.

Richard

Hi Tom,

Thank you very much your advice, please be more specific about your comment: "not set up NLS stuff correctly", the database has existed for seven years, how do I correct it?

Thanks for help.

Richard

Hi Tom,

Following my last response. Does "not set up NLS stuff correctly" mean database parameter set up or when using XSLT and dbms_xmlsave package? Please be a bit more specific.

Thanks.

Richard

Hi Tom,

Thanks for all the great work you are doing for all of us. This question is related to XML Guru Sean.

I am trying to re-install the XMLDB in the Oracle database.

I followed the steps given in the documentation.
i executed
catqm.sql <pwd> <ts_name> <ts_temp>

But this script fails in between and i see that it has disconnected from the database and there are lots of Not connected to Oracle error.

I opened the catqm.sql and tried to execute each script inside it.
and i found that the problem occurs in the script catxdbz.sql.

The first pl/sql code within the file executed but the second one to register the schema did not work and says table does not exist .
error in XDB.DBMS_XMLSCHEMA_INT and XDB_DBMS_XMLSCHEMA.

so can you please help me out with this installation.

A reply in this will be greatly appreciated.

Hi Tom,
We are having issues with updating a resource in XMLDB which was initially created as a BLOB. We can successfully create a new resource using dbms_xdb.CreateResource, but could not find a way to update it later with new contents.

BEGIN
dbms_xdb.DeleteResource(abspath => '/pic.jpg',
delete_option => dbms_xdb.DELETE_RESOURCE);
END;
/

DECLARE
result RAW(16);
BEGIN
/*
Here, we are able to create a new binary resource
*/
IF (dbms_xdb.CreateResource(abspath => '/pic.jpg',
data => bfilename(directory => 'XDB_DIR',
filename => 'pic.jpg')) =
FALSE)
THEN
dbms_output.put_line('CreateResource failed!');
ROLLBACK;
ELSE
result := dbms_xdb_version.makeversioned(pathname => '/pic.jpg');
COMMIT;
END IF;
END;
/
DECLARE
result RAW(16);
BEGIN
dbms_xdb_version.checkout(pathname => '/pic.jpg');
/*
Here is the issue. we do not know how to
update this resource with new contents.
resource_view.res is of type XMLType, and
we need to update the contents from a BLOB
*/
UPDATE resource_view r
SET r.res = ???
WHERE r.any_path = '/pic.jpg';
result := dbms_xdb_version.checkin(pathname => '/pic.jpg');
END;
/

Hi Tom,

with XMDB I have a comfortable FTP-Access into the Database, so I could store other document types (PDF, Word etc...) in Oracle via FTP (it's like the old IFS)

Or should I use the XMLDB only with XML-files?
What do you think about it?

Bye,

Jens

Hi Sean,

that was exactly what I was looking for! Oracle should do more marketing there...

Bye,

Jens

I have stored some HTML files in XDB using WEBDAV.

They are stored as binaries since they are non-XML.

Now I want to access these HTML files and do some work on them using DBMS_LOB.

Problem is I can't figure out any way to get the data out of the XDB Resource and into a CLOB. I can't get rid of the XML wrapper and the various conversions (ie. all < became &lt;)

How do you do this?

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

Heya Mike,

When you store data in the repository via WebDAV, Oracle is going to parse the document and convert any special characters such as brackets, etc.  That's why you're getting the &lt; instead of < when you query the data back.  You'll need to convert these programmatically if you want to access the data through RESOURCE_VIEW or PATH_VIEW.  If you access the data via a HTTPUri, however, you'll get the data just as you would if you query it through WebDAV.

As an example...

-----
SQL> declare
 2    l_file clob := '<html><body>TEST</body></html>';
 3    l_bool boolean;
 4  begin
      -- (uncomment after first run)
 5    -- dbms_xdb.deleteresource('/home/TEST/test.html');
 6    -- dbms_xdb.deleteresource('/home/TEST');
 7    l_bool := dbms_xdb.createfolder('/home/TEST');
 8    l_bool := dbms_xdb.createresource('/home/TEST/test.html', l_f
 9  end;
10  /
PL/SQL procedure successfully completed.

SQL> select res
 2    from resource_view
 3   where any_path = '/home/TEST/test.html'
 4  /

RES
--------------------------------------------------------------------
<Resource xmlns="

 <CreationDate>2005-01-29T21:04:03.539000</CreationDate>
 <ModificationDate>2005-01-29T21:04:03.539000</ModificationDate>
 <DisplayName>test.html</DisplayName>
 <Language>en-US</Language>
 <CharacterSet>WINDOWS-1252</CharacterSet>
 <ContentType>text/html</ContentType>
 <RefCount>1</RefCount>
</Resource>

SQL> select p.res.extract('/Resource/Contents/text/text()') content
 2    from resource_view p
 3   where any_path = '/home/TEST/test.html'
 4  /

CONTENT
--------------------------------------------------------------------
&lt;html&gt;&lt;body&gt;TEST&lt;/body&gt;&lt;/html&gt; 

On the other hand, if you use an HTTPUri to access the data, you are retrieving it through a web server and it will be formatted appropriately.  For example:

SQL> declare
  2    l_httpuri httpuritype;
  3  begin
  4    l_httpuri := httpuritype('

;
  5    dbms_output.put_line(substr(l_httpuri.getClob(),1,200));
  6  end;
  7  /
<html><body>TEST</body></html>

I hope that helps!

_smd_ 

xdburitype(template_path || template_name || '.htm').getClob()

We want to use XDB but we want the user name and password for the WEBDAV connection to be from an application, not the DB.

Is that possible? If so how?

We have a Forms application.

Rather than have users enter a DB username and password they enter a username and password that are managed by our application which programmatically grants them access to the system.

Now we want to integrate with XDB and WEBDAV so they can manage documents in the DB from Windows Exlporer.

But I can't find any way to programmatically intercept the user names and passwords that they enter in the Windows interface and tell XDB to authenticate or not authenticate the user.

I am trying to figure out if there is any way to do this, maybe using Global DB users with single sign on - there isn't much in the Oracle docs on this kind of thing.

</code> http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96620/xdb14dbu.htm#1656 <code>

Just ran into this! This is amazing.

Any table/view in my database is accessible to users using just a web browser and with the ability to apply filters i.e. select * from something where ....?!

It even talks about browsing Oracle database using Windows Explorer as if it were a network share and download/upload documents!

I didnt know all this existed in 9iR2, Oracle should really market all this stuff better!

Thanks

OK suppose I use

</code> http://server:8080/oradb/SCOTT/EMP <code>

my browser dumps the raw XML in the window.

How can I get the output in a more user-friendly format i.e. as a HTML table with headers, grids, etc?

Or is this getting too far away from Oracle and into web development land?

Thanks

<blockquote>ahh, the foibles of doing things in the application tier.. perfect.

you see, since you have injected your application in between the end user and
the data, you have made it so that the ONLY way to access your data is -- well
-- via your application.

Happens every single time your application takes over "security" or "access
control", your application becomes the only only only way to access the data.</blockquote>

Tom, I generally agree with your distaste for this approach, but at current time it still seems to be necessary:

1. The database does not provide any secure method I can find to allow trusted applications to access it but prevent untrusted applications. We have business rules that are enforced in the application layer so I'm not willing to let users in with SQL*Plus, TOAD, or any other random client.
2. The database provides no way to let multiple users in different user groups have the same name. When we run multiple applications (sometimes for different companies) on the same database we want users to be able to reuse names between companies. You would be amazed how unreasonable a user in an ASP environment gets when you tell him "You can't pick the username 'CEO' because someone in another company that shares the same database that you are also running on picked that name a year ago."
3. Tied in with point above, this also makes it brutally hard to migrate a company to another database for load management, etc. If even one user name is being used in both DBs you are out of luck.

I'll also point out that we are not the only people who have looked at these issues and chosen to go outside the database. Please look at Oracle's own Applications Division. Admittedly, Jeff Walker fostered a sometimes un-necessarily "NIH / Go it alone" attitude, but still - they don't use DB authentication.

I'm very open to using the DB / App Server to solve this - I would rather use a DB facility than build my own. I'm trying to figure out if we can use Global Users mapped to common DB Accounts but right now I can's see how this would work.

I was already aware of proxy authentication.

But as far as I can tell, it can't be used with Forms. Our application uses Forms.

Also, there doesn't address the issues I raised like allowing different companies that share the same DB to issue the same user names to their staff. I suppose we could use this to authenticate to the middle tier and then have Forms separately log into the DB but then we would be back to our original problem - how to create XDB accounts that map to middle tier users.

I was wondering if the embedded listener for XMLDB can be configured to execute mod_plsql?

Can you clarify the information in
</code> http://www.oracle.com/technology/tech/xml/xmldb/Current/TWP_Mastering_XMLDB_Repository.pdf <code>

Page 12 describes 'Embedded PL/SQL Gateway'....

'the embedded PL/SQL gateway - a gateway implementation that runs in the Oracle XML DB HTTP listener.'

Any examples of how to configure the xdbconfig.xml file to support this if it is available?

Hello Tom,
I can create table of xmltype, column of xmltype, and use SQL XML functions, PL/SQL APIs without having XDB repository installed, is it right?

Could you tell me what XDB repository is used for? If I need to use XDB features, do I have to install XDB repository(under xdb schema)?

Sincerely,
Frank

Hi Tom,
Thanks for the reply. That's very helpful. So If I want to take advantage of XDB repository and build an application on top of that, I am wondering how scalable the repository itself will be.

An analogy is, HTML DB is great tool to write some quick and small applications, but I may have to use other tools for bigger systems.

Similarly, do you have any comment on what type and scale of applications are suitable to use XDB repository?
Sincerely,

Hi Tom,

  I am sorry if this seems to be a new query. This is with regard to loading the XML document using XMLDB. The following are the steps done:

CREATE OR REPLACE DIRECTORY DATA_DIR AS '/tmp/'

CREATE TABLE XMLTABLE OF XMLType;

grant read on directory data_dir to public with grant option;

CREATE OR REPLACE function getClobDocument(
filename in varchar2,
 charset in varchar2 default NULL)
 return CLOB deterministic
 is
    file            bfile := bfilename('DATA_DIR',filename);
    charContent     CLOB := ' ';
    targetFile      bfile;
    lang_ctx        number := DBMS_LOB.default_lang_ctx;
    charset_id      number := 0;
    src_offset      number := 1 ;
    dst_offset      number := 1 ;
    warning         number;
 begin
   if charset is not null then
       charset_id := NLS_CHARSET_ID(charset);
   end if;
   targetFile := file;
   DBMS_LOB.fileopen(targetFile, DBMS_LOB.file_readonly);
   DBMS_LOB.LOADCLOBFROMFILE(charContent, targetFile,
           DBMS_LOB.getLength(targetFile), src_offset, dst_offset,
           charset_id, lang_ctx,warning);
   DBMS_LOB.fileclose(targetFile);
   return charContent;
 end;
/

 The character set on the database is UTF8 and the file is also in UTF8.

  Now to insert the file and its contents:

SQL> INSERT INTO XMLTABLE
  2  VALUES(XMLTYPE(getCLOBDocument('ExtractXML.xml','UTF8')))
  3  /
VALUES(XMLTYPE(getCLOBDocument('ExtractXML.xml','UTF8')))
       *
ERROR at line 2:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00210: expected '<' instead of 'ÿ'
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 0
ORA-06512: at line 1

SQL> select * from v$version ;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for Linux: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

  Could you please help me out? Thanks in advance. 

Hi Tom,

Sorry for not giving the file contents. Herewith I have given the contents of the XML Document.

<?xml version="1.0" encoding="utf-8" ?>
<EXPORT><RECORD><Type>I</Type>

V060697

<FolderN>97</FolderN><Density></Density><Template>Ingredient</Template><Owner>MOFFASUE</Owner><Status>I:DEV</Status><DefWaste></DefWaste><NOTES><NOTEITEM><NoteCode>COMM</NoteCode><NoteText>
<DESCRIPTIONS><DESCITEM><DescCode>ORIG FOLDER</DescCode><Descr>KLN Production > EU Food Common</Descr></DESCITEM><DESCITEM><DescCode>EUROITEM</DescCode></RECORD></EXPORT>

Thanks.

Tom,
I am currently preparing for 1Z0-032(DBA fundamentals - II ) exam. Today while looking at my local database server setup i realized that eventhough it is a dedicated server it has got a dispatcher running to handle requests for XML DB. I wanted to know where can i get a detailed look at Oracle's Architecture that includes all the components , even those that are optional. Books generally discuss about main components present in the database and just ignore the others. I even looked up the XML DB documentation but couldn't find anything useful.

Thanks,
Khalid

I wanted to use xquery(not sql) to query my data,which uses FLWOR concepts.I heard it's only available in 10gr2.I tried in this release ,but i wasn't able to make it work.I didn't find any example in your site .I will appreciate if you could give me one example of how to do following

suppose I have one xml file in d:\x.xml

1)How do I see the complete contents of this xml
I said following

sql > xquery for $i in doc("d:\x.xml") return $i

it returned this..

No items selected

2)how do I use group by and sum together along with order by like salary for each department and display the results in desc order of salary.

3)How do I use max function etc.

All of this should be achieved by using xquery, not sql.
Any documentation will helpful as well..along with your example.

cheers

Tom,
Our developers are exploring XML in Oracle and being from non-Oracle background, have asked me this question. Me being a DBA do not know much about XML. I hope you do not find this question stupid. I just know how to install and configure XML DB but do not know the entire capabilities of XML. I believe these are second to none. My question is specific to 10gr2.

Is XML DB a must if I want to use XML features in Oracle? What can I do with XML in Oracle if XML DB is not installed? Can you please point me to Oracle documentation which details what can be done without XML DB? Just the name of document will be enough.

Thanks

Hi all,

i am working Oracle XMLDB in Oracle 10g R1. I created XML Schema file and their table but i am not able to modify(means add column delete column,add constraint)
please help me.