1 2 Previous Next 24 Replies Latest reply on Sep 5, 2014 1:44 PM by Marco Gralike

    hi i am having issue in validating xml against xsd and inserting it into table....

    2737886

      I am using this function to validate the xml against xsd but it is giving -31000 error related to xdb

      and I am also trying to fetch some data from the xml file but it is fetching null's from it.

      while I was trying to fetch data after removing the tablenamespace , url and other extra field or can say

      from a simple xml file than it is fetching from it.

      please help me out with this !!!

       

       

       

      CREATE OR REPLACE FUNCTION ALI$XML_VALID (P_xml IN xmltype
                                               ,P_xsd IN xmltype)
                                                RETURN NUMBER
      is


        l_status NUMBER;
        l_xml xmltype;
        l_xsd XMLTYPE;
        l_v_schemaURL    VARCHAR2 (256) := 'NewBusinessApplication.xsd';
        schema_doesnt_exist exception;
        pragma exception_init (schema_doesnt_exist, -31000);

      BEGIN
        l_xsd := p_xsd;
        begin
           DBMS_XMLSCHEMA.deleteSchema(l_v_schemaURL, dbms_xmlSchema.DELETE_CASCADE_FORCE);
        exception
          when schema_doesnt_exist then
            null;
        end;

      DBMS_XMLSCHEMA.REGISTERSCHEMA(

          schemaurl => l_v_schemaURL

        , schemadoc => l_xsd

        , local     => TRUE

        , genTypes  => FALSE

        , genbean   => FALSE

        , genTables => FALSE

        );
        l_xml := p_xml ;

         if l_xml.isSchemaValid() = 1 then

           dbms_output.put_line('Schema is Valid');
           l_status := 1;
           RETURN l_status;
         else
           l_xml.schemaValidate();
           l_status := 0;
           RETURN l_status;
         end if;
      END;

       

      ---- call to above function

       

       

       

      DECLARE

         l_xml xmltype := xmltype('<?xml version="1.0" encoding="UTF-8"?>

      <NewBusinessApplication xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

                              xsi:schemaLocation="http://www.example.org NewBusinessApplication.xsd"

                              xmlns="http://www.example.org">

        <TransactionHeader>

          <TR_REF_NO>000009812681</TR_REF_NO>

          <EFF_DT>20140703094140.572[-4:US/Eastern]</EFF_DT>

          <SRC_SYS_CD dmnSRC_SYSTEM="TSS">Tele Support System</SRC_SYS_CD>

        </TransactionHeader>

        <ApplicationRq>

          <ApplInfo>

            <APPLNO>A00018L</APPLNO>

            <MED_NONMED dmnMED_NONMED="M">Medical</MED_NONMED>

            <APPL_DT>20140714</APPL_DT>

            <EFF_DT>20140714</EFF_DT>

            <APPL_RECV_DT>20140714</APPL_RECV_DT>

          </ApplInfo>

          <AgentInfo>

            <!--Repeat the Below nodes for more than one agent -->

            <Agent>

              <AGT_CD>AGN0000001</AGT_CD>

              <FIRSTNAME>James</FIRSTNAME>

              <LASTNAME>Whorphin</LASTNAME>

              <AGT-LICNO>BKK1010090009</AGT-LICNO>

              <TEAM>Warriors</TEAM>

              <TEAMLEADER>Suriya Thapa</TEAMLEADER>

              <TEAMLEADER-LICNO>BKK909988999</TEAMLEADER-LICNO>

            </Agent>

          </AgentInfo>

          <InsuredInfo>

            <PREFIX-TH dmnPERS_NAMEPREFIX="XX">Dr.</PREFIX-TH>

            <FIRSTNAME-TH>XXXXXXX</FIRSTNAME-TH>

            <MIDNAME-TH>XXXXXX</MIDNAME-TH>

            <LASTNAME-TH>XXXXXX</LASTNAME-TH>

            <FORMERNAME>XXXXXX</FORMERNAME>

            <PREFIX-EN dmnPERS_NAMEPREFIX-EN="XX">Dr.</PREFIX-EN>

            <FIRSTNAME-EN>YYYYYY</FIRSTNAME-EN>

            <MIDNAME-EN>YYYYYY</MIDNAME-EN>

            <LASTNAME-EN>YYYYYY</LASTNAME-EN>

            <BIRTH_DT>19800101</BIRTH_DT>

            <AGE>33</AGE>

            <GENDER dmnPERNSEXCD="XX">Male</GENDER>

            <MARITAL_STATUS dmnPERC_MAR_STAT_CD="XX">Single</MARITAL_STATUS>

            <RACE dmnRACE="XX">ZZZ</RACE>

            <RELIGION dmnRELIGION="H">HINDU</RELIGION>

            <NATIONALITY dmnNATIONALITY="TH">THAI</NATIONALITY>

            <ID-TYPE dmnIDTYPE="TI">Thai National ID</ID-TYPE>

            <ID-NUMBER>AJMPK0001J</ID-NUMBER>

            <ID-XPIR_DT>20200101</ID-XPIR_DT>

            <OCCUPATION dmnOCCUP_CD="XX"/>

            <OCCUP_CLASS dmnOCCUP_CLASS_CD="XX"/>

            <ANNUAL_INCOME>100000</ANNUAL_INCOME>

            <HEIGHT>175</HEIGHT>

            <WEIGHT>80</WEIGHT>

          </InsuredInfo>

          <ProposerInfo>

            <PREFIX-TH dmnPERS_NAMEPREFIX="XX">Dr.</PREFIX-TH>

            <FIRSTNAME-TH>XXXXXXX</FIRSTNAME-TH>

            <MIDNAME-TH>XXXXXX</MIDNAME-TH>

            <LASTNAME-TH>XXXXXX</LASTNAME-TH>

            <FORMERNAME>XXXXXX</FORMERNAME>

            <PREFIX-EN dmnPERS_NAMEPREFIX-EN="XX">Dr.</PREFIX-EN>

            <FIRSTNAME-EN>YYYYYY</FIRSTNAME-EN>

            <MIDNAME-EN>YYYYYY</MIDNAME-EN>

            <LASTNAME-EN>YYYYYY</LASTNAME-EN>

            <BIRTH_DT>19800101</BIRTH_DT>

            <AGE>33</AGE>

            <GENDER dmnPERNSEXCD="XX">Male</GENDER>

            <MARITAL_STATUS dmnPERC_MAR_STAT_CD="XX">Single</MARITAL_STATUS>

            <RACE dmnRACE="XX">ZZZ</RACE>

            <RELIGION dmnRELIGION="H">HINDU</RELIGION>

            <NATIONALITY dmnNATIONALITY="TH">THAI</NATIONALITY>

            <ID_TYPE dmnIDTYPE="TI">Thai National ID</ID_TYPE>

            <ID_NUMBER>AJMPK0001J</ID_NUMBER>

            <ID_XPIR_DT>20200101</ID_XPIR_DT>

            <OCCUPATION dmnOCCUP_CD="XX"/>

            <OCCUP_CLASS dmnOCCUP_CLASS_CD="XX"/>

            <ANNUAL_INCOME>100000</ANNUAL_INCOME>

          </ProposerInfo>

          <ContactInfo>

            <AddressInfo>

              <!--Repeat the Below nodes for each address type -->

              <Address>

                <ADDRTYPE dmnADDR_ADDRTYPE="01">Home</ADDRTYPE>

                <PRIMARY_CONTACT>0</PRIMARY_CONTACT>

              </Address>

              <Address>

                <ADDRTYPE dmnADDR_ADDRTYPE="02">Office</ADDRTYPE>

                <PRIMARY_CONTACT>1</PRIMARY_CONTACT>

              </Address>

            </AddressInfo>

            <PhoneInfo>

              <!--Repeat the Below nodes for each phone type -->

              <Phone>

                <PHONETYPE dmnADRP_PHONETYPE="01">Mobile</PHONETYPE>

                <PHONENUM>9887666789</PHONENUM>

                <PRIMARY_CONTACT>1</PRIMARY_CONTACT>

              </Phone>

              <Phone>

                <PHONETYPE dmnADRP_PHONETYPE="02">Home</PHONETYPE>

                <PHONENUM>9887666789</PHONENUM>

                <PRIMARY_CONTACT>0</PRIMARY_CONTACT>

              </Phone>

            </PhoneInfo>

            <EmailInfo>

              <!--Repeat the Below nodes for each email type -->

              <Email>

                <EMAILTYPE dmnADRO_EMAILTYPE="01">Email1</EMAILTYPE>

                <EMAILDATA>abc@yahoo.co.in</EMAILDATA>

                <PRIMARY_CONTACT>0</PRIMARY_CONTACT>

              </Email>

              <Email>

                <EMAILTYPE dmnADRO_EMAILTYPE="02">Email2</EMAILTYPE>

                <EMAILDATA>abc@yahoo.co.in</EMAILDATA>

                <PRIMARY_CONTACT>1</PRIMARY_CONTACT>

              </Email>

            </EmailInfo>

          </ContactInfo>

          <PolicyInfo>

            <BASE-PLAN_CD>STG102</BASE-PLAN_CD>

            <BASE-PLAN_NM>Smart Term Gold 10</BASE-PLAN_NM>

            <MODEOFPAYMENT dmnZZFREQUENCY="M">Monthly</MODEOFPAYMENT>

            <BASE-SA>100000.00</BASE-SA>

            <BASE-PREMIUM>12000.00</BASE-PREMIUM>

            <BASE-MODALPREMIUM>1000.00</BASE-MODALPREMIUM>

            <AYFP>13200.00</AYFP>

            <RiderInfo>

              <Rider>

                <RIDER-CODE>ME</RIDER-CODE>

                <RIDER-NAME>Medical Expense</RIDER-NAME>

                <RIDER-SA>20000.00</RIDER-SA>

                <RIDER-PREMIUM>600.00</RIDER-PREMIUM>

                <RIDER-MODALPREMIUM>50.00</RIDER-MODALPREMIUM>

              </Rider>

              <Rider>

                <RIDER-CODE>HAB</RIDER-CODE>

                <RIDER-NAME>Hospital Admission Benefit</RIDER-NAME>

                <RIDER-SA>20000.00</RIDER-SA>

                <RIDER-PREMIUM>600.00</RIDER-PREMIUM>

                <RIDER-MODALPREMIUM>50.00</RIDER-MODALPREMIUM>

              </Rider>

            </RiderInfo>

          </PolicyInfo>

          <PaymentInfo>

            <FirstYear>

              <TMP_RECEIPT_NO>TEMP0001</TMP_RECEIPT_NO>

              <TOTAL_PYMT_AMT>13200.00</TOTAL_PYMT_AMT>

              <!--Repeat the Below nodes for payment through Multiple channels -->

              <Payment>

                <PAYER dmnPAYRTYPE="01">Insured</PAYER>

                <PYMT-TYPE dmnPaymentType="01">Credit Card</PYMT-TYPE>

                <PYMT-AMT>13200.00</PYMT-AMT>

                <PYMT-Channel>

                  <CreditCard>

                    <CARD-TYPE dmnCardType="01">Visa</CARD-TYPE>

                    <CARD-NO>9890989098909877</CARD-NO>

                    <CARD-HOLDERNAME>RICKY WHORPHIN</CARD-HOLDERNAME>

                    <CARD-BANKNM>Standard Chartered Bank</CARD-BANKNM>

                    <CARD-EXPIRYDATE>08/14</CARD-EXPIRYDATE>

                    <CARD-AUTHCODE/>

                    <CARD-AUTHDATE/>

                  </CreditCard>

                </PYMT-Channel>

              </Payment>

              <Payment>

                <PAYER dmnPAYRTYPE="01">Insured</PAYER>

                <PYMT-TYPE dmnPaymentType="02">Deposit Account</PYMT-TYPE>

                <PYMT-AMT>13200.00</PYMT-AMT>

                <PYMT-Channel>

                  <Bank>

                    <BANK-CODE/>

                    <BANK-BRANCH/>

                    <BANK-ACCNO/>

                    <BANK-ACCNAME/>

                  </Bank>

                </PYMT-Channel>

              </Payment>

              <Payment>

                <PAYER dmnPAYRTYPE="01">Insured</PAYER>

                <PYMT-TYPE dmnPaymentType="03">Cheque</PYMT-TYPE>

                <PYMT-AMT>13200.00</PYMT-AMT>

                <PYMT-Channel>

                  <Cheque>

                    <CHQ-NO/>

                    <CHQ-DATE/>

                    <CHQ-ISSUEBANK/>

                  </Cheque>

                </PYMT-Channel>

              </Payment>

            </FirstYear>

            <RenewalPayment>

              <PAYER dmnPAYRTYPE="01">Insured</PAYER>

              <DDCHANNEL dmnPaymentType="01">DDC</DDCHANNEL>

              <DDCDetails>

                <CARD-TYPE dmnCardType="01">Visa</CARD-TYPE>

                <CARD-NO>9890989098909877</CARD-NO>

                <CARD-HOLDERNAME>RICKY WHORPHIN</CARD-HOLDERNAME>

                <CARD-BANKNM>Standard Chartered Bank</CARD-BANKNM>

                <CARD-EXPIRYDATE>08/14</CARD-EXPIRYDATE>

              </DDCDetails>

              <DDADetails>

                <BANK-ACCTYPE dmnBNK_TYP_CD="01">Saving</BANK-ACCTYPE>

                <BANK-ACCNO/>

                <BANK-ACCNAME/>

                <BANK-BRANCH/>

              </DDADetails>

            </RenewalPayment>

          </PaymentInfo>

          <BeneficiaryInfo>

            <!--Repeat the Below nodes for each beneficiary and sum of pct should be 100 -->

            <Beneficiary>

              <RELATIONSHIP dmnBENE_RELT_CD="01">Spouse</RELATIONSHIP>

              <FIRSTNAME>XXXX</FIRSTNAME>

              <MIDNAME>XXXXX</MIDNAME>

              <LASTNAME>XXXX</LASTNAME>

              <PERCENTAGE>50</PERCENTAGE>

              <BIRTH_DT>19800101</BIRTH_DT>

              <GENDER dmnPERNSEXCD="XX">Female</GENDER>

              <NATLIDNO>TH000001</NATLIDNO>

            </Beneficiary>

            <Beneficiary>

              <RELATIONSHIP dmnBENE_RELT_CD="02">Child</RELATIONSHIP>

              <FIRSTNAME>XXXX</FIRSTNAME>

              <MIDNAME>XXXXX</MIDNAME>

              <LASTNAME>XXXX</LASTNAME>

              <PERCENTAGE>50</PERCENTAGE>

              <BIRTH_DT>19800101</BIRTH_DT>

              <GENDER dmnPERNSEXCD="XX">Male</GENDER>

              <NATLIDNO>TH000002</NATLIDNO>

            </Beneficiary>

          </BeneficiaryInfo>

          <Questionnaire>

            <HealthDeclaration>

              <Question>

                <QUESTION_ID>Q001</QUESTION_ID>

                <ANSWER dmnZZYESNO_NUM="N">No</ANSWER>

              </Question>

              <Question>

                <QUESTION_ID>Q002</QUESTION_ID>

                <ANSWER dmnZZYESNO_NUM="N">No</ANSWER>

              </Question>

              <Question>

                <QUESTION_ID>Q003</QUESTION_ID>

                <ANSWER dmnZZYESNO_NUM="N">No</ANSWER>

              </Question>

              <Question>

                <QUESTION_ID>Q004</QUESTION_ID>

                <ANSWER dmnZZYESNO_NUM="N">No</ANSWER>

              </Question>

              <Question>

                <QUESTION_ID>Q005</QUESTION_ID>

                <ANSWER dmnZZYESNO_NUM="N">No</ANSWER>

              </Question>

            </HealthDeclaration>

          </Questionnaire>

        </ApplicationRq>

        <ApplicationRs>

          <Reject-Reason/>

        </ApplicationRs>

      </NewBusinessApplication>

      ');

         l_xsd xmltype := xmltype('<?xml version="1.0" encoding="windows-1252" ?>

      <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.example.org"

                  targetNamespace="http://www.example.org" elementFormDefault="qualified">

        <xsd:element name="NewBusinessApplication">

          <xsd:complexType>

            <xsd:sequence>

              <xsd:element name="TransactionHeader">

                <xsd:complexType>

                  <xsd:sequence>

                    <xsd:element name="TR_REF_NO" type="xsd:integer"/>

                    <xsd:element name="EFF_DT" type="xsd:string"/>

                    <xsd:element name="SRC_SYS_CD">

                      <xsd:complexType>

                        <xsd:simpleContent>

                          <xsd:extension base="xsd:string">

                            <xsd:attribute name="dmnSRC_SYSTEM" type="xsd:string"/>

                          </xsd:extension>

                        </xsd:simpleContent>

                      </xsd:complexType>

                    </xsd:element>

                  </xsd:sequence>

                </xsd:complexType>

              </xsd:element>

              <xsd:element name="ApplicationRq">

                <xsd:complexType>

                  <xsd:sequence>

                    <xsd:element name="ApplInfo">

                      <xsd:complexType>

                        <xsd:sequence>

                          <xsd:element name="APPLNO" type="xsd:string"/>

                          <xsd:element name="MED_NONMED">

                            <xsd:complexType>

                              <xsd:simpleContent>

                                <xsd:extension base="xsd:string">

                                  <xsd:attribute name="dmnMED_NONMED" type="xsd:string"/>

                                </xsd:extension>

                              </xsd:simpleContent>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="APPL_DT" type="xsd:integer"/>

                          <xsd:element name="EFF_DT" type="xsd:integer"/>

                          <xsd:element name="APPL_RECV_DT" type="xsd:integer"/>

                        </xsd:sequence>

                      </xsd:complexType>

                    </xsd:element>

                    <xsd:element name="AgentInfo">

                      <xsd:complexType>

                        <xsd:sequence>

                          <xsd:element name="Agent">

                            <xsd:complexType>

                              <xsd:sequence>

                                <xsd:element name="AGT_CD" type="xsd:string"/>

                                <xsd:element name="FIRSTNAME" type="xsd:string"/>

                                <xsd:element name="LASTNAME" type="xsd:string"/>

                                <xsd:element name="AGT-LICNO" type="xsd:string"/>

                                <xsd:element name="TEAM" type="xsd:string"/>

                                <xsd:element name="TEAMLEADER" type="xsd:string"/>

                                <xsd:element name="TEAMLEADER-LICNO" type="xsd:string"/>

                              </xsd:sequence>

                            </xsd:complexType>

                          </xsd:element>

                        </xsd:sequence>

                      </xsd:complexType>

                    </xsd:element>

                    <xsd:element name="InsuredInfo">

                      <xsd:complexType>

                        <xsd:sequence>

                          <xsd:element name="PREFIX-TH">

                            <xsd:complexType>

                              <xsd:simpleContent>

                                <xsd:extension base="xsd:string">

                                  <xsd:attribute name="dmnPERS_NAMEPREFIX" type="xsd:string"/>

                                </xsd:extension>

                              </xsd:simpleContent>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="FIRSTNAME-TH" type="xsd:string"/>

                          <xsd:element name="MIDNAME-TH" type="xsd:string"/>

                          <xsd:element name="LASTNAME-TH" type="xsd:string"/>

                          <xsd:element name="FORMERNAME" type="xsd:string"/>

                          <xsd:element name="PREFIX-EN">

                            <xsd:complexType>

                              <xsd:simpleContent>

                                <xsd:extension base="xsd:string">

                                  <xsd:attribute name="dmnPERS_NAMEPREFIX-EN" type="xsd:string"/>

                                </xsd:extension>

                              </xsd:simpleContent>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="FIRSTNAME-EN" type="xsd:string"/>

                          <xsd:element name="MIDNAME-EN" type="xsd:string"/>

                          <xsd:element name="LASTNAME-EN" type="xsd:string"/>

                          <xsd:element name="BIRTH_DT" type="xsd:integer"/>

                          <xsd:element name="AGE" type="xsd:integer"/>

                          <xsd:element name="GENDER">

                            <xsd:complexType>

                              <xsd:simpleContent>

                                <xsd:extension base="xsd:string">

                                  <xsd:attribute name="dmnPERNSEXCD" type="xsd:string"/>

                                </xsd:extension>

                              </xsd:simpleContent>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="MARITAL_STATUS">

                            <xsd:complexType>

                              <xsd:simpleContent>

                                <xsd:extension base="xsd:string">

                                  <xsd:attribute name="dmnPERC_MAR_STAT_CD" type="xsd:string"/>

                                </xsd:extension>

                              </xsd:simpleContent>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="RACE">

                            <xsd:complexType>

                              <xsd:simpleContent>

                                <xsd:extension base="xsd:string">

                                  <xsd:attribute name="dmnRACE" type="xsd:string"/>

                                </xsd:extension>

                              </xsd:simpleContent>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="RELIGION">

                            <xsd:complexType>

                              <xsd:simpleContent>

                                <xsd:extension base="xsd:string">

                                  <xsd:attribute name="dmnRELIGION" type="xsd:string"/>

                                </xsd:extension>

                              </xsd:simpleContent>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="NATIONALITY">

                            <xsd:complexType>

                              <xsd:simpleContent>

                                <xsd:extension base="xsd:string">

                                  <xsd:attribute name="dmnNATIONALITY" type="xsd:string"/>

                                </xsd:extension>

                              </xsd:simpleContent>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="ID-TYPE">

                            <xsd:complexType>

                              <xsd:simpleContent>

                                <xsd:extension base="xsd:string">

                                  <xsd:attribute name="dmnIDTYPE" type="xsd:string"/>

                                </xsd:extension>

                              </xsd:simpleContent>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="ID-NUMBER" type="xsd:string"/>

                          <xsd:element name="ID-XPIR_DT" type="xsd:integer"/>

                          <xsd:element name="OCCUPATION">

                            <xsd:complexType>

                              <xsd:attribute name="dmnOCCUP_CD" type="xsd:string"/>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="OCCUP_CLASS">

                            <xsd:complexType>

                              <xsd:attribute name="dmnOCCUP_CLASS_CD" type="xsd:string"/>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="ANNUAL_INCOME" type="xsd:integer"/>

                          <xsd:element name="HEIGHT" type="xsd:integer"/>

                          <xsd:element name="WEIGHT" type="xsd:integer"/>

                        </xsd:sequence>

                      </xsd:complexType>

                    </xsd:element>

                    <xsd:element name="ProposerInfo">

                      <xsd:complexType>

                        <xsd:sequence>

                          <xsd:element name="PREFIX-TH">

                            <xsd:complexType>

                              <xsd:simpleContent>

                                <xsd:extension base="xsd:string">

                                  <xsd:attribute name="dmnPERS_NAMEPREFIX" type="xsd:string"/>

                                </xsd:extension>

                              </xsd:simpleContent>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="FIRSTNAME-TH" type="xsd:string"/>

                          <xsd:element name="MIDNAME-TH" type="xsd:string"/>

                          <xsd:element name="LASTNAME-TH" type="xsd:string"/>

                          <xsd:element name="FORMERNAME" type="xsd:string"/>

                          <xsd:element name="PREFIX-EN">

                            <xsd:complexType>

                              <xsd:simpleContent>

                                <xsd:extension base="xsd:string">

                                  <xsd:attribute name="dmnPERS_NAMEPREFIX-EN" type="xsd:string"/>

                                </xsd:extension>

                              </xsd:simpleContent>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="FIRSTNAME-EN" type="xsd:string"/>

                          <xsd:element name="MIDNAME-EN" type="xsd:string"/>

                          <xsd:element name="LASTNAME-EN" type="xsd:string"/>

                          <xsd:element name="BIRTH_DT" type="xsd:integer"/>

                          <xsd:element name="AGE" type="xsd:integer"/>

                          <xsd:element name="GENDER">

                            <xsd:complexType>

                              <xsd:simpleContent>

                                <xsd:extension base="xsd:string">

                                  <xsd:attribute name="dmnPERNSEXCD" type="xsd:string"/>

                                </xsd:extension>

                              </xsd:simpleContent>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="MARITAL_STATUS">

                            <xsd:complexType>

                              <xsd:simpleContent>

                                <xsd:extension base="xsd:string">

                                  <xsd:attribute name="dmnPERC_MAR_STAT_CD" type="xsd:string"/>

                                </xsd:extension>

                              </xsd:simpleContent>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="RACE">

                            <xsd:complexType>

                              <xsd:simpleContent>

                                <xsd:extension base="xsd:string">

                                  <xsd:attribute name="dmnRACE" type="xsd:string"/>

                                </xsd:extension>

                              </xsd:simpleContent>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="RELIGION">

                            <xsd:complexType>

                              <xsd:simpleContent>

                                <xsd:extension base="xsd:string">

                                  <xsd:attribute name="dmnRELIGION" type="xsd:string"/>

                                </xsd:extension>

                              </xsd:simpleContent>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="NATIONALITY">

                            <xsd:complexType>

                              <xsd:simpleContent>

                                <xsd:extension base="xsd:string">

                                  <xsd:attribute name="dmnNATIONALITY" type="xsd:string"/>

                                </xsd:extension>

                              </xsd:simpleContent>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="ID_TYPE">

                            <xsd:complexType>

                              <xsd:simpleContent>

                                <xsd:extension base="xsd:string">

                                  <xsd:attribute name="dmnIDTYPE" type="xsd:string"/>

                                </xsd:extension>

                              </xsd:simpleContent>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="ID_NUMBER" type="xsd:string"/>

                          <xsd:element name="ID_XPIR_DT" type="xsd:integer"/>

                          <xsd:element name="OCCUPATION">

                            <xsd:complexType>

                              <xsd:attribute name="dmnOCCUP_CD" type="xsd:string"/>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="OCCUP_CLASS">

                            <xsd:complexType>

                              <xsd:attribute name="dmnOCCUP_CLASS_CD" type="xsd:string"/>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="ANNUAL_INCOME" type="xsd:integer"/>

                        </xsd:sequence>

                      </xsd:complexType>

                    </xsd:element>

                    <xsd:element name="ContactInfo">

                      <xsd:complexType>

                        <xsd:sequence>

                          <xsd:element name="AddressInfo">

                            <xsd:complexType>

                              <xsd:sequence>

                                <xsd:element name="Address" maxOccurs="unbounded">

                                  <xsd:complexType>

                                    <xsd:sequence>

                                      <xsd:element name="ADDRTYPE">

                                        <xsd:complexType>

                                          <xsd:simpleContent>

                                            <xsd:extension base="xsd:string">

                                              <xsd:attribute name="dmnADDR_ADDRTYPE" type="xsd:integer"/>

                                            </xsd:extension>

                                          </xsd:simpleContent>

                                        </xsd:complexType>

                                      </xsd:element>

                                      <xsd:element name="PRIMARY_CONTACT" type="xsd:integer"/>

                                    </xsd:sequence>

                                  </xsd:complexType>

                                </xsd:element>

                              </xsd:sequence>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="PhoneInfo">

                            <xsd:complexType>

                              <xsd:sequence>

                                <xsd:element name="Phone" maxOccurs="unbounded">

                                  <xsd:complexType>

                                    <xsd:sequence>

                                      <xsd:element name="PHONETYPE">

                                        <xsd:complexType>

                                          <xsd:simpleContent>

                                            <xsd:extension base="xsd:string">

                                              <xsd:attribute name="dmnADRP_PHONETYPE" type="xsd:integer"/>

                                            </xsd:extension>

                                          </xsd:simpleContent>

                                        </xsd:complexType>

                                      </xsd:element>

                                      <xsd:element name="PHONENUM" type="xsd:integer"/>

                                      <xsd:element name="PRIMARY_CONTACT" type="xsd:integer"/>

                                    </xsd:sequence>

                                  </xsd:complexType>

                                </xsd:element>

                              </xsd:sequence>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="EmailInfo">

                            <xsd:complexType>

                              <xsd:sequence>

                                <xsd:element name="Email" maxOccurs="unbounded">

                                  <xsd:complexType>

                                    <xsd:sequence>

                                      <xsd:element name="EMAILTYPE">

                                        <xsd:complexType>

                                          <xsd:simpleContent>

                                            <xsd:extension base="xsd:string">

                                              <xsd:attribute name="dmnADRO_EMAILTYPE" type="xsd:integer"/>

                                            </xsd:extension>

                                          </xsd:simpleContent>

                                        </xsd:complexType>

                                      </xsd:element>

                                      <xsd:element name="EMAILDATA" type="xsd:string"/>

                                      <xsd:element name="PRIMARY_CONTACT" type="xsd:integer"/>

                                    </xsd:sequence>

                                  </xsd:complexType>

                                </xsd:element>

                              </xsd:sequence>

                            </xsd:complexType>

                          </xsd:element>

                        </xsd:sequence>

                      </xsd:complexType>

                    </xsd:element>

                    <xsd:element name="PolicyInfo">

                      <xsd:complexType>

                        <xsd:sequence>

                          <xsd:element name="BASE-PLAN_CD" type="xsd:string"/>

                          <xsd:element name="BASE-PLAN_NM" type="xsd:string"/>

                          <xsd:element name="MODEOFPAYMENT">

                            <xsd:complexType>

                              <xsd:simpleContent>

                                <xsd:extension base="xsd:string">

                                  <xsd:attribute name="dmnZZFREQUENCY" type="xsd:string"/>

                                </xsd:extension>

                              </xsd:simpleContent>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="BASE-SA" type="xsd:float"/>

                          <xsd:element name="BASE-PREMIUM" type="xsd:float"/>

                          <xsd:element name="BASE-MODALPREMIUM" type="xsd:float"/>

                          <xsd:element name="AYFP" type="xsd:float"/>

                          <xsd:element name="RiderInfo">

                            <xsd:complexType>

                              <xsd:sequence>

                                <xsd:element name="Rider" maxOccurs="unbounded">

                                  <xsd:complexType>

                                    <xsd:sequence>

                                      <xsd:element name="RIDER-CODE" type="xsd:string"/>

                                      <xsd:element name="RIDER-NAME" type="xsd:string"/>

                                      <xsd:element name="RIDER-SA" type="xsd:float"/>

                                      <xsd:element name="RIDER-PREMIUM" type="xsd:float"/>

                                      <xsd:element name="RIDER-MODALPREMIUM" type="xsd:float"/>

                                    </xsd:sequence>

                                  </xsd:complexType>

                                </xsd:element>

                              </xsd:sequence>

                            </xsd:complexType>

                          </xsd:element>

                        </xsd:sequence>

                      </xsd:complexType>

                    </xsd:element>

                    <xsd:element name="PaymentInfo">

                      <xsd:complexType>

                        <xsd:sequence>

                          <xsd:element name="FirstYear">

                            <xsd:complexType>

                              <xsd:sequence>

                                <xsd:element name="TMP_RECEIPT_NO" type="xsd:string"/>

                                <xsd:element name="TOTAL_PYMT_AMT" type="xsd:float"/>

                                <xsd:element name="Payment" maxOccurs="unbounded">

                                  <xsd:complexType>

                                    <xsd:sequence>

                                      <xsd:element name="PAYER">

                                        <xsd:complexType>

                                          <xsd:simpleContent>

                                            <xsd:extension base="xsd:string">

                                              <xsd:attribute name="dmnPAYRTYPE" type="xsd:integer"/>

                                            </xsd:extension>

                                          </xsd:simpleContent>

                                        </xsd:complexType>

                                      </xsd:element>

                                      <xsd:element name="PYMT-TYPE">

                                        <xsd:complexType>

                                          <xsd:simpleContent>

                                            <xsd:extension base="xsd:string">

                                              <xsd:attribute name="dmnPaymentType" type="xsd:integer"/>

                                            </xsd:extension>

                                          </xsd:simpleContent>

                                        </xsd:complexType>

                                      </xsd:element>

                                      <xsd:element name="PYMT-AMT" type="xsd:float"/>

                                      <xsd:element name="PYMT-Channel">

                                        <xsd:complexType>

                                          <xsd:sequence>

                                            <xsd:element name="CreditCard" minOccurs="0">

                                              <xsd:complexType>

                                                <xsd:sequence>

                                                  <xsd:element name="CARD-TYPE">

                                                    <xsd:complexType>

                                                      <xsd:simpleContent>

                                                        <xsd:extension base="xsd:string">

                                                          <xsd:attribute name="dmnCardType" type="xsd:integer"/>

                                                        </xsd:extension>

                                                      </xsd:simpleContent>

                                                    </xsd:complexType>

                                                  </xsd:element>

                                                  <xsd:element name="CARD-NO" type="xsd:integer"/>

                                                  <xsd:element name="CARD-HOLDERNAME" type="xsd:string"/>

                                                  <xsd:element name="CARD-BANKNM" type="xsd:string"/>

                                                  <xsd:element name="CARD-EXPIRYDATE" type="xsd:string"/>

                                                  <xsd:element name="CARD-AUTHCODE"/>

                                                  <xsd:element name="CARD-AUTHDATE"/>

                                                </xsd:sequence>

                                              </xsd:complexType>

                                            </xsd:element>

                                            <xsd:element name="Bank" minOccurs="0">

                                              <xsd:complexType>

                                                <xsd:sequence>

                                                  <xsd:element name="BANK-CODE"/>

                                                  <xsd:element name="BANK-BRANCH"/>

                                                  <xsd:element name="BANK-ACCNO"/>

                                                  <xsd:element name="BANK-ACCNAME"/>

                                                </xsd:sequence>

                                              </xsd:complexType>

                                            </xsd:element>

                                            <xsd:element name="Cheque" minOccurs="0">

                                              <xsd:complexType>

                                                <xsd:sequence>

                                                  <xsd:element name="CHQ-NO"/>

                                                  <xsd:element name="CHQ-DATE"/>

                                                  <xsd:element name="CHQ-ISSUEBANK"/>

                                                </xsd:sequence>

                                              </xsd:complexType>

                                            </xsd:element>

                                          </xsd:sequence>

                                        </xsd:complexType>

                                      </xsd:element>

                                    </xsd:sequence>

                                  </xsd:complexType>

                                </xsd:element>

                              </xsd:sequence>

                            </xsd:complexType>

                          </xsd:element>

                          <xsd:element name="RenewalPayment">

                            <xsd:complexType>

                              <xsd:sequence>

                                <xsd:element name="PAYER">

                                  <xsd:complexType>

                                    <xsd:simpleContent>

                                      <xsd:extension base="xsd:string">

                                        <xsd:attribute name="dmnPAYRTYPE" type="xsd:integer"/>

                                      </xsd:extension>

                                    </xsd:simpleContent>

                                  </xsd:complexType>

                                </xsd:element>

                                <xsd:element name="DDCHANNEL">

                                  <xsd:complexType>

                                    <xsd:simpleContent>

                                      <xsd:extension base="xsd:string">

                                        <xsd:attribute name="dmnPaymentType" type="xsd:integer"/>

                                      </xsd:extension>

                                    </xsd:simpleContent>

                                  </xsd:complexType>

                                </xsd:element>

                                <xsd:element name="DDCDetails">

                                  <xsd:complexType>

                                    <xsd:sequence>

                                      <xsd:element name="CARD-TYPE">

                                        <xsd:complexType>

                                          <xsd:simpleContent>

                                            <xsd:extension base="xsd:string">

                                              <xsd:attribute name="dmnCardType" type="xsd:integer"/>

                                            </xsd:extension>

                                          </xsd:simpleContent>

                                        </xsd:complexType>

                                      </xsd:element>

                                      <xsd:element name="CARD-NO" type="xsd:integer"/>

                                      <xsd:element name="CARD-HOLDERNAME" type="xsd:string"/>

                                      <xsd:element name="CARD-BANKNM" type="xsd:string"/>

                                      <xsd:element name="CARD-EXPIRYDATE" type="xsd:string"/>

                                    </xsd:sequence>

                                  </xsd:complexType>

                                </xsd:element>

                                <xsd:element name="DDADetails">

                                  <xsd:complexType>

                                    <xsd:sequence>

                                      <xsd:element name="BANK-ACCTYPE">

                                        <xsd:complexType>

                                          <xsd:simpleContent>

                                            <xsd:extension base="xsd:string">

                                              <xsd:attribute name="dmnBNK_TYP_CD" type="xsd:integer"/>

                                            </xsd:extension>

                                          </xsd:simpleContent>

                                        </xsd:complexType>

                                      </xsd:element>

                                      <xsd:element name="BANK-ACCNO"/>

                                      <xsd:element name="BANK-ACCNAME"/>

                                      <xsd:element name="BANK-BRANCH"/>

                                    </xsd:sequence>

                                  </xsd:complexType>

                                </xsd:element>

                              </xsd:sequence>

                            </xsd:complexType>

                          </xsd:element>

                        </xsd:sequence>

                      </xsd:complexType>

                    </xsd:element>

                    <xsd:element name="BeneficiaryInfo">

                      <xsd:complexType>

                        <xsd:sequence>

                          <xsd:element name="Beneficiary" maxOccurs="unbounded">

                            <xsd:complexType>

                              <xsd:sequence>

                                <xsd:element name="RELATIONSHIP">

                                  <xsd:complexType>

                                    <xsd:simpleContent>

                                      <xsd:extension base="xsd:string">

                                        <xsd:attribute name="dmnBENE_RELT_CD" type="xsd:integer"/>

                                      </xsd:extension>

                                    </xsd:simpleContent>

                                  </xsd:complexType>

                                </xsd:element>

                                <xsd:element name="FIRSTNAME" type="xsd:string"/>

                                <xsd:element name="MIDNAME" type="xsd:string"/>

                                <xsd:element name="LASTNAME" type="xsd:string"/>

                                <xsd:element name="PERCENTAGE" type="xsd:integer"/>

                                <xsd:element name="BIRTH_DT" type="xsd:integer"/>

                                <xsd:element name="GENDER">

                                  <xsd:complexType>

                                    <xsd:simpleContent>

                                      <xsd:extension base="xsd:string">

                                        <xsd:attribute name="dmnPERNSEXCD" type="xsd:string"/>

                                      </xsd:extension>

                                    </xsd:simpleContent>

                                  </xsd:complexType>

                                </xsd:element>

                                <xsd:element name="NATLIDNO" type="xsd:string"/>

                              </xsd:sequence>

                            </xsd:complexType>

                          </xsd:element>

                        </xsd:sequence>

                      </xsd:complexType>

                    </xsd:element>

                    <xsd:element name="Questionnaire">

                      <xsd:complexType>

                        <xsd:sequence>

                          <xsd:element name="HealthDeclaration">

                            <xsd:complexType>

                              <xsd:sequence>

                                <xsd:element name="Question" maxOccurs="unbounded">

                                  <xsd:complexType>

                                    <xsd:sequence>

                                      <xsd:element name="QUESTION_ID" type="xsd:string"/>

                                      <xsd:element name="ANSWER">

                                        <xsd:complexType>

                                          <xsd:simpleContent>

                                            <xsd:extension base="xsd:string">

                                              <xsd:attribute name="dmnZZYESNO_NUM" type="xsd:string"/>

                                            </xsd:extension>

                                          </xsd:simpleContent>

                                        </xsd:complexType>

                                      </xsd:element>

                                    </xsd:sequence>

                                  </xsd:complexType>

                                </xsd:element>

                              </xsd:sequence>

                            </xsd:complexType>

                          </xsd:element>

                        </xsd:sequence>

                      </xsd:complexType>

                    </xsd:element>

                  </xsd:sequence>

                </xsd:complexType>

              </xsd:element>

              <xsd:element name="ApplicationRs">

                <xsd:complexType>

                  <xsd:sequence>

                    <xsd:element name="Reject-Reason"/>

                  </xsd:sequence>

                </xsd:complexType>

              </xsd:element>

            </xsd:sequence>

            <xsd:attribute name="xsi" type="xsd:string"/>

            <xsd:attribute name="schemaLocation" type="xsd:string"/>

          </xsd:complexType>

        </xsd:element>

      </xsd:schema>

      ');

      BEGIN

         dbms_output.put_line(ALI$XML_VALID(l_xml,l_xsd));

      END;

        • 1. Re: hi i am having issue in validating xml against xsd and inserting it into table....
          Marco Gralike

          Regarding the ORA-31000, has the XSD been registerd as expected (naming etc)

           

          Check via:

           

          SQL> select owner, schema_url, local from all_xml_schemas where owner not in ('XDB','ORDSYS','EXFSYS', 'MDSYS', 'SYS');

          • 2. Re: hi i am having issue in validating xml against xsd and inserting it into table....
            2737886

            yes, Marco in schema_url field it is showing NewBusinessApplication.xsd

            • 3. Re: hi i am having issue in validating xml against xsd and inserting it into table....
              2737886

              have you execute that function ???

              I hope you are also getting same error as ora-31000

              I don't know how to fix this ...

              I am thinking may be there is some error in xml or xsd file...

              please help me out with this I need to do this by today...

              last day the schema got validate but when I was trying to fetch some data from within the function then it is returning NULL value..

              which is not correct ? !!

              • 4. Re: hi i am having issue in validating xml against xsd and inserting it into table....
                Marco Gralike

                Database version is...?

                 

                You could check if the setup "as is works" via

                 

                SQL> declare
                  2    V_XML XMLTYPE := XMLType(:INSTANCE1).createSchemaBasedXML (:SCHEMAURL1);
                  3  begin
                  4    V_XML.schemaValidate();
                  5  end;
                  6  /

                 

                 

                See for more info regarding the above: XMLTYPE schemaValidate() Problem...

                • 5. Re: hi i am having issue in validating xml against xsd and inserting it into table....
                  2737886

                  DB version is : 11g

                   

                  and if you please specify what is :INSTANCE1 and :SCHEMAURL1 in my XML and XSD ???

                  As I am new to xml DB !!

                  • 6. Re: hi i am having issue in validating xml against xsd and inserting it into table....
                    Marco Gralike

                    See for more info regarding the above: XMLTYPE schemaValidate() Problem... (aka READ the info for a better understanding of how/what/were...)

                     

                    If there is no schema known, you will have to associate the XML doc with the XSD.

                    • 7. Re: hi i am having issue in validating xml against xsd and inserting it into table....
                      Marco Gralike

                      11g is not a version. Differences exist in behavior between versions:

                       

                      11.1.0.6  (eg securefile for the first time --> binary xml...)

                      11.1.0.7  (eg. lot of bugs fixed regarding XML functionality, XML partitioning introduced)

                      11.2.0.1  (eg.structured XML Indexes possible)

                      11.2.0.2  (eg. new XSLT/XQuery behavior)

                      11.2.0.3  (eg. XQuery Update facility introduced)

                      11.2.0.4  (...etc)

                      • 8. Re: hi i am having issue in validating xml against xsd and inserting it into table....
                        Marco Gralike

                        Extra reading material can be found in the FAQ thread (XML DB FAQ) of this XMLDB forum:

                         

                         

                        How does XML DB handle XML Schema Validation ?

                        • 9. Re: Re: hi i am having issue in validating xml against xsd and inserting it into table....
                          odie_63

                          Both the schema and XML document are fine.

                           

                          The issue is with your whole approach.

                           

                          Problem #1 :

                          First time I ran the PL/SQL block, I got this :

                          ORA-19030: Method invalid for non-schema based XML Documents.

                          for this line :

                          l_xml.schemaValidate();

                           

                          That's because at the moment the XMLType instance was built, the schema was not registered yet, so Oracle didn't recognize the xsi:schemaLocation attribute and didn't made the instance schema-based automatically.

                           

                          Problem #2 :

                          As you described, for subsequent run, we always get :

                          ORA-31000: Resource '' is not an XDB schema document.

                          for this line :

                          l_xml := p_xml ;

                           

                          That's because p_xml is schema-based when passed to the function, because now the schema was registered when the XMLType was built, but since the function drops and registers the schema again in the meantime, the internal schema id has changed and p_xml doesn't reference a valid schema anymore.

                          Consequently, when trying to build a copy of the document, it fails.

                           

                          The most simple way to fix all this is to register the schema only once, like you would do for any DDL, and not every single time you want to validate some XML.

                          Your validate function would then just perform the validation.

                          • 10. Re: Re: hi i am having issue in validating xml against xsd and inserting it into table....
                            2737886

                            Hi,

                             

                            Your answer seems to be satisfying.....

                            If you can please guide me through the process of registering that xml or xsd in my DB and get it validate and then fetch some rows from it then it'll be helpful for me.

                            I am tired of trying all these methods and nothing is helping me..

                            you know what when I run that function as anonymous block then it validates the xml (but still I cant fetch rows from that xml)  and now when I incorporate that anonymous block in a function then it thorws 31000 error.

                            • 11. Re: Re: hi i am having issue in validating xml against xsd and inserting it into table....
                              Marco Gralike

                              2737886 wrote:

                               

                              I am tired of trying all these methods and nothing is helping me..

                               

                              Reading would help. Mark shows and demonstrates a perfect example here: How does XML DB handle XML Schema Validation ?

                               

                              The ONLY thing you have to do is re-use the code...

                              • 12. Re: hi i am having issue in validating xml against xsd and inserting it into table....
                                odie_63

                                If you can please guide me through the process of registering that xml or xsd in my DB and get it validate and then fetch some rows from it then it'll be helpful for me.

                                You already know how to register a schema : use DBMS_XMLSCHEMA.registerSchema, but do that only once.

                                If there's something you didn't understand in my previous reply, please ask specific questions.

                                 

                                Once the schema is registered, you don't need to touch it anymore, except if it evolves.

                                That being done, create your validating function which will be no more than a simple wrapper for the schemaValidate() method.

                                • 13. Re: hi i am having issue in validating xml against xsd and inserting it into table....
                                  2737886

                                  Hi odie,

                                  1>  how will I check if the schema is register or not.

                                  2>  If its register then do I need to delete it and register again.

                                  3> check my anonymous block it is validating the XML but not fetching data from it ??
                                  ----------------------------------------------------------------------------------------------------------------------------------

                                  DECLARE
                                                                                          --   l_app_no_1, l_app_dt, l_src_cd
                                    l_app_no_1 VARCHAR2(20);
                                    l_app_dt DATE;
                                    l_src_cd VARCHAR2(20);
                                    l_xml xmltype;

                                    l_xsd XMLTYPE;

                                    l_v_schemaURL    VARCHAR2 (256) := 'NewBusinessApplication.xsd';


                                    schema_doesnt_exist exception;

                                    pragma exception_init (schema_doesnt_exist, -31000);


                                  BEGIN

                                   

                                    l_xsd := XMLTYPE('<?xml version="1.0" encoding="windows-1252" ?>
                                  <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.example.org"
                                              targetNamespace="http://www.example.org" elementFormDefault="qualified">
                                    <xsd:element name="NewBusinessApplication">
                                      <xsd:complexType>
                                        <xsd:sequence>
                                          <xsd:element name="TransactionHeader">
                                            <xsd:complexType>
                                              <xsd:sequence>
                                                <xsd:element name="TR_REF_NO" type="xsd:integer"/>
                                                <xsd:element name="EFF_DT" type="xsd:string"/>
                                                <xsd:element name="SRC_SYS_CD">
                                                  <xsd:complexType>
                                                    <xsd:simpleContent>
                                                      <xsd:extension base="xsd:string">
                                                        <xsd:attribute name="dmnSRC_SYSTEM" type="xsd:string"/>
                                                      </xsd:extension>
                                                    </xsd:simpleContent>
                                                  </xsd:complexType>
                                                </xsd:element>
                                              </xsd:sequence>
                                            </xsd:complexType>
                                          </xsd:element>
                                          <xsd:element name="ApplicationRq">
                                            <xsd:complexType>
                                              <xsd:sequence>
                                                <xsd:element name="ApplInfo">
                                                  <xsd:complexType>
                                                    <xsd:sequence>
                                                      <xsd:element name="APPLNO" type="xsd:string"/>
                                                      <xsd:element name="MED_NONMED">
                                                        <xsd:complexType>
                                                          <xsd:simpleContent>
                                                            <xsd:extension base="xsd:string">
                                                              <xsd:attribute name="dmnMED_NONMED" type="xsd:string"/>
                                                            </xsd:extension>
                                                          </xsd:simpleContent>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="APPL_DT" type="xsd:integer"/>
                                                      <xsd:element name="EFF_DT" type="xsd:integer"/>
                                                      <xsd:element name="APPL_RECV_DT" type="xsd:integer"/>
                                                    </xsd:sequence>
                                                  </xsd:complexType>
                                                </xsd:element>
                                                <xsd:element name="AgentInfo">
                                                  <xsd:complexType>
                                                    <xsd:sequence>
                                                      <xsd:element name="Agent">
                                                        <xsd:complexType>
                                                          <xsd:sequence>
                                                            <xsd:element name="AGT_CD" type="xsd:string"/>
                                                            <xsd:element name="FIRSTNAME" type="xsd:string"/>
                                                            <xsd:element name="LASTNAME" type="xsd:string"/>
                                                            <xsd:element name="AGT-LICNO" type="xsd:string"/>
                                                            <xsd:element name="TEAM" type="xsd:string"/>
                                                            <xsd:element name="TEAMLEADER" type="xsd:string"/>
                                                            <xsd:element name="TEAMLEADER-LICNO" type="xsd:string"/>
                                                          </xsd:sequence>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                    </xsd:sequence>
                                                  </xsd:complexType>
                                                </xsd:element>
                                                <xsd:element name="InsuredInfo">
                                                  <xsd:complexType>
                                                    <xsd:sequence>
                                                      <xsd:element name="PREFIX-TH">
                                                        <xsd:complexType>
                                                          <xsd:simpleContent>
                                                            <xsd:extension base="xsd:string">
                                                              <xsd:attribute name="dmnPERS_NAMEPREFIX" type="xsd:string"/>
                                                            </xsd:extension>
                                                          </xsd:simpleContent>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="FIRSTNAME-TH" type="xsd:string"/>
                                                      <xsd:element name="MIDNAME-TH" type="xsd:string"/>
                                                      <xsd:element name="LASTNAME-TH" type="xsd:string"/>
                                                      <xsd:element name="FORMERNAME" type="xsd:string"/>
                                                      <xsd:element name="PREFIX-EN">
                                                        <xsd:complexType>
                                                          <xsd:simpleContent>
                                                            <xsd:extension base="xsd:string">
                                                              <xsd:attribute name="dmnPERS_NAMEPREFIX-EN" type="xsd:string"/>
                                                            </xsd:extension>
                                                          </xsd:simpleContent>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="FIRSTNAME-EN" type="xsd:string"/>
                                                      <xsd:element name="MIDNAME-EN" type="xsd:string"/>
                                                      <xsd:element name="LASTNAME-EN" type="xsd:string"/>
                                                      <xsd:element name="BIRTH_DT" type="xsd:integer"/>
                                                      <xsd:element name="AGE" type="xsd:integer"/>
                                                      <xsd:element name="GENDER">
                                                        <xsd:complexType>
                                                          <xsd:simpleContent>
                                                            <xsd:extension base="xsd:string">
                                                              <xsd:attribute name="dmnPERNSEXCD" type="xsd:string"/>
                                                            </xsd:extension>
                                                          </xsd:simpleContent>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="MARITAL_STATUS">
                                                        <xsd:complexType>
                                                          <xsd:simpleContent>
                                                            <xsd:extension base="xsd:string">
                                                              <xsd:attribute name="dmnPERC_MAR_STAT_CD" type="xsd:string"/>
                                                            </xsd:extension>
                                                          </xsd:simpleContent>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="RACE">
                                                        <xsd:complexType>
                                                          <xsd:simpleContent>
                                                            <xsd:extension base="xsd:string">
                                                              <xsd:attribute name="dmnRACE" type="xsd:string"/>
                                                            </xsd:extension>
                                                          </xsd:simpleContent>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="RELIGION">
                                                        <xsd:complexType>
                                                          <xsd:simpleContent>
                                                            <xsd:extension base="xsd:string">
                                                              <xsd:attribute name="dmnRELIGION" type="xsd:string"/>
                                                            </xsd:extension>
                                                          </xsd:simpleContent>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="NATIONALITY">
                                                        <xsd:complexType>
                                                          <xsd:simpleContent>
                                                            <xsd:extension base="xsd:string">
                                                              <xsd:attribute name="dmnNATIONALITY" type="xsd:string"/>
                                                            </xsd:extension>
                                                          </xsd:simpleContent>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="ID-TYPE">
                                                        <xsd:complexType>
                                                          <xsd:simpleContent>
                                                            <xsd:extension base="xsd:string">
                                                              <xsd:attribute name="dmnIDTYPE" type="xsd:string"/>
                                                            </xsd:extension>
                                                          </xsd:simpleContent>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="ID-NUMBER" type="xsd:string"/>
                                                      <xsd:element name="ID-XPIR_DT" type="xsd:integer"/>
                                                      <xsd:element name="OCCUPATION">
                                                        <xsd:complexType>
                                                          <xsd:attribute name="dmnOCCUP_CD" type="xsd:string"/>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="OCCUP_CLASS">
                                                        <xsd:complexType>
                                                          <xsd:attribute name="dmnOCCUP_CLASS_CD" type="xsd:string"/>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="ANNUAL_INCOME" type="xsd:integer"/>
                                                      <xsd:element name="HEIGHT" type="xsd:integer"/>
                                                      <xsd:element name="WEIGHT" type="xsd:integer"/>
                                                    </xsd:sequence>
                                                  </xsd:complexType>
                                                </xsd:element>
                                                <xsd:element name="ProposerInfo">
                                                  <xsd:complexType>
                                                    <xsd:sequence>
                                                      <xsd:element name="PREFIX-TH">
                                                        <xsd:complexType>
                                                          <xsd:simpleContent>
                                                            <xsd:extension base="xsd:string">
                                                              <xsd:attribute name="dmnPERS_NAMEPREFIX" type="xsd:string"/>
                                                            </xsd:extension>
                                                          </xsd:simpleContent>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="FIRSTNAME-TH" type="xsd:string"/>
                                                      <xsd:element name="MIDNAME-TH" type="xsd:string"/>
                                                      <xsd:element name="LASTNAME-TH" type="xsd:string"/>
                                                      <xsd:element name="FORMERNAME" type="xsd:string"/>
                                                      <xsd:element name="PREFIX-EN">
                                                        <xsd:complexType>
                                                          <xsd:simpleContent>
                                                            <xsd:extension base="xsd:string">
                                                              <xsd:attribute name="dmnPERS_NAMEPREFIX-EN" type="xsd:string"/>
                                                            </xsd:extension>
                                                          </xsd:simpleContent>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="FIRSTNAME-EN" type="xsd:string"/>
                                                      <xsd:element name="MIDNAME-EN" type="xsd:string"/>
                                                      <xsd:element name="LASTNAME-EN" type="xsd:string"/>
                                                      <xsd:element name="BIRTH_DT" type="xsd:integer"/>
                                                      <xsd:element name="AGE" type="xsd:integer"/>
                                                      <xsd:element name="GENDER">
                                                        <xsd:complexType>
                                                          <xsd:simpleContent>
                                                            <xsd:extension base="xsd:string">
                                                              <xsd:attribute name="dmnPERNSEXCD" type="xsd:string"/>
                                                            </xsd:extension>
                                                          </xsd:simpleContent>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="MARITAL_STATUS">
                                                        <xsd:complexType>
                                                          <xsd:simpleContent>
                                                            <xsd:extension base="xsd:string">
                                                              <xsd:attribute name="dmnPERC_MAR_STAT_CD" type="xsd:string"/>
                                                            </xsd:extension>
                                                          </xsd:simpleContent>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="RACE">
                                                        <xsd:complexType>
                                                          <xsd:simpleContent>
                                                            <xsd:extension base="xsd:string">
                                                              <xsd:attribute name="dmnRACE" type="xsd:string"/>
                                                            </xsd:extension>
                                                          </xsd:simpleContent>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="RELIGION">
                                                        <xsd:complexType>
                                                          <xsd:simpleContent>
                                                            <xsd:extension base="xsd:string">
                                                              <xsd:attribute name="dmnRELIGION" type="xsd:string"/>
                                                            </xsd:extension>
                                                          </xsd:simpleContent>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="NATIONALITY">
                                                        <xsd:complexType>
                                                          <xsd:simpleContent>
                                                            <xsd:extension base="xsd:string">
                                                              <xsd:attribute name="dmnNATIONALITY" type="xsd:string"/>
                                                            </xsd:extension>
                                                          </xsd:simpleContent>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="ID_TYPE">
                                                        <xsd:complexType>
                                                          <xsd:simpleContent>
                                                            <xsd:extension base="xsd:string">
                                                              <xsd:attribute name="dmnIDTYPE" type="xsd:string"/>
                                                            </xsd:extension>
                                                          </xsd:simpleContent>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="ID_NUMBER" type="xsd:string"/>
                                                      <xsd:element name="ID_XPIR_DT" type="xsd:integer"/>
                                                      <xsd:element name="OCCUPATION">
                                                        <xsd:complexType>
                                                          <xsd:attribute name="dmnOCCUP_CD" type="xsd:string"/>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="OCCUP_CLASS">
                                                        <xsd:complexType>
                                                          <xsd:attribute name="dmnOCCUP_CLASS_CD" type="xsd:string"/>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="ANNUAL_INCOME" type="xsd:integer"/>
                                                    </xsd:sequence>
                                                  </xsd:complexType>
                                                </xsd:element>
                                                <xsd:element name="ContactInfo">
                                                  <xsd:complexType>
                                                    <xsd:sequence>
                                                      <xsd:element name="AddressInfo">
                                                        <xsd:complexType>
                                                          <xsd:sequence>
                                                            <xsd:element name="Address" maxOccurs="unbounded">
                                                              <xsd:complexType>
                                                                <xsd:sequence>
                                                                  <xsd:element name="ADDRTYPE">
                                                                    <xsd:complexType>
                                                                      <xsd:simpleContent>
                                                                        <xsd:extension base="xsd:string">
                                                                          <xsd:attribute name="dmnADDR_ADDRTYPE" type="xsd:integer"/>
                                                                        </xsd:extension>
                                                                      </xsd:simpleContent>
                                                                    </xsd:complexType>
                                                                  </xsd:element>
                                                                  <xsd:element name="PRIMARY_CONTACT" type="xsd:integer"/>
                                                                </xsd:sequence>
                                                              </xsd:complexType>
                                                            </xsd:element>
                                                          </xsd:sequence>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="PhoneInfo">
                                                        <xsd:complexType>
                                                          <xsd:sequence>
                                                            <xsd:element name="Phone" maxOccurs="unbounded">
                                                              <xsd:complexType>
                                                                <xsd:sequence>
                                                                  <xsd:element name="PHONETYPE">
                                                                    <xsd:complexType>
                                                                      <xsd:simpleContent>
                                                                        <xsd:extension base="xsd:string">
                                                                          <xsd:attribute name="dmnADRP_PHONETYPE" type="xsd:integer"/>
                                                                        </xsd:extension>
                                                                      </xsd:simpleContent>
                                                                    </xsd:complexType>
                                                                  </xsd:element>
                                                                  <xsd:element name="PHONENUM" type="xsd:integer"/>
                                                                  <xsd:element name="PRIMARY_CONTACT" type="xsd:integer"/>
                                                                </xsd:sequence>
                                                              </xsd:complexType>
                                                            </xsd:element>
                                                          </xsd:sequence>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="EmailInfo">
                                                        <xsd:complexType>
                                                          <xsd:sequence>
                                                            <xsd:element name="Email" maxOccurs="unbounded">
                                                              <xsd:complexType>
                                                                <xsd:sequence>
                                                                  <xsd:element name="EMAILTYPE">
                                                                    <xsd:complexType>
                                                                      <xsd:simpleContent>
                                                                        <xsd:extension base="xsd:string">
                                                                          <xsd:attribute name="dmnADRO_EMAILTYPE" type="xsd:integer"/>
                                                                        </xsd:extension>
                                                                      </xsd:simpleContent>
                                                                    </xsd:complexType>
                                                                  </xsd:element>
                                                                  <xsd:element name="EMAILDATA" type="xsd:string"/>
                                                                  <xsd:element name="PRIMARY_CONTACT" type="xsd:integer"/>
                                                                </xsd:sequence>
                                                              </xsd:complexType>
                                                            </xsd:element>
                                                          </xsd:sequence>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                    </xsd:sequence>
                                                  </xsd:complexType>
                                                </xsd:element>
                                                <xsd:element name="PolicyInfo">
                                                  <xsd:complexType>
                                                    <xsd:sequence>
                                                      <xsd:element name="BASE-PLAN_CD" type="xsd:string"/>
                                                      <xsd:element name="BASE-PLAN_NM" type="xsd:string"/>
                                                      <xsd:element name="MODEOFPAYMENT">
                                                        <xsd:complexType>
                                                          <xsd:simpleContent>
                                                            <xsd:extension base="xsd:string">
                                                              <xsd:attribute name="dmnZZFREQUENCY" type="xsd:string"/>
                                                            </xsd:extension>
                                                          </xsd:simpleContent>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="BASE-SA" type="xsd:float"/>
                                                      <xsd:element name="BASE-PREMIUM" type="xsd:float"/>
                                                      <xsd:element name="BASE-MODALPREMIUM" type="xsd:float"/>
                                                      <xsd:element name="AYFP" type="xsd:float"/>
                                                      <xsd:element name="RiderInfo">
                                                        <xsd:complexType>
                                                          <xsd:sequence>
                                                            <xsd:element name="Rider" maxOccurs="unbounded">
                                                              <xsd:complexType>
                                                                <xsd:sequence>
                                                                  <xsd:element name="RIDER-CODE" type="xsd:string"/>
                                                                  <xsd:element name="RIDER-NAME" type="xsd:string"/>
                                                                  <xsd:element name="RIDER-SA" type="xsd:float"/>
                                                                  <xsd:element name="RIDER-PREMIUM" type="xsd:float"/>
                                                                  <xsd:element name="RIDER-MODALPREMIUM" type="xsd:float"/>
                                                                </xsd:sequence>
                                                              </xsd:complexType>
                                                            </xsd:element>
                                                          </xsd:sequence>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                    </xsd:sequence>
                                                  </xsd:complexType>
                                                </xsd:element>
                                                <xsd:element name="PaymentInfo">
                                                  <xsd:complexType>
                                                    <xsd:sequence>
                                                      <xsd:element name="FirstYear">
                                                        <xsd:complexType>
                                                          <xsd:sequence>
                                                            <xsd:element name="TMP_RECEIPT_NO" type="xsd:string"/>
                                                            <xsd:element name="TOTAL_PYMT_AMT" type="xsd:float"/>
                                                            <xsd:element name="Payment" maxOccurs="unbounded">
                                                              <xsd:complexType>
                                                                <xsd:sequence>
                                                                  <xsd:element name="PAYER">
                                                                    <xsd:complexType>
                                                                      <xsd:simpleContent>
                                                                        <xsd:extension base="xsd:string">
                                                                          <xsd:attribute name="dmnPAYRTYPE" type="xsd:integer"/>
                                                                        </xsd:extension>
                                                                      </xsd:simpleContent>
                                                                    </xsd:complexType>
                                                                  </xsd:element>
                                                                  <xsd:element name="PYMT-TYPE">
                                                                    <xsd:complexType>
                                                                      <xsd:simpleContent>
                                                                        <xsd:extension base="xsd:string">
                                                                          <xsd:attribute name="dmnPaymentType" type="xsd:integer"/>
                                                                        </xsd:extension>
                                                                      </xsd:simpleContent>
                                                                    </xsd:complexType>
                                                                  </xsd:element>
                                                                  <xsd:element name="PYMT-AMT" type="xsd:float"/>
                                                                  <xsd:element name="PYMT-Channel">
                                                                    <xsd:complexType>
                                                                      <xsd:sequence>
                                                                        <xsd:element name="CreditCard" minOccurs="0">
                                                                          <xsd:complexType>
                                                                            <xsd:sequence>
                                                                              <xsd:element name="CARD-TYPE">
                                                                                <xsd:complexType>
                                                                                  <xsd:simpleContent>
                                                                                    <xsd:extension base="xsd:string">
                                                                                      <xsd:attribute name="dmnCardType" type="xsd:integer"/>
                                                                                    </xsd:extension>
                                                                                  </xsd:simpleContent>
                                                                                </xsd:complexType>
                                                                              </xsd:element>
                                                                              <xsd:element name="CARD-NO" type="xsd:integer"/>
                                                                              <xsd:element name="CARD-HOLDERNAME" type="xsd:string"/>
                                                                              <xsd:element name="CARD-BANKNM" type="xsd:string"/>
                                                                              <xsd:element name="CARD-EXPIRYDATE" type="xsd:string"/>
                                                                              <xsd:element name="CARD-AUTHCODE"/>
                                                                              <xsd:element name="CARD-AUTHDATE"/>
                                                                            </xsd:sequence>
                                                                          </xsd:complexType>
                                                                        </xsd:element>
                                                                        <xsd:element name="Bank" minOccurs="0">
                                                                          <xsd:complexType>
                                                                            <xsd:sequence>
                                                                              <xsd:element name="BANK-CODE"/>
                                                                              <xsd:element name="BANK-BRANCH"/>
                                                                              <xsd:element name="BANK-ACCNO"/>
                                                                              <xsd:element name="BANK-ACCNAME"/>
                                                                            </xsd:sequence>
                                                                          </xsd:complexType>
                                                                        </xsd:element>
                                                                        <xsd:element name="Cheque" minOccurs="0">
                                                                          <xsd:complexType>
                                                                            <xsd:sequence>
                                                                              <xsd:element name="CHQ-NO"/>
                                                                              <xsd:element name="CHQ-DATE"/>
                                                                              <xsd:element name="CHQ-ISSUEBANK"/>
                                                                            </xsd:sequence>
                                                                          </xsd:complexType>
                                                                        </xsd:element>
                                                                      </xsd:sequence>
                                                                    </xsd:complexType>
                                                                  </xsd:element>
                                                                </xsd:sequence>
                                                              </xsd:complexType>
                                                            </xsd:element>
                                                          </xsd:sequence>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                      <xsd:element name="RenewalPayment">
                                                        <xsd:complexType>
                                                          <xsd:sequence>
                                                            <xsd:element name="PAYER">
                                                              <xsd:complexType>
                                                                <xsd:simpleContent>
                                                                  <xsd:extension base="xsd:string">
                                                                    <xsd:attribute name="dmnPAYRTYPE" type="xsd:integer"/>
                                                                  </xsd:extension>
                                                                </xsd:simpleContent>
                                                              </xsd:complexType>
                                                            </xsd:element>
                                                            <xsd:element name="DDCHANNEL">
                                                              <xsd:complexType>
                                                                <xsd:simpleContent>
                                                                  <xsd:extension base="xsd:string">
                                                                    <xsd:attribute name="dmnPaymentType" type="xsd:integer"/>
                                                                  </xsd:extension>
                                                                </xsd:simpleContent>
                                                              </xsd:complexType>
                                                            </xsd:element>
                                                            <xsd:element name="DDCDetails">
                                                              <xsd:complexType>
                                                                <xsd:sequence>
                                                                  <xsd:element name="CARD-TYPE">
                                                                    <xsd:complexType>
                                                                      <xsd:simpleContent>
                                                                        <xsd:extension base="xsd:string">
                                                                          <xsd:attribute name="dmnCardType" type="xsd:integer"/>
                                                                        </xsd:extension>
                                                                      </xsd:simpleContent>
                                                                    </xsd:complexType>
                                                                  </xsd:element>
                                                                  <xsd:element name="CARD-NO" type="xsd:integer"/>
                                                                  <xsd:element name="CARD-HOLDERNAME" type="xsd:string"/>
                                                                  <xsd:element name="CARD-BANKNM" type="xsd:string"/>
                                                                  <xsd:element name="CARD-EXPIRYDATE" type="xsd:string"/>
                                                                </xsd:sequence>
                                                              </xsd:complexType>
                                                            </xsd:element>
                                                            <xsd:element name="DDADetails">
                                                              <xsd:complexType>
                                                                <xsd:sequence>
                                                                  <xsd:element name="BANK-ACCTYPE">
                                                                    <xsd:complexType>
                                                                      <xsd:simpleContent>
                                                                        <xsd:extension base="xsd:string">
                                                                          <xsd:attribute name="dmnBNK_TYP_CD" type="xsd:integer"/>
                                                                        </xsd:extension>
                                                                      </xsd:simpleContent>
                                                                    </xsd:complexType>
                                                                  </xsd:element>
                                                                  <xsd:element name="BANK-ACCNO"/>
                                                                  <xsd:element name="BANK-ACCNAME"/>
                                                                  <xsd:element name="BANK-BRANCH"/>
                                                                </xsd:sequence>
                                                              </xsd:complexType>
                                                            </xsd:element>
                                                          </xsd:sequence>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                    </xsd:sequence>
                                                  </xsd:complexType>
                                                </xsd:element>
                                                <xsd:element name="BeneficiaryInfo">
                                                  <xsd:complexType>
                                                    <xsd:sequence>
                                                      <xsd:element name="Beneficiary" maxOccurs="unbounded">
                                                        <xsd:complexType>
                                                          <xsd:sequence>
                                                            <xsd:element name="RELATIONSHIP">
                                                              <xsd:complexType>
                                                                <xsd:simpleContent>
                                                                  <xsd:extension base="xsd:string">
                                                                    <xsd:attribute name="dmnBENE_RELT_CD" type="xsd:integer"/>
                                                                  </xsd:extension>
                                                                </xsd:simpleContent>
                                                              </xsd:complexType>
                                                            </xsd:element>
                                                            <xsd:element name="FIRSTNAME" type="xsd:string"/>
                                                            <xsd:element name="MIDNAME" type="xsd:string"/>
                                                            <xsd:element name="LASTNAME" type="xsd:string"/>
                                                            <xsd:element name="PERCENTAGE" type="xsd:integer"/>
                                                            <xsd:element name="BIRTH_DT" type="xsd:integer"/>
                                                            <xsd:element name="GENDER">
                                                              <xsd:complexType>
                                                                <xsd:simpleContent>
                                                                  <xsd:extension base="xsd:string">
                                                                    <xsd:attribute name="dmnPERNSEXCD" type="xsd:string"/>
                                                                  </xsd:extension>
                                                                </xsd:simpleContent>
                                                              </xsd:complexType>
                                                            </xsd:element>
                                                            <xsd:element name="NATLIDNO" type="xsd:string"/>
                                                          </xsd:sequence>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                    </xsd:sequence>
                                                  </xsd:complexType>
                                                </xsd:element>
                                                <xsd:element name="Questionnaire">
                                                  <xsd:complexType>
                                                    <xsd:sequence>
                                                      <xsd:element name="HealthDeclaration">
                                                        <xsd:complexType>
                                                          <xsd:sequence>
                                                            <xsd:element name="Question" maxOccurs="unbounded">
                                                              <xsd:complexType>
                                                                <xsd:sequence>
                                                                  <xsd:element name="QUESTION_ID" type="xsd:string"/>
                                                                  <xsd:element name="ANSWER">
                                                                    <xsd:complexType>
                                                                      <xsd:simpleContent>
                                                                        <xsd:extension base="xsd:string">
                                                                          <xsd:attribute name="dmnZZYESNO_NUM" type="xsd:string"/>
                                                                        </xsd:extension>
                                                                      </xsd:simpleContent>
                                                                    </xsd:complexType>
                                                                  </xsd:element>
                                                                </xsd:sequence>
                                                              </xsd:complexType>
                                                            </xsd:element>
                                                          </xsd:sequence>
                                                        </xsd:complexType>
                                                      </xsd:element>
                                                    </xsd:sequence>
                                                  </xsd:complexType>
                                                </xsd:element>
                                              </xsd:sequence>
                                            </xsd:complexType>
                                          </xsd:element>
                                          <xsd:element name="ApplicationRs">
                                            <xsd:complexType>
                                              <xsd:sequence>
                                                <xsd:element name="Reject-Reason"/>
                                              </xsd:sequence>
                                            </xsd:complexType>
                                          </xsd:element>
                                        </xsd:sequence>
                                        <xsd:attribute name="xsi" type="xsd:string"/>
                                        <xsd:attribute name="schemaLocation" type="xsd:string"/>
                                      </xsd:complexType>
                                    </xsd:element>
                                  </xsd:schema>
                                  ');

                                   


                                    begin

                                       DBMS_XMLSCHEMA.deleteSchema(l_v_schemaURL, dbms_xmlSchema.DELETE_CASCADE_FORCE);

                                    exception

                                      when schema_doesnt_exist then

                                        null;

                                    end;

                                   


                                    DBMS_XMLSCHEMA.REGISTERSCHEMA(

                                      schemaurl => l_v_schemaURL

                                    , schemadoc => l_xsd

                                    , local     => TRUE

                                    , genTypes  => FALSE

                                    , genbean   => FALSE

                                    , genTables => FALSE

                                    );

                                   

                                    l_xml := XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
                                  <NewBusinessApplication xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                                                          xsi:schemaLocation="http://www.example.org NewBusinessApplication.xsd"
                                                          xmlns="http://www.example.org">
                                    <TransactionHeader>
                                      <TR_REF_NO>000009812681</TR_REF_NO>
                                      <EFF_DT>20140703094140.572[-4:US/Eastern]</EFF_DT>
                                      <SRC_SYS_CD dmnSRC_SYSTEM="TSS">Tele Support System</SRC_SYS_CD>
                                    </TransactionHeader>
                                    <ApplicationRq>
                                      <ApplInfo>
                                        <APPLNO>A00018L</APPLNO>
                                        <MED_NONMED dmnMED_NONMED="M">Medical</MED_NONMED>
                                        <APPL_DT>20140714</APPL_DT>
                                        <EFF_DT>20140714</EFF_DT>
                                        <APPL_RECV_DT>20140714</APPL_RECV_DT>
                                      </ApplInfo>
                                      <AgentInfo>
                                        <!--Repeat the Below nodes for more than one agent -->
                                        <Agent>
                                          <AGT_CD>AGN0000001</AGT_CD>
                                          <FIRSTNAME>James</FIRSTNAME>
                                          <LASTNAME>Whorphin</LASTNAME>
                                          <AGT-LICNO>BKK1010090009</AGT-LICNO>
                                          <TEAM>Warriors</TEAM>
                                          <TEAMLEADER>Suriya Thapa</TEAMLEADER>
                                          <TEAMLEADER-LICNO>BKK909988999</TEAMLEADER-LICNO>
                                        </Agent>
                                      </AgentInfo>
                                      <InsuredInfo>
                                        <PREFIX-TH dmnPERS_NAMEPREFIX="XX">Dr.</PREFIX-TH>
                                        <FIRSTNAME-TH>XXXXXXX</FIRSTNAME-TH>
                                        <MIDNAME-TH>XXXXXX</MIDNAME-TH>
                                        <LASTNAME-TH>XXXXXX</LASTNAME-TH>
                                        <FORMERNAME>XXXXXX</FORMERNAME>
                                        <PREFIX-EN dmnPERS_NAMEPREFIX-EN="XX">Dr.</PREFIX-EN>
                                        <FIRSTNAME-EN>YYYYYY</FIRSTNAME-EN>
                                        <MIDNAME-EN>YYYYYY</MIDNAME-EN>
                                        <LASTNAME-EN>YYYYYY</LASTNAME-EN>
                                        <BIRTH_DT>19800101</BIRTH_DT>
                                        <AGE>33</AGE>
                                        <GENDER dmnPERNSEXCD="XX">Male</GENDER>
                                        <MARITAL_STATUS dmnPERC_MAR_STAT_CD="XX">Single</MARITAL_STATUS>
                                        <RACE dmnRACE="XX">ZZZ</RACE>
                                        <RELIGION dmnRELIGION="H">HINDU</RELIGION>
                                        <NATIONALITY dmnNATIONALITY="TH">THAI</NATIONALITY>
                                        <ID-TYPE dmnIDTYPE="TI">Thai National ID</ID-TYPE>
                                        <ID-NUMBER>AJMPK0001J</ID-NUMBER>
                                        <ID-XPIR_DT>20200101</ID-XPIR_DT>
                                        <OCCUPATION dmnOCCUP_CD="XX"/>
                                        <OCCUP_CLASS dmnOCCUP_CLASS_CD="XX"/>
                                        <ANNUAL_INCOME>100000</ANNUAL_INCOME>
                                        <HEIGHT>175</HEIGHT>
                                        <WEIGHT>80</WEIGHT>
                                      </InsuredInfo>
                                      <ProposerInfo>
                                        <PREFIX-TH dmnPERS_NAMEPREFIX="XX">Dr.</PREFIX-TH>
                                        <FIRSTNAME-TH>XXXXXXX</FIRSTNAME-TH>
                                        <MIDNAME-TH>XXXXXX</MIDNAME-TH>
                                        <LASTNAME-TH>XXXXXX</LASTNAME-TH>
                                        <FORMERNAME>XXXXXX</FORMERNAME>
                                        <PREFIX-EN dmnPERS_NAMEPREFIX-EN="XX">Dr.</PREFIX-EN>
                                        <FIRSTNAME-EN>YYYYYY</FIRSTNAME-EN>
                                        <MIDNAME-EN>YYYYYY</MIDNAME-EN>
                                        <LASTNAME-EN>YYYYYY</LASTNAME-EN>
                                        <BIRTH_DT>19800101</BIRTH_DT>
                                        <AGE>33</AGE>
                                        <GENDER dmnPERNSEXCD="XX">Male</GENDER>
                                        <MARITAL_STATUS dmnPERC_MAR_STAT_CD="XX">Single</MARITAL_STATUS>
                                        <RACE dmnRACE="XX">ZZZ</RACE>
                                        <RELIGION dmnRELIGION="H">HINDU</RELIGION>
                                        <NATIONALITY dmnNATIONALITY="TH">THAI</NATIONALITY>
                                        <ID_TYPE dmnIDTYPE="TI">Thai National ID</ID_TYPE>
                                        <ID_NUMBER>AJMPK0001J</ID_NUMBER>
                                        <ID_XPIR_DT>20200101</ID_XPIR_DT>
                                        <OCCUPATION dmnOCCUP_CD="XX"/>
                                        <OCCUP_CLASS dmnOCCUP_CLASS_CD="XX"/>
                                        <ANNUAL_INCOME>100000</ANNUAL_INCOME>
                                      </ProposerInfo>
                                      <ContactInfo>
                                        <AddressInfo>
                                          <!--Repeat the Below nodes for each address type -->
                                          <Address>
                                            <ADDRTYPE dmnADDR_ADDRTYPE="01">Home</ADDRTYPE>
                                            <PRIMARY_CONTACT>0</PRIMARY_CONTACT>
                                          </Address>
                                          <Address>
                                            <ADDRTYPE dmnADDR_ADDRTYPE="02">Office</ADDRTYPE>
                                            <PRIMARY_CONTACT>1</PRIMARY_CONTACT>
                                          </Address>
                                        </AddressInfo>
                                        <PhoneInfo>
                                          <!--Repeat the Below nodes for each phone type -->
                                          <Phone>
                                            <PHONETYPE dmnADRP_PHONETYPE="01">Mobile</PHONETYPE>
                                            <PHONENUM>9887666789</PHONENUM>
                                            <PRIMARY_CONTACT>1</PRIMARY_CONTACT>
                                          </Phone>
                                          <Phone>
                                            <PHONETYPE dmnADRP_PHONETYPE="02">Home</PHONETYPE>
                                            <PHONENUM>9887666789</PHONENUM>
                                            <PRIMARY_CONTACT>0</PRIMARY_CONTACT>
                                          </Phone>
                                        </PhoneInfo>
                                        <EmailInfo>
                                          <!--Repeat the Below nodes for each email type -->
                                          <Email>
                                            <EMAILTYPE dmnADRO_EMAILTYPE="01">Email1</EMAILTYPE>
                                            <EMAILDATA>abc@yahoo.co.in</EMAILDATA>
                                            <PRIMARY_CONTACT>0</PRIMARY_CONTACT>
                                          </Email>
                                          <Email>
                                            <EMAILTYPE dmnADRO_EMAILTYPE="02">Email2</EMAILTYPE>
                                            <EMAILDATA>abc@yahoo.co.in</EMAILDATA>
                                            <PRIMARY_CONTACT>1</PRIMARY_CONTACT>
                                          </Email>
                                        </EmailInfo>
                                      </ContactInfo>
                                      <PolicyInfo>
                                        <BASE-PLAN_CD>STG102</BASE-PLAN_CD>
                                        <BASE-PLAN_NM>Smart Term Gold 10</BASE-PLAN_NM>
                                        <MODEOFPAYMENT dmnZZFREQUENCY="M">Monthly</MODEOFPAYMENT>
                                        <BASE-SA>100000.00</BASE-SA>
                                        <BASE-PREMIUM>12000.00</BASE-PREMIUM>
                                        <BASE-MODALPREMIUM>1000.00</BASE-MODALPREMIUM>
                                        <AYFP>13200.00</AYFP>
                                        <RiderInfo>
                                          <Rider>
                                            <RIDER-CODE>ME</RIDER-CODE>
                                            <RIDER-NAME>Medical Expense</RIDER-NAME>
                                            <RIDER-SA>20000.00</RIDER-SA>
                                            <RIDER-PREMIUM>600.00</RIDER-PREMIUM>
                                            <RIDER-MODALPREMIUM>50.00</RIDER-MODALPREMIUM>
                                          </Rider>
                                          <Rider>
                                            <RIDER-CODE>HAB</RIDER-CODE>
                                            <RIDER-NAME>Hospital Admission Benefit</RIDER-NAME>
                                            <RIDER-SA>20000.00</RIDER-SA>
                                            <RIDER-PREMIUM>600.00</RIDER-PREMIUM>
                                            <RIDER-MODALPREMIUM>50.00</RIDER-MODALPREMIUM>
                                          </Rider>
                                        </RiderInfo>
                                      </PolicyInfo>
                                      <PaymentInfo>
                                        <FirstYear>
                                          <TMP_RECEIPT_NO>TEMP0001</TMP_RECEIPT_NO>
                                          <TOTAL_PYMT_AMT>13200.00</TOTAL_PYMT_AMT>
                                          <!--Repeat the Below nodes for payment through Multiple channels -->
                                          <Payment>
                                            <PAYER dmnPAYRTYPE="01">Insured</PAYER>
                                            <PYMT-TYPE dmnPaymentType="01">Credit Card</PYMT-TYPE>
                                            <PYMT-AMT>13200.00</PYMT-AMT>
                                            <PYMT-Channel>
                                              <CreditCard>
                                                <CARD-TYPE dmnCardType="01">Visa</CARD-TYPE>
                                                <CARD-NO>9890989098909877</CARD-NO>
                                                <CARD-HOLDERNAME>RICKY WHORPHIN</CARD-HOLDERNAME>
                                                <CARD-BANKNM>Standard Chartered Bank</CARD-BANKNM>
                                                <CARD-EXPIRYDATE>08/14</CARD-EXPIRYDATE>
                                                <CARD-AUTHCODE/>
                                                <CARD-AUTHDATE/>
                                              </CreditCard>
                                            </PYMT-Channel>
                                          </Payment>
                                          <Payment>
                                            <PAYER dmnPAYRTYPE="01">Insured</PAYER>
                                            <PYMT-TYPE dmnPaymentType="02">Deposit Account</PYMT-TYPE>
                                            <PYMT-AMT>13200.00</PYMT-AMT>
                                            <PYMT-Channel>
                                              <Bank>
                                                <BANK-CODE/>
                                                <BANK-BRANCH/>
                                                <BANK-ACCNO/>
                                                <BANK-ACCNAME/>
                                              </Bank>
                                            </PYMT-Channel>
                                          </Payment>
                                          <Payment>
                                            <PAYER dmnPAYRTYPE="01">Insured</PAYER>
                                            <PYMT-TYPE dmnPaymentType="03">Cheque</PYMT-TYPE>
                                            <PYMT-AMT>13200.00</PYMT-AMT>
                                            <PYMT-Channel>
                                              <Cheque>
                                                <CHQ-NO/>
                                                <CHQ-DATE/>
                                                <CHQ-ISSUEBANK/>
                                              </Cheque>
                                            </PYMT-Channel>
                                          </Payment>
                                        </FirstYear>
                                        <RenewalPayment>
                                          <PAYER dmnPAYRTYPE="01">Insured</PAYER>
                                          <DDCHANNEL dmnPaymentType="01">DDC</DDCHANNEL>
                                          <DDCDetails>
                                            <CARD-TYPE dmnCardType="01">Visa</CARD-TYPE>
                                            <CARD-NO>9890989098909877</CARD-NO>
                                            <CARD-HOLDERNAME>RICKY WHORPHIN</CARD-HOLDERNAME>
                                            <CARD-BANKNM>Standard Chartered Bank</CARD-BANKNM>
                                            <CARD-EXPIRYDATE>08/14</CARD-EXPIRYDATE>
                                          </DDCDetails>
                                          <DDADetails>
                                            <BANK-ACCTYPE dmnBNK_TYP_CD="01">Saving</BANK-ACCTYPE>
                                            <BANK-ACCNO/>
                                            <BANK-ACCNAME/>
                                            <BANK-BRANCH/>
                                          </DDADetails>
                                        </RenewalPayment>
                                      </PaymentInfo>
                                      <BeneficiaryInfo>
                                        <!--Repeat the Below nodes for each beneficiary and sum of pct should be 100 -->
                                        <Beneficiary>
                                          <RELATIONSHIP dmnBENE_RELT_CD="01">Spouse</RELATIONSHIP>
                                          <FIRSTNAME>XXXX</FIRSTNAME>
                                          <MIDNAME>XXXXX</MIDNAME>
                                          <LASTNAME>XXXX</LASTNAME>
                                          <PERCENTAGE>50</PERCENTAGE>
                                          <BIRTH_DT>19800101</BIRTH_DT>
                                          <GENDER dmnPERNSEXCD="XX">Female</GENDER>
                                          <NATLIDNO>TH000001</NATLIDNO>
                                        </Beneficiary>
                                        <Beneficiary>
                                          <RELATIONSHIP dmnBENE_RELT_CD="02">Child</RELATIONSHIP>
                                          <FIRSTNAME>XXXX</FIRSTNAME>
                                          <MIDNAME>XXXXX</MIDNAME>
                                          <LASTNAME>XXXX</LASTNAME>
                                          <PERCENTAGE>50</PERCENTAGE>
                                          <BIRTH_DT>19800101</BIRTH_DT>
                                          <GENDER dmnPERNSEXCD="XX">Male</GENDER>
                                          <NATLIDNO>TH000002</NATLIDNO>
                                        </Beneficiary>
                                      </BeneficiaryInfo>
                                      <Questionnaire>
                                        <HealthDeclaration>
                                          <Question>
                                            <QUESTION_ID>Q001</QUESTION_ID>
                                            <ANSWER dmnZZYESNO_NUM="N">No</ANSWER>
                                          </Question>
                                          <Question>
                                            <QUESTION_ID>Q002</QUESTION_ID>
                                            <ANSWER dmnZZYESNO_NUM="N">No</ANSWER>
                                          </Question>
                                          <Question>
                                            <QUESTION_ID>Q003</QUESTION_ID>
                                            <ANSWER dmnZZYESNO_NUM="N">No</ANSWER>
                                          </Question>
                                          <Question>
                                            <QUESTION_ID>Q004</QUESTION_ID>
                                            <ANSWER dmnZZYESNO_NUM="N">No</ANSWER>
                                          </Question>
                                          <Question>
                                            <QUESTION_ID>Q005</QUESTION_ID>
                                            <ANSWER dmnZZYESNO_NUM="N">No</ANSWER>
                                          </Question>
                                        </HealthDeclaration>
                                      </Questionnaire>
                                    </ApplicationRq>
                                    <ApplicationRs>
                                      <Reject-Reason/>
                                    </ApplicationRs>
                                  </NewBusinessApplication>
                                  ') ;

                                   

                                     if l_xml.isSchemaValid() = 1 then

                                       dbms_output.put_line('Schema is Valid');
                                       SELECT extractvalue(Value(p),'NewBusinessApplication/ApplicationRq/ApplInfo/APPLNO/text()')  AS applno
                                           ,extractvalue(Value(p),'NewBusinessApplication/ApplicationRq/ApplInfo/APPL_DT/text()') AS appl_date
                                           ,extractvalue(Value(p),'NewBusinessApplication/TransactionHeader/SRC_SYS_CD/text()')   AS src_cd
                                     INTO l_app_no_1, l_app_dt, l_src_cd
                                     FROM TABLE (xmlsequence( EXTRACT(l_xml,'NewBusinessApplication') )) p;
                                     Dbms_Output.put_line(l_app_no_1||' '||l_app_dt||' '||l_src_cd);


                                     else

                                       l_xml.schemaValidate();

                                     end if;


                                  END;

                                  • 14. Re: Re: hi i am having issue in validating xml against xsd and inserting it into table....
                                    odie_63

                                    Looks like you've not read or understand what Marco and I have said so far...

                                    1>  how will I check if the schema is register or not.

                                    You already know that... Query ALL_XML_SCHEMAS dictionary view.

                                     

                                    2>  If its register then do I need to delete it and register again.

                                    I've already told you that twice : NO!

                                    You register the schema ONCE, then you can use it to validate XML documents as many times as you want.

                                    3> check my anonymous block it is validating the XML but not fetching data from it ??

                                    See the complete example below.

                                     

                                    For convenience, I've stored both the schema and the sample XML in the filesystem (Oracle directory XML_DIR).

                                     

                                    1- Schema registration

                                     

                                    Again, you do that once, as a deployment step :

                                     

                                    DECLARE
                                    
                                      l_xsd     xmltype := xmltype(bfilename('XML_DIR','NewBusinessApplication.xsd'), nls_charset_id('WE8MSWIN1252'));
                                    
                                    BEGIN
                                    
                                      dbms_xmlschema.registerSchema(
                                        schemaurl => 'NewBusinessApplication.xsd'
                                      , schemadoc => l_xsd
                                      , local     => true
                                      , genTypes  => false
                                      , genTables => false
                                      , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
                                      , options => dbms_xmlschema.REGISTER_BINARYXML
                                      );
                                    
                                    END;
                                    /
                                    

                                     

                                    2- Create a table with a schema-based XMLType column, it'll boost query performance and automatically validate new XML documents against the schema :

                                     

                                    create table business_app_xml (
                                      filename varchar2(256) not null
                                    , doc      xmltype
                                    )
                                    xmltype column doc store as securefile binary xml
                                    xmlschema "NewBusinessApplication.xsd" element "NewBusinessApplication" ;
                                    

                                     

                                     

                                    You can pack the following steps in a procedure if you need so.

                                     

                                    3- Insert a sample document :

                                     

                                    insert into business_app_xml (filename, doc)
                                    values (
                                      'sample.xml'
                                    , xmltype(bfilename('XML_DIR','sample.xml'), nls_charset_id('AL32UTF8'))
                                    );
                                    

                                     

                                    If the input XML is not valid as per the XSD, an exception will be raised, which you can catch and report to the caller.

                                     

                                    4- Extract some values :

                                     

                                    Note : extractvalue, extract, xmlsequence are deprecated functions. Use XMLTable and XMLNamespaces clause to handle namespace declarations.

                                     

                                    SQL> select x.*
                                      2  from business_app_xml t
                                      3     , xmltable(
                                      4         xmlnamespaces(default 'http://www.example.org')
                                      5       , '/NewBusinessApplication'
                                      6         passing t.doc
                                      7         columns applno       varchar2(10) path 'ApplicationRq/ApplInfo/APPLNO'
                                      8               , appl_date    date         path 'ApplicationRq/ApplInfo/APPL_DT'
                                      9               , src_sys_cd   varchar2(30) path 'TransactionHeader/SRC_SYS_CD'
                                     10       ) x
                                     11  ;
                                    
                                    APPLNO     APPL_DATE   SRC_SYS_CD
                                    ---------- ----------- ------------------------------
                                    A00018L    14/07/2014  Tele Support System
                                    
                                    1 2 Previous Next