 |
 |
| Index Search Add FAQ Ask Question |
|---|
$Date: 13-Jan-2004 $
$Revision: 1.04 $
$Author: Frank Naudé $
<?xml version="1.0"?>
<!DOCTYPE question-list SYSTEM "faq.dtd">
<?xml-stylesheet type="text/xml" href="faq.xsl"?>
<FAQ-LIST>
<QUESTION>
<QUERY>Question goes here</QUERY>
<RESPONSE>Answer goes here.</RESPONSE>
</QUESTION>
<QUESTION>
<QUERY>Another question goes here.</QUERY>
<RESPONSE>The answer goes here.</RESPONSE>
</QUESTION>
</FAQ-LIST>
<?xml version="1.0"?> <!ELEMENT faq-list (question+)> <!ELEMENT question (query, response*)> <!ELEMENT query (#PCDATA)> <!ELEMENT response (#PCDATA)>Notes:
<?xml version="1.0" encoding="UTF-8"?> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified"> <xs:element name="FAQ-LIST"> <xs:annotation> <xs:documentation>Example XML schema</xs:documentation> </xs:annotation> <xs:complexType> <xs:sequence maxOccurs="unbounded"> <xs:element name="QUESTION"> <xs:complexType> <xs:sequence> <xs:element name="QUERY"/> <xs:element name="RESPONSE"/> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:schema>Note: It is quite difficult to compile XML Schemas by hand. Best would be to get a tool like XMLSpy to help you getting the job done.
Install steps for older versions (Oracle 8i and below):
-- call sys.dbms_java.loadjava ('-v -r -grant PUBLIC xdk/lib/xmlparserv2.jar');
Run the above command manually from OS command line:
$ loadjava -v -r -grant PUBLIC $ORACLE_HOME/xdk/lib/xmlparserv2.jar
grant execute on xmldom to public;
grant execute on xmlparser to public;
create public synonym xmldom for sys.xmldom;
create public synonym xmlparser for sys.xmlparser;
Look at the following Oracle 9i code example:
connect scott/tiger
set serveroutput on
DECLARE
Ctx DBMS_XMLGEN.ctxHandle; -- Var's to convert SQL output to XML
xml clob;
emp_no NUMBER := 7369;
xmlc varchar2(4000); -- Var's required to convert lob to varchar
off integer := 1;
len integer := 4000;
BEGIN
Ctx := DBMS_XMLGEN.newContext('SELECT * FROM emp WHERE empno = '||emp_no);
DBMS_XMLGen.setRowsetTag(Ctx, 'EMP_TABLE');
DBMS_XMLGen.setRowTag(Ctx, 'EMP_ROW');
DBMS_XMLGEN.closeContext(Ctx);
xml := DBMS_XMLGEN.getXML(Ctx);
DBMS_LOB.READ(xml, len, off, xmlc); -- Display first part on screen
DBMS_OUTPUT.PUT_LINE(xmlc);
END;
/
The same results can be achieved using SQLX (see http://sqlx.org/).
Some of the SQLX functions are XMLElement(), XMLForest(), XMLSequence(), etc.
Look at this example.
set long 32000
SELECT XMLELEMENT("EMP_TABLE",
(select XMLELEMENT("EMP_ROW",
XMLFOREST(empno, ename, job, mgr, hiredate, sal, deptno)
)
from emp
where empno = 7369))
from dual;
An older Oracle 8i example:
connect scott/tiger
set serveroutput on
DECLARE
Ctx DBMS_XMLQuery.ctxType; -- Var's to convert SQL output to XML
xml clob;
emp_no NUMBER := 7369;
xmlc varchar2(4000); -- Var's required to convert lob to varchar
off integer := 1;
len integer := 4000;
BEGIN
Ctx := DBMS_XMLQuery.newContext('SELECT * FROM emp WHERE empno = :empno');
DBMS_XMLQuery.setBindValue(Ctx, 'empno', emp_no);
xml := DBMS_XMLQuery.getXML(Ctx);
DBMS_XMLQuery.closeContext(Ctx);
DBMS_LOB.READ(xml, len, off, xmlc); -- Display first part on screen
DBMS_OUTPUT.PUT_LINE(xmlc);
END;
/
connect scott/tiger
create table XMLTable (doc_id number, xml_data XMLType);
insert into XMLTable values (1,
XMLType('<FAQ-LIST>
<QUESTION>
<QUERY>Question 1</QUERY>
<RESPONSE>Answer goes here.</RESPONSE>
</QUESTION>
</FAQ-LIST>'));
select extractValue(xml_data, '/FAQ-LIST/QUESTION/RESPONSE') -- XPath expression
from XMLTable
where existsNode(xml_data, '/FAQ-LIST/QUESTION[QUERY="Question 1"]') = 1;
create index XMLTable_ind on XMLTable (extractValue(xml_data, '/FAQ-LIST/QUESTION/QUERY') );
|
|