Tuesday 22 January 2013

Validate XML against an XSD using JDeveloper




Validating xml against an xsd can be achieved thorough JDeveloper, (which is available as a free download).

As an example, if we have the following xsd saved in a file

<?xml version="1.0" encoding="UTF-8"?> 
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">   
   <xs:element name="userdetails">     
      <xs:complexType>       
         <xs:sequence>             
            <xs:element name="username" type="xs:string"/>         
            <xs:element name="password"  type="xs:string"/> 
         </xs:sequence>     
      </xs:complexType>   
   </xs:element> 
</xs:schema>

and the following xml saved in another file

<userdetails>
   <username>Fred</username>
   <password>pass123</password>
</userdetails>

We can open both the files in JDeveloper. Once opened a reference to the XSD needs to be made in the xml file. Modify the xml file and add the following entry to the initial node

    
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
  xsi:noNamespaceSchemaLocation="a.xsd">

so the file looks like

<userdetails
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
    xsi:noNamespaceSchemaLocation="a.xsd">
   <username>Fred</username>
   <password>pass123</password>
</userdetails>

This adds a reference to the xsd file (a.xsd) which resides in the same file location as the xml.

The xml can be validated by right clicking the xml and choosing Validate XML.


If the xml validates against the xsd the following will be displayed in the messages log.


If the xml is modified by adding an element which is not defined in the xsd

 <userdetails>
 <username>Fred</username>
 <password>pass123</password>
 <newelement>dummy value</newelement>
</userdetails>

then when the validation is run an error is displayed


Validating xml with a namespace against an xsd

In the following example the xml is modified to have a default namespace of http://www.onsheld.co.uk/ns1

<?xml version="1.0"?>
<userdetails xmlns="http://www.onsheld.co.uk/ns1">
   <username>Fred</username>
   <password>pass123</password>
</userdetails>

The xsd to validate the xml is show below.  Note the xsd has a reference to the namespace.


<xs:schema elementFormDefault="qualified" 
           targetNamespace="http://www.onsheld.co.uk/ns1" 
           xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="userdetails">
    <xs:complexType>
      <xs:sequence>
        <xs:element type="xs:string" name="username"/>
        <xs:element type="xs:string" name="password"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

The xml would require the following two tags to point the xml to the xsd and indicate validation was required

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
This indicates xml validation

xsi:schemaLocation="http://www.onsheld.co.uk/ns1 http://www.onsheld.co.uk/files/xml_files/a.xsd"

This has two parts, first the namespace followed by a space followed by the name and location of the xsd file (in this case the file is called a.xsd which resides on the web at location http://www.onsheld.co.uk/files/xml_files/a.xsd )

The revised xml would be:

<?xml version="1.0"?>
<userdetails xmlns="http://www.onsheld.co.uk/ns1"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
             xsi:schemaLocation="http://www.onsheld.co.uk/ns1 http://www.onsheld.co.uk/files/xml_files/a.xsd">
   <username>Fred</username>
   <password>pass123</password>
</userdetails>


Further resources


Monday 7 January 2013

VB .Net, Creating Hello World Example



In this blog entry we are going to create a form in VB .Net.  The user will be able to run the form and enter their name in a text box.  On pressing the submit button the form will display the words Hello and the name entered in the text box.

To create this application we will complete the following steps:
    1. Open up Visual Studio 2012
    2. Create a new project
    3. Add a text box to the form
    4. Set the properties of the text box
    5. Add a button
    6. Set the properties of the button
    7. Add a label to the form
    8. Set the properties of the label
    9. Create the code behind the form
    10. Run the form

      Step 1, Open up Microsoft Visual Studio 2012.

      • Click the windows button
      • Click on Visual Studio 2012

      Step 2, Create a new project

      • Click File
      • New Project




      • In the name field enter a name
      • Click OK.

      Step 3, Add a text box to the form


      A page will appear with a new blank form.
      At the left hand side of the form there is a menu called Toolbox.



      Click on the Toolbox Menu


      • Click on TextBox
      • Hold down the left mouse button
      • Drage th Text Box off the Menu
      • When the mernu disappears drop the text box onto the form.
       The form should now look like this:


      Step 4, Set the properties of the text box


      If we click on the text box on the bottom right of the screen we can see the properties of the text box.  The properties are all the differnt settings of the text box.



      Click on the properites and find name.  Make sure its set to textBox1


      Step 5, Add a button

      • Click on the Toolbox on the left of the form
      • Click on Button
      • Hold down the left mouse button
      • Drag the Button off the Menu
      • When the mernu disappears drop the button onto the form.

      Step 6, Set the properties of the button

      • Click on the button on the form
      • Go over to the properties (bottom right of the screen)
      • Make sure the name is button1
      • Change text to Submit.  When this is changed you'll see the label of the button change from button1 to Submit 

      Step 7, Add a label to the form


      Click on the Toolbox on the left of the form
      Click on Label
      Hold down the left mouse button
      Drag the Label off the Menu
      When the menu disappears drop the label onto the form.
      Put the lavel on the form underneath the button

      Step 8, Set the properties of the label

      • Make sure the name is label1
      • Set the text to nothing
      • Set Minimum Size to 50,0


      Step 9, Create the code behind the form

      • Double click on the button
      • Enter the following code
        • label2.Text = "Hello " +  textBox1.Text
      • between the text Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click and  End Sub
      Public Class Form1
      
          Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
      
             label2.Text = "Hello " +  textBox1.Text
      
          End Sub
      End Class
      



      Step 10, run the form


      Click the start button on the toolbar to run the form

      How it works

       

      • The users enters their name in textbox1.
      • The user then presses the button button1.
      • Pressing button1 causes the code behind button1to run.
      • The code behind button1 sets the label (labl1) to the text Hello + whatever text has been entered in textbox1.



      Saturday 5 January 2013

      Validate XML in Oracle against an XSD using DBMS_XMLSCHEMA and XMLTYPE.SCHEMAVALIDATE

      The xml below

      <userdetails>
         <username>Fred</username>
         <password>pass123</password>
      </userdetails>
      can be represented by the following XSD

      <?xml version="1.0" encoding="UTF-8"?> 
      <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">   
         <xs:element name="userdetails">     
            <xs:complexType>       
               <xs:sequence>             
                  <xs:element name="username" type="xs:string"/>         
                  <xs:element name="password"  type="xs:string"/> 
               </xs:sequence>     
            </xs:complexType>   
         </xs:element> 
      </xs:schema>
      The xsd can be registered with the oracle database by making a call to dbms_xmlschema.registerSchema.
      In the example below we have named this xsd as xsd1.xsd

      BEGIN    
      
         dbms_xmlschema.registerSchema(schemaURL   => 'xsd1.xsd', 
                                       schemaDoc   => '<?xml version="1.0" encoding="UTF-8"?> 
                                                       <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">   
                                                       <xs:element name="userdetails">     
                                                       <xs:complexType>       
                                                       <xs:sequence>             
                                                       <xs:element name="username" type="xs:string"/>         
                                                       <xs:element name="password"  type="xs:string"/> 
                                                       </xs:sequence>     
                                                       </xs:complexType>   
                                                       </xs:element> 
                                                       </xs:schema>'); 
      END; 
      / 
      The registered schema can be viewed in the database by accessing the data dictionary all_xml_schemas

      SELECT schema 
        FROM all_xml_schemas 
       WHERE schema_url = 'xsd1.xsd'
      /
      
      SCHEMA
      ----------------------------------------------------------------------------------------------------
      <?xml version="1.0" encoding="WINDOWS-1252"?>
      <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:oraxdb="http://xmlns.oracle.com/xdb" oraxdb:flags="295" oraxdb:schemaURL="xsd1.xsd" oraxdb:schemaOwner="MYSCHEMA" oraxdb:numProps="3">
         <xs:element name="userdetails" oraxdb:propNumber="2515" oraxdb:global="true" oraxdb:SQLName="userdetails" 
        oraxdb:SQLType="userdetails193_T" oraxdb:SQLSchema="MYSCHEMA" oraxdb:memType="258" oraxdb:defaultTable="userdetails194_TAB" oraxdb:defaultTableSchema="MYSCHEMA">
            <xs:complexType oraxdb:SQLType="userdetails193_T" oraxdb:SQLSchema="MYSCHEMA">
               <xs:sequence>
                  <xs:element name="username" type="xs:string" oraxdb:propNumber="2513" oraxdb:global="false"
      oraxdb:SQLName="username" oraxdb:SQLType="VARCHAR2" oraxdb:memType="1" oraxdb:SQLInline="true" oraxdb:MemInline="true" oraxdb:JavaInline="true"/>
                  <xs:element name="password" type="xs:string" oraxdb:propNumber="2514" oraxdb:global="false"
      oraxdb:SQLName="password" oraxdb:SQLType="VARCHAR2" oraxdb:memType="1" oraxdb:SQLInline="true" oraxdb:MemInline="true" oraxdb:JavaInline="true"/>
               </xs:sequence>
            </xs:complexType>
         </xs:element>
      </xs:schema>
      To validate the xml shown below

      <userdetails>
         <username>Fred</username>
         <password>pass123</password>
      </userdetails>
      we need to add a reference to the xsd we registered earlier and called xsd1.xsd. To the outer node add the reference xsi:noNamespaceSchemaLocation="xsd1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

      <userdetails xsi:noNamespaceSchemaLocation="xsd1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      

      We can then make a call to XMLTYPE.schemaValidate to validate the xml against the schema
      DECLARE
      
      l_xml         XMLTYPE ;
      
      BEGIN    
      
         l_xml := XMLTYPE('<userdetails xsi:noNamespaceSchemaLocation="xsd1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                             <username>Fred</username>
                             <password>pass123</password>
                           </userdetails>');     
                           
         XMLTYPE.schemaValidate(l_xml); 
      
      END; 
      /
      
      
      PL/SQL procedure successfully completed.
      The successful execution of the pl/sql indicates the xml was valid. Adding an element that is not defined in the xsd (element newelement) will fail schema validation resulting in an exception being raised

      DECLARE
      
      l_xml         XMLTYPE ;
      
      BEGIN    
      
         l_xml := XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
                           <userdetails xsi:noNamespaceSchemaLocation="xsd1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                             <username>Fred</username>
                             <password>pass123</password>
                             <newelement>dummy value</newelement>
                           </userdetails>');     
                           
         XMLTYPE.schemaValidate(l_xml); 
      
      END; 
      /
      
      DECLARE
      *
      ERROR at line 1:
      ORA-30937: No schema definition for 'newelement' (namespace '##local') in parent '/userdetails'
      ORA-06512: at "SYS.XMLTYPE", line 345
      ORA-06512: at line 14
      If we don't have an XSD but already have a type representation in the database Oracle allows us to generate an XSD based upon that type

      Our original XML

      <userdetails>
         <username>Fred</username>
         <password>pass123</password>
      </userdetails>
      can be represented in the database by the following type

      CREATE TYPE user_details_typ AS OBJECT
      (
       "username"     VARCHAR2(20) ,
       "password"     VARCHAR2(30)
      )
      /
      
      Type created.
      Note, the attributes username and password have been enclosed in double quotes to preserve their case. To generate the XSD a call to dbms_xmlschema.generateschema is required.

      SELECT DBMS_XMLSCHEMA.generateschema('MYSCHEMA', 'USER_DETAILS_TYP', 'userdetails') FROM DUAL ;
      

      This will result in the following XSD being created
      <?xml version="1.0"?>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
      <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
      xmlns:xdb="http://xmlns.oracle.com/xdb" 
      xsi:schemaLocation="http://xmlns.oracle.com/xdb http://xmlns.oracle.com/xdb/XDBSchema.xsd">                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
       <xsd:element name="userdetails" type="USER_DETAILS_TYPType" xdb:SQLType="USER_DETAILS_TYP" xdb:SQLSchema="MYSCHEMA"/>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
       <xsd:complexType name="USER_DETAILS_TYPType" xdb:SQLType="USER_DETAILS_TYP" xdb:SQLSchema="MYSCHEMA" xdb:maintainDOM="false">                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
        <xsd:sequence>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         <xsd:element name="username" xdb:SQLName="username" xdb:SQLType="VARCHAR2">                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
          <xsd:simpleType>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
           <xsd:restriction base="xsd:string">                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
            <xsd:maxLength value="20"/>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
           </xsd:restriction>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
          </xsd:simpleType>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
         </xsd:element>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
         <xsd:element name="password" xdb:SQLName="password" xdb:SQLType="VARCHAR2">                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
          <xsd:simpleType>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
           <xsd:restriction base="xsd:string">                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
            <xsd:maxLength value="30"/>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
           </xsd:restriction>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
          </xsd:simpleType>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
         </xsd:element>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
        </xsd:sequence>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
       </xsd:complexType>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
      </xsd:schema> 
       
      The parameters passed into dbms_xmlschema.generateschema were:
      • 'MYSCHEMA' - the name of the schema the object (user_details_typ) resides
      • 'USER_DETAILS_TYP' - the name of the object
      • 'userdetails' - the name of the top node
      An issue with creating types where the attributes are enclosed in double quotes such as

      CREATE TYPE user_details_typ AS OBJECT
      (
       "username"     VARCHAR2(20) ,
       "password"     VARCHAR2(30)
      )
      /
      
      
      is that in order to reference the type in pl/sql the attributes have to be enclosed in double quotes to preserve their case

      DECLARE
      
      l_user_details     user_details_typ ;
      
      BEGIN
      
         l_user_details := user_details_typ (NULL, NULL) ;
         l_user_details."username" := 'Fred' ;
      
      END ;
      /
      
      PL/SQL procedure successfully completed.
      
      
      Failing to use double quotes will result in an error

      DECLARE
      
      l_user_details     user_details_typ ;
      
      BEGIN
      
         l_user_details := user_details_typ (NULL, NULL) ;
         l_user_details.username := 'Fred' ;
      
      END ;
      /
      
      ERROR at line 8:
      ORA-06550: line 8, column 19:
      PLS-00302: component 'USERNAME' must be declared
      ORA-06550: line 8, column 4:
      PL/SQL: Statement ignored
      
      
      Alternatively the object could be created without double quotes and the resulting xml could be tranformed using a style sheet.
      The code below transforms xml with uppercase attribute names to xml with lower case attribute names using the xml method transform.

      DECLARE
      
      l_xml              XMLTYPE ;
      l_xslt             XMLTYPE ;
      l_transformed_xml  XMLTYPE ;
      
      BEGIN 
      
         -- the original xml
         l_xml := XMLTYPE ('<USERDETAILS>
                              <USERNAME>Fred</USERNAME>
                              <PASSWORD>pass123</PASSWORD>
                           </USERDETAILS>') ;
                           
         dbms_output.put_line ('Original xml' || CHR(10)||'---------------------------') ;                  
         dbms_output.put_line (l_xml.getclobval) ;   
         
         
         -- stylesheet to transform all the element names to lower case
         
         l_xslt := XMLTYPE ('<?xml version="1.0" encoding="UTF-8"?>
                              <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
                              <xsl:output method="xml" encoding="UTF-8" version="1.0" />  
                                <xsl:template match="*">
                                  <xsl:variable name="vElement"
                                    select="translate(name(), ''ABCDEFGHIJKLMNOPQRSTUVWXYZ'',''abcdefghijklmnopqrstuvwxyz'')" />
                                  <xsl:element name="{$vElement}">
                                    <xsl:apply-templates />
                                  </xsl:element>
                                </xsl:template>
                              </xsl:stylesheet>') ;      
      
         -- apply the xslt to the original xml
         
         l_transformed_xml := l_xml.transform (l_xslt) ;          
         
         dbms_output.put_line ('Transformed xml'|| CHR(10)||'---------------------------') ;      
         dbms_output.put_line (l_transformed_xml.getclobval) ;
      
      
      END ;
      /
      
      Original xml
      ---------------------------
      <USERDETAILS>
                              <USERNAME>Fred</USERNAME>
      
      <PASSWORD>pass123</PASSWORD>
                           </USERDETAILS>
      Transformed xml
      ---------------------------
      <userdetails>
      <username>Fred</username>
      <password>pass123</password>
      </userdetails>
      
      
      PL/SQL procedure successfully completed.