I'm using Oracle 8.1.7 and the XDK 9.0.1.1.0A for PL/SQL

You can get the latest version of the XDK at OTN

http://otn.oracle.com/software/tech/xml/xdk_plsql/content.html

Schemas are a very important aspect of XML, especially when you are making interaction with a Database, they describe the xml file, and can be use to validate your xml file.

So it's very important in any scenario to be able to produce schemas, the DBMS_XMLQuery use two common Schemas the DTD and the XML Schema (XSD).

I think that something more could be done with the XSD, specially being able to retrieve a separate xsd, like it's possible with the DTD, maybe this is something that will be available in a near future.

So it's possible to get a xml file that includes a DTD or a XSD and also to retrieve a separated DTD.

To retrieve a xml file with included schema, you just have to pass a parameter to the getXML procedure, the second parameter (optional) will tell what schema will be produced.

The options are:  Null - no schema ; 1 - DTD ;  2 - XSD

CREATE OR REPLACE procedure tstXMLQuery_3_DTD is

queryCtx DBMS_XMLquery.ctxType;

result CLOB;

begin

queryCtx := DBMS_XMLQuery.newContext('select * from customer');

DBMS_XMLQuery.setrowsettag(queryctx,'customers');

DBMS_XMLQuery.setrowtag(queryctx,'customer');

--Setting the second parameter of getXML, will output also the Schema

result := DBMS_XMLQuery.getXML(queryCtx,DBMS_XMLQuery.DTD);

DBMS_XMLQuery.closeContext(queryCtx);

Clob2table(result);

end;

This produces this XML.

<?xml version = '1.0'?>

<!DOCTYPE customers [

<!ELEMENT customers (customer)*>

<!ELEMENT customer (CUSTID, NAME?, ADDRESS?, CITY?, STATE?, ZIP?, AREA?, PHONE?, REPID, CREDITLIMIT?, COMMENTS?, DATECUST?)>

<!ATTLIST customer num CDATA #REQUIRED>

<!ELEMENT CUSTID (#PCDATA)>

<!ELEMENT NAME (#PCDATA)>

<!ELEMENT ADDRESS (#PCDATA)>

<!ELEMENT CITY (#PCDATA)>

<!ELEMENT STATE (#PCDATA)>

<!ELEMENT ZIP (#PCDATA)>

<!ELEMENT AREA (#PCDATA)>

<!ELEMENT PHONE (#PCDATA)>

<!ELEMENT REPID (#PCDATA)>

<!ELEMENT CREDITLIMIT (#PCDATA)>

<!ELEMENT COMMENTS (#PCDATA)>

<!ELEMENT DATECUST (#PCDATA)>

]>

<customers>

<customer num=1>

<CUSTID>100</CUSTID>

<NAME>JOCKSPORTS</NAME>

<ADDRESS>345 VIEWRIDGE</ADDRESS>

<CITY>BELMONT</CITY>

<STATE>CA</STATE>

<ZIP>96711</ZIP>

<AREA>415</AREA>

<PHONE>598-6609</PHONE>

<REPID>7844</REPID>

<CREDITLIMIT>5000</CREDITLIMIT>

<DATECUST>12/11/2001 0:26:1</DATECUST>

</customer>

</customers>

To retrieve a separate DTD you must use the getDTD procedure:

CREATE OR REPLACE procedure TXQ_4_SeparatedDTD is

queryCtx DBMS_XMLquery.ctxType;

result CLOB;

dtd CLOB;

begin

queryCtx := DBMS_XMLQuery.newContext('select * from customer');

DBMS_XMLQuery.setrowsettag(queryctx,'customers');

DBMS_XMLQuery.setrowtag(queryctx,'customer');

result := DBMS_XMLQuery.getXML(queryCtx);

--If you need a separate file to hold the schema

dtd := DBMS_XMLQuery.getDTD(queryCtx,true);

DBMS_XMLQuery.closeContext(queryCtx);

Clob2table(result);

Clob2table(dtd);

end;

Sometimes I need to produce a separete xsd file so I've made a small stylesheet that just grabs the schema and this makes something  similar to the getDTD procedure (perhaps in future version of the XDK there will be a getXSD procedure).

CREATE OR REPLACE procedure getXSD is

queryCtx DBMS_XMLquery.ctxType;

result CLOB;

begin

queryCtx := DBMS_XMLQuery.newContext('select * from customer');

DBMS_XMLQuery.setrowsettag(queryctx,'customers');

DBMS_XMLQuery.setrowtag(queryctx,'customer');

DBMS_XMLQuery.setXSLT(queryCtx,'D:\ORATest\getXSD.xsl');

result := DBMS_XMLQuery.getXML(queryCtx,DBMS_XMLQuery.SCHEMA);

DBMS_XMLQuery.closeContext(queryCtx);

Clob2table(result);

end;

<xsl:stylesheet version=1.0 xmlns:xsl=http://www.w3.org/1999/XSL/Transform xmlns:xsd=http://www.w3.org/2000/10/XMLSchema>

<xsl:output method=xml omit-xml-declaration=no indent=yes/>

<xsl:template match=/>

<xsl:apply-templates select=DOCUMENT/xsd:schema/>

</xsl:template>

<xsl:template match=xsd:schema>

<xsl:copy-of select=./>

</xsl:template>

</xsl:stylesheet>

<?xml version = '1.0'?>

<xsd:schema xmlns:xsd=http://www.w3.org/2000/10/XMLSchema>

<xsd:element name=customers>

<xsd:complexType>

<xsd:sequence>

<xsd:element name=customer minOccurs=0 maxOccurs=unbounded>

<xsd:complexType>

<xsd:sequence>

<xsd:element name=CUSTID type=xsd:integer minOccurs=0/>

<xsd:element name=NAME type=xsd:string nullable=true minOccurs=0/>

<xsd:element name=ADDRESS type=xsd:string nullable=true minOccurs=0/>

<xsd:element name=CITY type=xsd:string nullable=true minOccurs=0/>

<xsd:element name=STATE type=xsd:string nullable=true minOccurs=0/>

<xsd:element name=ZIP type=xsd:string nullable=true minOccurs=0/>

<xsd:element name=AREA type=xsd:integer nullable=true minOccurs=0/>

<xsd:element name=PHONE type=xsd:string nullable=true minOccurs=0/>

<xsd:element name=REPID type=xsd:integer minOccurs=0/>

<xsd:element name=CREDITLIMIT type=xsd:double nullable=true minOccurs=0/>

<xsd:element name=COMMENTS type=xsd:string nullable=true minOccurs=0/>

<xsd:element name=DATECUST type=xsd:string nullable=true minOccurs=0/>

</xsd:sequence>

<xsd:attribute name=num type=xsd:integer/>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:schema>

Pedro Gil