What is XML Schema

What is XML Schema: A brief introduction-
----------------------------------------
An XML schema defines the structure of an XML document by setting constraints on the data types of elements and attributes in the document. XML documents can be validated against the XML schema to ensure they conform to the constraints defined in the schema. There are several schema languages that define the grammar for an XML document, for instance, Document-Type Definition (DTD) language, and XML Schema language. Of these languages, because of its extensive support for data types and namespaces, XML Schema is becoming the most widely used schema language;


XML documents are increasingly being used as a format of data exchange. But for an XML document to be acceptable to different developers/users, the XML document should conform to a standard structure. XML Schema is an XML-based representation of the structure of an XML document. Through its support for datatypes and namespaces, XML Schema has the potential to provide the standard structure for XML elements and attributes. However, to check if an XML document conforms to an XML Schema, the document must be validated against that XML Schema.

Three procedure for performing that validation using parsers for the

1.Simple API for XML (SAX)
2.Document Object Model (DOM)
3.An XML Schema Design (XSD) validator.

Cursor in query and xml

create table table_1 (countryid varchar2(10),country varchar2(20));

create table table_2 (cityid varchar2(10),city varchar2(20),countryid varchar2(10));


select cityid,cursor(city,countryid from table_2)
from table_2


select DBMS_XMLquery.GetXML('SELECT COUNTRYID, COUNTRY,
CURSOR (SELECT CITYID, CITY
FROM TABLE_2 T1
WHERE T1.COUNTRYID = T2.COUNTRYID) AS CITYNAME
FROM TABLE_1 T2',2) dd from dual;


SELECT countryid, country,
(SELECT cityid
FROM table_2 t1
AS cityname
FROM table_1 t2


select cityid,cursor(city,countryid
from table_2) as c
from table_2


exec Get_Xml_Schema('SELECT COUNTRYID, COUNTRY,
CURSOR (SELECT CITYID, CITY
FROM TABLE_2 T1
WHERE T1.COUNTRYID = T2.COUNTRYID) AS CITYNAME
FROM TABLE_1 T2')


select DBMS_XMLQuery.GetXML('select * from t1',2) from dual;

xml read xpath

create or replace procedure tamjid_xml_read_2 is
x XMLTYPE := XMLTYPE('



12
String

2
2
2
2


2
2
2
2

1967-08-13
14:20:00.0Z
String
1967-08-13
14:20:00.0Z
14:20:00.0Z
2
String
String
String



1967-08-13
1967-08-13
14:20:00.0Z
String
String
String
String
String
String



2

2
2
2
2


2
2
2
2

1967-08-13
1967-08-13
2
2

2
2
2
2

String


1967-08-13
14:20:00.0Z
14:20:00.0Z
String
String

2
2
2
2

2
2
2
2
String
String
2
2
String
String
1
1
String


1

2
2
2
2

**** VALID TEXT REQUIRED ****
123456789123456
aaaaaaaaaaaaaaaaaa

2
2
2
2

aaaaaaaaaaaaaaaaaa
Y
2
0
1


12

2
2
2
2

**** VALID TEXT REQUIRED ****
123456789123456
Y
2
0
12
aaaaaaaaaaaaaaa


1

2
2
2
2

**** VALID TEXT REQUIRED****
0
0
0
0
0
0
0
1
0
0
true
aaaaaaaa



2
2
2
2

**** VALID TEXT REQUIRED****
aa
123456789123456
aaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaa

0
1234
1234
1234
1234

1234
127
1234
a
1
127


0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
aaaaaaaaaaaaaaaaaaaa


2
2
2
2
String



2
2
2
2
2
String



2
2
2
2
String
String


');
begin
FOR i IN ( ( select EXTRACTVALUE(VALUE(t),'/EHR/StandardLevel') b_1,
EXTRACTVALUE(VALUE(t),'/EHR/TestFileInd') b_2,
EXTRACTVALUE(VALUE(t),'/EHR/ECreateDate') b_3,
EXTRACTVALUE(VALUE(t),'/EHR/ECreateTime') b_4,
EXTRACTVALUE(VALUE(t),'/EHR/ResendInd') b_5,
EXTRACTVALUE(VALUE(t),'/EHR/ESettlementDate') b_6,
EXTRACTVALUE(VALUE(t),'/EHR/ESessionTime') b_7,
EXTRACTVALUE(VALUE(t),'/EHR/ESettlementTime') b_8,
EXTRACTVALUE(VALUE(t),'/EHR/EType') b_9,
EXTRACTVALUE(VALUE(t),'/EHR/FileIDMod') b_10,
EXTRACTVALUE(VALUE(t),'/EHR/CountryCode') b_11,
EXTRACTVALUE(VALUE(t),'/EHR/EUsrFld') b_12,
EXTRACTVALUE(VALUE(t),'/EHR/DestRT/BankCode') b_13 ----type
from TABLE(XMLSEQUENCE(EXTRACT(x,'/BACPSInterface/OCE/EHR'))) t)
)

LOOP
dbms_output.put_line('B_1 = ' i.b_1 ' bankCode ' i.b_13);
END LOOP;

END;

How can i took xml field data form clob clumn directly

i want to insert data in a table directly from clob data type.I have a long xml document.I am useing this way because i want tokeep save the xml in my database table first.


SQL> CREATE TABLE XML_LOAD_IN
2 (XML_CFILE CLOB
3 );

Table created.

SQL>
SQL> INSERT INTO XML_LOAD_IN ( XML_CFILE)
2 VALUES (
3 '
4 1
5 2
6 1500
7 2
8 Bank Asia
9 20090714
10
11 ');

1 row created.

SQL>
SQL> CREATE TABLE CCR_IN
2 (
3 “BundleCount” NUMBER(6) NOT NULL,
4 “ItemWithinCashLetterCount” NUMBER(8) NOT NULL,
5 “CashLetterTotalAmount” NUMBER(14) NOT NULL,
6 “ImagesWithinCashLetterCount” NUMBER(9),
7 “ECEInstitutionName” VARCHAR2(18 BYTE),
8 “SettlementDate” VARCHAR2(8 BYTE)
9 )
10 /

Table created.


CREATE OR REPLACE PROCEDURE mic.dpr_insert_from_xml_ccr IS
l_bfile BFILE;
l_clob CLOB;
l_parser DBMS_XMLPARSER.parser;
l_doc DBMS_XMLDOM.domdocument;
l_nl DBMS_XMLDOM.domnodelist;
l_n DBMS_XMLDOM.domnode;

TYPE tab_type IS TABLE OF ccr_in%ROWTYPE;

t_tab tab_type := tab_type ();
BEGIN
BEGIN
SELECT xml_cfile
INTO l_clob
FROM xml_load_in;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error (-2001, 'Inward XML File Not Found.');
WHEN OTHERS THEN
RAISE;
END;

l_parser := DBMS_XMLPARSER.newparser;
DBMS_XMLPARSER.parseclob (l_parser, l_clob);
l_doc := DBMS_XMLPARSER.getdocument (l_parser);
DBMS_XMLPARSER.freeparser (l_parser);
l_nl :=
DBMS_XSLPROCESSOR.selectnodes (DBMS_XMLDOM.makenode (l_doc), '/CCR');

---FOR ECR
FOR cur_ccr IN 0 .. DBMS_XMLDOM.getlength (l_nl) - 1 LOOP
l_n := DBMS_XMLDOM.item (l_nl, cur_ccr);
t_tab.EXTEND;
--- Use XPATH syntax to assign values to he elements of the collection.
DBMS_XSLPROCESSOR.valueof (l_n,
'BundleCount/text()',
t_tab (t_tab.LAST)."BundleCount"
);
DBMS_XSLPROCESSOR.valueof (l_n,
'ItemWithinCashLetterCount/text()',
t_tab (t_tab.LAST)."ItemWithinCashLetterCount"
);
DBMS_XSLPROCESSOR.valueof (l_n,
'CashLetterTotalAmount/text()',
t_tab (t_tab.LAST)."CashLetterTotalAmount"
);
DBMS_XSLPROCESSOR.valueof
(l_n,
'ImagesWithinCashLetterCount/text()',
t_tab (t_tab.LAST)."ImagesWithinCashLetterCount"
);
DBMS_XSLPROCESSOR.valueof (l_n,
'ECEInstitutionName/text()',
t_tab (t_tab.LAST)."ECEInstitutionName"
);
DBMS_XSLPROCESSOR.valueof (l_n,
'SettlementDate/text()',
t_tab (t_tab.LAST)."SettlementDate"
);
END LOOP;

FOR cur_ccr IN t_tab.FIRST .. t_tab.LAST LOOP
INSERT INTO ccr_in
("BundleCount",
"ItemWithinCashLetterCount",
"CashLetterTotalAmount",
"ImagesWithinCashLetterCount",
"ECEInstitutionName",
"SettlementDate"
)
VALUES (t_tab (cur_ccr)."BundleCount",
t_tab (cur_ccr)."ItemWithinCashLetterCount",
t_tab (cur_ccr)."CashLetterTotalAmount",
t_tab (cur_ccr)."ImagesWithinCashLetterCount",
t_tab (cur_ccr)."ECEInstitutionName",
t_tab (cur_ccr)."SettlementDate"
);
END LOOP;

COMMIT;
DBMS_XMLDOM.freedocument (l_doc);
EXCEPTION
WHEN OTHERS THEN
DBMS_XMLPARSER.freeparser (l_parser);
DBMS_XMLDOM.freedocument (l_doc);
END;
/

Procedure created.

SQL>
SQL> select * From xml_load_in;

XML_CFILE
——————————————————————————–

1
2
SQL>
SQL> exec Dpr_Insert_From_Xml_CCR;

PL/SQL procedure successfully completed.

SQL>
SQL> select * from ccr_in;

BundleCount ItemWithinCashLetterCount CashLetterTotalAmount
———– ————————- ———————
ImagesWithinCashLetterCount ECEInstitutionName Settleme
————————— —————— ——–
1 2 1500
2 20090714

SQL>

dbms_xmlgen

dbms_xmlgen.newContextFromHierarchy(queryString IN VARCHAR2)
RETURN ctxHandle;

set serveroutput on

DECLARE
qryctx dbmx_xmlgen.ctxhandle;
result XMLTYPE;

PROCEDURE lob_output (p_clob CLOB) IS l_clob CLOB;
l_clob_length NUMBER;
l_iterations NUMBER;
l_chunk VARCHAR2(32767);
l_chunk_length NUMBER := 32767;
BEGIN
l_clob := p_clob;
l_clob_length := dbms_lob.getlength(l_clob);
l_iterations := CEIL(l_clob_length / l_chunk_length);

FOR i IN 0 .. l_iterations - 1 LOOP
l_chunk := dbms_lob.substr(l_clob,l_chunk_length,i*l_chunk_length+1);
dbms_output.put_line(l_chunk);
END LOOP;
END;
BEGIN
qryctx := dbms_xmlgen.newcontextFromHierarchy('SELECT level,
XMLElement("Position", XMLElement("Name", ename), XMLElement("Title",
job)) FROM emp CONNECT BY PRIOR empno = mgr START WITH mgr is NULL');

result := dbms_xmlgen.getxmltype(qryctx);

dbms_xmlgen.closeContext(qryctx);

lob_output(RESULT.getClobVal());
END;
/

nested table for hierarchycal xml

CREATE OR REPLACE TYPE test1_type AS OBJECT
(
SOURCE_ID NUMBER(10),
ENTITY_ID_CODE VARCHAR2(3 Byte)
)

CREATE OR REPLACE TYPE test1_type_tbl
AS TABLE OF test1_type;

CREATE OR REPLACE TYPE test2_type AS OBJECT
(
RECEIVER_ID NUMBER(10),
SOURCE_ID NUMBER(10)
)

CREATE OR REPLACE TYPE test2_type_tbl
AS TABLE OF test2_type;

CREATE OR REPLACE TYPE wrapper_type AS OBJECT
(
test_nested_input1 test1_type_tbl,
test_nested_input2 test2_type_tbl
)

CREATE OR REPLACE TYPE wrapper_type_tbl
AS TABLE OF wrapper_type;

create table REALMED_CORP_NESTED_TABLE
(
id INTEGER Primary Key,
Company_Name VarChar2(20),
wrapper_rec wrapper_type_tbl
)
NESTED TABLE wrapper_rec STORE AS nested_wrapper_rec
(NESTED TABLE test_nested_input1 STORE AS nested_Input1_tab
NESTED TABLE test_nested_input2 STORE AS nested_Input2_tab);

insert into REALMED_CORP_NESTED_TABLE
values
(001,
'c12',
wrapper_type_tbl(
wrapper_type(
test1_type_tbl(test1_type(12,'t')),
test2_type_tbl(test2_type(12,12))
))
);

commit

select DBMS_XMLquery.GetXML('select * from REALMED_CORP_NESTED_TABLE') from dual

cursor in query and generate hierarchical xml

cursor in query and generate hierarchical xml
================================================


CREATE TABLE table_1 (countryid VARCHAR2(10),country VARCHAR2(20));


INSERT INTO table_1
VALUES ('100', 'Bangladesh');

INSERT INTO table_1
VALUES ('200', 'India');

INSERT INTO table_1
VALUES ('300', 'Pakistan');

COMMIT ;


CREATE TABLE table_2 (cityid VARCHAR2(10),city VARCHAR2(20),countryid VARCHAR2(10));


INSERT INTO table_2
VALUES ('10', 'Dhaka', '100');

INSERT INTO table_2
VALUES ('20', 'Sylhet', '100');

INSERT INTO table_2
VALUES ('30', 'Rajshahi', '100');


INSERT INTO table_2
VALUES ('10', 'Kolkata', '200');

INSERT INTO table_2
VALUES ('20', 'Mumbai', '200');

INSERT INTO table_2
VALUES ('30', 'Rajastan', '200');


COMMIT ;

----Query

SELECT countryid, country,
CURSOR (SELECT cityid, city
FROM table_2 t1
WHERE t1.countryid = t2.countryid) AS cityname
FROM table_1 t2


-----generate an xml file with hierarchical

select DBMS_XMLquery.GetXML('SELECT COUNTRYID, COUNTRY,
CURSOR (SELECT CITYID, CITY
FROM TABLE_2 T1
WHERE T1.COUNTRYID = T2.COUNTRYID) AS CITYNAME
FROM TABLE_1 T2',2) dd from dual;

HOW TO GENERATE xml FROM ORACLE TABLE AND SAVE TO HARD DRIVE

HOW TO GENERATE xml FROM ORACLE TABLE AND SAVE TO HARD DRIVE
--------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE BEFTN.dpr_generate_FHR_6 ( p_filename in varchar2
default 'FHR',
p_dir in varchar2
default 'MY_XML'
) IS

v_OutFile utl_file.file_type;
v_value varchar2(2000);
v_QryCtx DBMS_XMLQuery.ctxType;
v_Xml CLOB;
v_More BOOLEAN := TRUE;


--exec BEFTN.dpr_generate_FHR_4

---create directory MY_XML AS 'D:\MY_XML\';


BEGIN
v_OutFile := utl_file.fopen( p_dir, p_filename||'.xml', 'w' );

utl_file.put_line(v_OutFile,'');
utl_file.put_line(v_OutFile,' ');
---------------------------------------------------------------------------------
for i in (select table_name from OCE where table_level=2 order by sl_id)
loop

if i.table_name = 'BEFTN_FHR' then
v_More := TRUE;
v_QryCtx := DBMS_XMLQuery.newContext('SELECT PRIORITYCODE,
IMMEDIATEDESTINATION ,
IMMEDIATEORIGIN ,
CREATIONDATE ,
CREATEIONTIME,
FILEIDMODIFIER ,
FORMATCODE,
IMMEDIATEDESTINATIONNAME,
IMMEDIATEORIGINNAME,
REFERENCECODE
FROM '||i.table_name||'
WHERE rownum = 1');
DBMS_XMLQuery.setRowsetTag(v_QryCtx, 'FHR');
DBMS_XMLQuery.setRowTag(v_QryCtx, 'FHR');
v_Xml := DBMS_XMLQuery.GetXML(v_QryCtx);
DBMS_XMLQuery.closeContext(v_QryCtx);

while v_more loop
utl_File.Put(v_OutFile, Substr(v_Xml, 24, 32767));
if length(v_Xml) > 32767 then
v_Xml := substr(v_Xml, 32768);
else
v_More := FALSE;
end if;
end loop;
end if;

END LOOP;


utl_file.put_line(v_OutFile,' ');
utl_file.fclose( v_OutFile );

Exception
when others then
utl_file.fclose( v_OutFile );
End;
/