Skip to Main Content
  • Questions
  • Generate Create Table statements from xml file

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Fahd.

Asked: January 16, 2023 - 5:55 am UTC

Last updated: January 17, 2023 - 1:39 am UTC

Version: 19c

Viewed 1000+ times

You Asked

Dear Ask Tom Team,

below is the XML script containing tables names, column names and datatype, i need to get create table statements automatically from below xml.
is there any way to do it ? e.g i pass this xml to some function/procedure etc and it returns me create tables statements ?

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="msisdnasusername" type="xs:long"/>
  <xs:element name="bvs" type="xs:string"/>
  <xs:element name="accountholder">
    <xs:complexType>
      <xs:sequence>
        <xs:element type="xs:string" name="GvnNm"/>
        <xs:element type="xs:string" name="Nm"/>
        <xs:element type="xs:string" name="Gndr"/>
        <xs:element type="xs:date" name="BirthDt"/>
        <xs:element type="xs:string" name="CityOfBirth"/>
        <xs:element name="CtryAndResdtlSts">
          <xs:complexType>
            <xs:sequence>
              <xs:element type="xs:string" name="ResdtlSts"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="PstlAdr">
          <xs:complexType>
            <xs:sequence>
              <xs:element type="xs:string" name="AdrTp"/>
              <xs:element type="xs:string" name="MlngInd"/>
              <xs:element type="xs:string" name="RegnAdrInd"/>
              <xs:element name="NmAndAdr">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="Adr">
                      <xs:complexType>
                        <xs:sequence>
                          <xs:element type="xs:string" name="AdrTp"/>
                          <xs:element type="xs:string" name="AdrLine" maxOccurs="unbounded" minOccurs="0"/>
                          <xs:element type="xs:string" name="CtrySubDvsn"/>
                          <xs:element type="xs:string" name="Ctry"/>
                        </xs:sequence>
                      </xs:complexType>
                    </xs:element>
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element type="xs:string" name="PmryComAdr"/>
        <xs:element type="xs:string" name="ScndryComAdr"/>
        <xs:element name="OthrId">
          <xs:complexType>
            <xs:sequence>
              <xs:element type="xs:long" name="Id"/>
              <xs:element type="xs:string" name="IdTp"/>
              <xs:element type="xs:date" name="IsseDt"/>
              <xs:element type="xs:date" name="XpryDt"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:element name="companyinformation">
    <xs:complexType>
      <xs:sequence>
        <xs:element type="xs:byte" name="companyid"/>
        <xs:element type="xs:string" name="companyname"/>
        <xs:element type="xs:long" name="registrationnumber"/>
        <xs:element type="xs:string" name="phonenumber"/>
        <xs:element type="xs:string" name="addressline1"/>
        <xs:element type="xs:string" name="city"/>
        <xs:element type="xs:string" name="province"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:element name="additionalinformationlist">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="additionalinformation" maxOccurs="unbounded" minOccurs="0">
          <xs:complexType>
            <xs:sequence>
              <xs:element type="xs:string" name="name"/>
              <xs:element type="xs:string" name="value"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:element name="notificationmsisdn" type="xs:long"/>
  <xs:element name="nextofkininformation">
    <xs:complexType>
      <xs:sequence>
        <xs:element type="xs:byte" name="relativeid"/>
        <xs:element type="xs:string" name="relationtype"/>
        <xs:element type="xs:string" name="firstname"/>
        <xs:element type="xs:long" name="idnumber"/>
        <xs:element type="xs:string" name="idtype"/>
        <xs:element type="xs:string" name="phonenumber"/>
        <xs:element type="xs:string" name="addressline1"/>
        <xs:element type="xs:string" name="city"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:element name="aliases">
    <xs:complexType>
      <xs:sequence>
        <xs:element type="xs:string" name="alias"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:element name="ratingparameterslist">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="ratingparameter">
          <xs:complexType>
            <xs:sequence>
              <xs:element type="xs:string" name="name"/>
              <xs:element type="xs:string" name="value"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

and Connor said...


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library