?
Solved

Validate XML file aginset spicific XSD

Posted on 2011-05-04
14
Medium Priority
?
1,081 Views
Last Modified: 2013-12-07
please help in Validating XML file aginst XSD file by PL/SQL and the result of validation if not sucessful to display the error,
Note 1. the XML file stored in physical file
        2. XSD already registered

Thank You
0
Comment
Question by:malek_eng
13 Comments
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 35692266
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35692269
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35692287
for_yan,

Did you look at that link before posting it after you Googled it?   They want to use Oracle's PL/SQL to validate the XML.  That link only talks about 3rd party tools.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:malek_eng
ID: 35692333
< I need Sample code  >
0
 
LVL 92

Expert Comment

by:objects
ID: 35692340
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35692374
Before I can provide code, I need more about the requirements.

How are you planning on getting the XML file from the filesystem into the Oracle stored procedure?
0
 

Author Comment

by:malek_eng
ID: 35692453
Thanks a lot for you concerned
i use l_bfile := bfilename('XML_dir', file_name.xml);
and the XSD registered
Note : i dont' have Deep knowledge in PL/SQL
0
 

Author Comment

by:malek_eng
ID: 35692494
i needed in Java or PL/SQL preffered to be in PL/SQL
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35692508
>>preffered to be in PL/SQL

Let me see what I can come up with.
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 35692750
Quick and dirty.

I borrowed the XML and XSD from: http://en.wikipedia.org/wiki/XML_Schema_%28W3C%29

I borrowed the PL/SQL from:
http://forums.oracle.com/forums/thread.jspa?threadID=373116

Below you'll find the xsd (I named q.xsd).
a 'good' xml file and a 'bad' xml file ('XX' in country).

The register script and an anonymous PL/SQL block that runs it all.
declare
	xmldoc xmltype;
begin
    
    --Good
	xmldoc := (XMLType(bfilename('XMLDIR', 'q_good.xml'), nls_charset_id('AL32UTF8')));
	xmldoc := xmldoc.createSchemaBasedXML('http://xmlns.oracle.com/xdb/documentation/q.xsd');
	if xmldoc.isSchemaValid() = 1 then
		dbms_output.put_line('Valid');
	else
		dbms_output.put_line('InValid');
		xmldoc.schemaValidate();
	end if;

    --Bad
	xmldoc := (XMLType(bfilename('XMLDIR', 'q_bad.xml'), nls_charset_id('AL32UTF8')));
	xmldoc := xmldoc.createSchemaBasedXML('http://xmlns.oracle.com/xdb/documentation/q.xsd');
	if xmldoc.isSchemaValid() = 1 then
		dbms_output.put_line('Valid');
	else
		dbms_output.put_line('InValid');
		xmldoc.schemaValidate();
	end if;
end;
/

Open in new window

drop directory XMLDIR;
create directory XMLDIR as 'c:\';

BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://xmlns.oracle.com/xdb/documentation/q.xsd',
    SCHEMADOC => bfilename('XMLDIR','q.xsd'));
END;
/

Open in new window

<?xml version="1.0" encoding="utf-8"?>
<Address xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Recipient>Mr. Walter C. Brown</Recipient>
  <House>49</House>
  <Street>Featherstone Street</Street>
  <Town>LONDON</Town>
  <PostCode>EC1Y 8SY</PostCode>
  <Country>XX</Country>
</Address>

Open in new window

<?xml version="1.0" encoding="utf-8"?>
<Address xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Recipient>Mr. Walter C. Brown</Recipient>
  <House>49</House>
  <Street>Featherstone Street</Street>
  <Town>LONDON</Town>
  <PostCode>EC1Y 8SY</PostCode>
  <Country>UK</Country>
</Address>

Open in new window

<?xml version="1.0" encoding="utf-8"?>
<xs:schema elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Address">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Recipient" type="xs:string" />
        <xs:element name="House" type="xs:string" />
        <xs:element name="Street" type="xs:string" />
        <xs:element name="Town" type="xs:string" />
        <xs:element name="County" type="xs:string" minOccurs="0" />
        <xs:element name="PostCode" type="xs:string" />
        <xs:element name="Country">
          <xs:simpleType>
            <xs:restriction base="xs:string">
              <xs:enumeration value="FR" />
              <xs:enumeration value="DE" />
              <xs:enumeration value="ES" />
              <xs:enumeration value="UK" />
              <xs:enumeration value="US" />
            </xs:restriction>
          </xs:simpleType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Open in new window

0
 

Author Comment

by:malek_eng
ID: 35693545
Many Thanks for your  effort .
If possible , Please to send to me how I can know the failed area of XML file or in which node not succeeded   such as printing the out put of validation
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 35694310
I think the error is directly related to the 'type' of failure.  If you tweak the 'bad' XML to the one below, you get a more descriptive error message.


Here is the error I receive:
declare
*
ERROR at line 1:
ORA-30936: Maximum number (1) of 'County' XML node elements exceeded
ORA-06512: at "SYS.XMLTYPE", line 345
ORA-06512: at line 7
<?xml version="1.0" encoding="utf-8"?>
<Address xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Recipient>Mr. Walter C. Brown</Recipient>
  <House>49</House>
  <Street>Featherstone Street</Street>
  <Town>LONDON</Town>
  <County>XX</County>
  <County>YY</County>
  <PostCode>EC1Y 8SY</PostCode>
  <Country>UK</Country>
</Address>

Open in new window

0
 

Author Closing Comment

by:malek_eng
ID: 35696198
Many Thanks for the effort and help
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses
Course of the Month15 days, 18 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question