Skip to Main Content
  • Questions

  • Retrieving the data from a clob with xml data into number of Tables using xsd

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Raji.

Asked: April 13, 2016 - 6:59 pm UTC

Last updated: April 14, 2016 - 2:40 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

I have a huge xml file to be loaded into oracle tables. I have loaded the XML data into CLOB object. Now I want to retrieve the xml data from clob into number of tables maintaining the relationships. Can you please post an example to retrieve data from the clob and populate into database tables. I want to add LEI in one table, Entity in another table, Entity Legal address and Head quarter address etc. I also want to maintain the relationship that the Legal address and headquarters address sub-nodes will belong to LEI.

I have the table as

select * from CLEI_XML

LOB_FILE XML_CONTENT
1 <Value Error>


SELECT dbms_lob.getlength(xml_content) from CLEI_XML

DBMS_LOB.GETLENGTH(XML_CONTENT
621503729


The XML sample is :

<?xml version="1.0" encoding="UTF-8"?><lei:LEIData xmlns:lei=" http://www.leiroc.org/data/schema/leidata/2014" xmlns:clei=" http://ns.c-lei.org/leidata/1" xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" >
<lei:LEIHeader>
<lei:ContentDate>2016-03-22T04:10:23-05:00</lei:ContentDate>
<lei:Originator>52990034RLKT0WSOAM90</lei:Originator>
<lei:Extension>
<clei:LOUFiles>
<clei:LOUFile>
<clei:DownloadDate>2016-02-21T04:00:20-06:00</clei:DownloadDate>
<clei:DownloadURL> https://www.ceireg.de/download/?download.dumptype=full&download.dumpdate:2=2016&download.dumpdate:1=02&download.dumpdate:0=21 </clei:DownloadURL>
<clei:ContentDate>2016-02-21T11:00:10.885+01:00</clei:ContentDate>
<clei:Originator>39120001KULK7200U106</clei:Originator>
<clei:RecordCount>1313</clei:RecordCount>
<clei:ManagingLOUCount managingLOU="39120001KULK7200U106">1313</clei:ManagingLOUCount>
<clei:SchemaValid>true</clei:SchemaValid>
</clei:LOUFile>
<clei:LOUFile>
<clei:DownloadDate>2016-03-22T04:00:30-05:00</clei:DownloadDate>
<clei:DownloadURL> http://www.leichina.org/webcei/LEIDataServlet?type=all </clei:DownloadURL>
<clei:ContentDate>2016-03-22T04:00:00.016+08:00</clei:ContentDate>
<clei:Originator>300300KDIZ11PV2GH547</clei:Originator>
<clei:RecordCount>201</clei:RecordCount>
<clei:ManagingLOUCount managingLOU="300300KDIZ11PV2GH547">201</clei:ManagingLOUCount>
<clei:SchemaValid>true</clei:SchemaValid>
</clei:LOUFile>
<clei:LOUFile>
<clei:DownloadDate>2016-03-11T04:00:32-06:00</clei:DownloadDate>
<clei:DownloadURL> http://www.centraldepository.cz/LEI/LEI_20160310_full.xml </clei:DownloadURL>
<clei:ContentDate>2016-03-10T00:00:00+01:00</clei:ContentDate>
<clei:Originator>315700LK78Z7C0WMIL03</clei:Originator>
<clei:RecordCount>6881</clei:RecordCount>
<clei:ManagingLOUCount managingLOU="315700LK78Z7C0WMIL03">6881</clei:ManagingLOUCount>
<clei:SchemaValid>true</clei:SchemaValid>
</clei:LOUFile>
<clei:LOUFile>
<clei:DownloadDate>2015-12-10T04:01:19-06:00</clei:DownloadDate>
<clei:DownloadURL> https://www.lei.mjusticia.gob.es/pgdescargaintxmls?id=11863&AxpsAutoDetectCookieSupport=1 </clei:DownloadURL>
<clei:ContentDate>2015-12-09T23:59:59.999+01:00</clei:ContentDate>
<clei:Originator>959800R2X69K6Y6MX775</clei:Originator>
<clei:RecordCount>12796</clei:RecordCount>
<clei:ManagingLOUCount managingLOU="959800R2X69K6Y6MX775">12796</clei:ManagingLOUCount>
<clei:SchemaValid>true</clei:SchemaValid>
</clei:LOUFile>
<clei:LOUFile>
<clei:DownloadDate>2015-11-16T04:01:10-06:00</clei:DownloadDate>
<clei:DownloadURL> https://www.ytj.fi/tiedostolataus.aspx?date=20151115 </clei:DownloadURL>
<clei:ContentDate>2015-11-15T00:00:00</clei:ContentDate>
<clei:Originator>743700OO8O2N3TQKJC81</clei:Originator>
<clei:RecordCount>2163</clei:RecordCount>
<clei:ManagingLOUCount managingLOU="743700OO8O2N3TQKJC81">2163</clei:ManagingLOUCount>
<clei:SchemaValid>true</clei:SchemaValid>
</clei:LOUFile>
<clei:LOUFile>
<clei:DownloadDate>2015-11-18T04:01:33-06:00</clei:DownloadDate>
<clei:DownloadURL> https://www.geiportal.org/customer/download_xml.php?file=GEI_Full_20151118_cf1.zip </clei:DownloadURL>
<clei:ContentDate>2015-11-18T01:30:08</clei:ContentDate>
<clei:Originator>5299000J2N45DDNE4Y28</clei:Originator>
<clei:RecordCount>62648</clei:RecordCount>
<clei:ManagingLOUCount managingLOU="5299000J2N45DDNE4Y28">62637</clei:ManagingLOUCount>
<clei:ManagingLOUCount managingLOU="EVK05KS7XY1DEII3R011">10</clei:ManagingLOUCount>
<clei:ManagingLOUCount managingLOU="213800D1EI4B9WTWWD28">1</clei:ManagingLOUCount>
<clei:SchemaValid>true</clei:SchemaValid>
</clei:LOUFile>
</clei:LOUFiles>
</lei:Extension>
</lei:LEIHeader>
<lei:LEIRecords>
<LEIRecord xmlns=" http://www.leiroc.org/data/schema/leidata/2014" >
<LEI>39120001CK4NXMUXLH16</LEI>
<Entity>
<LegalName>CHORUS CleanTech GmbH &amp; Co. Solarpark Vilseck KG</LegalName>
<LegalAddress>
<Line1>Prof.-Messerschmitt-Straße 3</Line1>
<City>Neubiberg</City>
<Region>DE-BY</Region>
<Country>DE</Country>
<PostalCode>85579</PostalCode>
</LegalAddress>
<HeadquartersAddress>
<Line1>Prof.-Messerschmitt-Straße 3</Line1>
<City>Neubiberg</City>
<Region>DE-BY</Region>
<Country>DE</Country>
<PostalCode>85579</PostalCode>
</HeadquartersAddress>
<BusinessRegisterEntityID register="München">HRA 97903</BusinessRegisterEntityID>
<LegalJurisdiction>DE</LegalJurisdiction>
<LegalForm>Kommanditgesellschaft</LegalForm>
<EntityStatus>ACTIVE</EntityStatus>
</Entity>
<Registration>
<InitialRegistrationDate>2014-02-14T15:49:16.075+01:00</InitialRegistrationDate>
<LastUpdateDate>2016-02-14T16:03:02.885+01:00</LastUpdateDate>
<RegistrationStatus>ISSUED</RegistrationStatus>
<NextRenewalDate>2017-02-14T15:49:16.075+01:00</NextRenewalDate>
<ManagingLOU>39120001KULK7200U106</ManagingLOU>
<ValidationSources>FULLY_CORROBORATED</ValidationSources>
</Registration>
</LEIRecord>
<LEIRecord xmlns=" http://www.leiroc.org/data/schema/leidata/2014" >
<LEI>39120001NUMDHGMNOW67</LEI>
<Entity>
<LegalName>SAN MARTINO ENERGY DI CHORUS SOLAR 5. SRL &amp; CO . SAS</LegalName>
<LegalAddress>
<Line1>ANELLO NORD / Nordring 25</Line1>
<City>Brunico / Bruneck (BZ)</City>
..........


There is a xsd file along with xml file if it can be used to load the xml data into tables. The XSD file is

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs=" http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified"
targetNamespace=" http://www.leiroc.org/data/schema/leidata/2014"
xmlns:lei=" http://www.leiroc.org/data/schema/leidata/2014" >
<xs:import namespace=" http://www.w3.org/XML/1998/namespace" schemaLocation=" http://www.w3.org/2001/xml.xsd"/ >
<xs:element name="LEIData" type="lei:LEIData" />
<xs:complexType name="LEIData">
<xs:sequence>
<xs:element name="LEIHeader" type="lei:LEIHeaderType" minOccurs="0"/>
<xs:element name="LEIRecords" type="lei:LEIRecordsType"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="LEIHeaderType">
<xs:sequence>
<xs:element name="ContentDate" type="xs:dateTime" minOccurs="0"/>
<xs:element name="Originator" type="lei:LEIType" minOccurs="0"/>
<xs:element name="FileContent" type="lei:FileContentEnum" minOccurs="0"/>
<xs:element name="DeltaStart" type="xs:dateTime" minOccurs="0"/>
<xs:element name="RecordCount" type="xs:int" minOccurs="0"/>
<xs:element name="NextVersion" type="lei:HeaderNextVersionType" minOccurs="0"/>
<xs:element name="Extension" type="lei:ExtensionType" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="HeaderNextVersionType">
<xs:sequence>
<xs:any minOccurs="0" maxOccurs="unbounded" processContents="lax" namespace="##targetNamespace"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="LEIRecordsType">
<xs:sequence>
<xs:element name="LEIRecord" type="lei:LEIRecordType" minOccurs="0" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="LEIRecordType" >
<xs:sequence>
<xs:element name="LEI" type="lei:LEIType"/>
<xs:element name="Entity" type="lei:EntityType"/>
<xs:element name="Registration" type="lei:RegistrationType"/>
<xs:element name="NextVersion" type="lei:LEIRecordNextVersionType" minOccurs="0"/>
<xs:element name="Extension" type="lei:ExtensionType" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="LEIRecordNextVersionType">
<xs:sequence>
<xs:any minOccurs="0" maxOccurs="unbounded" processContents="lax"
namespace="##targetNamespace"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="EntityType" >
<xs:sequence>
<xs:element name="LegalName" type="lei:NameType"/>
<xs:element name="OtherEntityNames" type="lei:OtherEntityNamesType" minOccurs="0"/>
<xs:element name="LegalAddress" type="lei:AddressType"/>
<xs:element name="HeadquartersAddress" type="lei:AddressType"/>
<xs:element name="OtherAddresses" type="lei:OtherAddressesType" minOccurs="0"/>
<xs:element name="BusinessRegisterEntityID" type="lei:BusinessRegisterEntityIDType" minOccurs="0"/>
<xs:element name="LegalJurisdiction" type="lei:JurisdictionCodeType" minOccurs="0"/>
<xs:element name="LegalForm" type="lei:NameType" minOccurs="0"/>
<xs:element name="AssociatedEntity" type="lei:AssociatedEntityType" minOccurs="0"/>
<xs:element name="EntityStatus" type="lei:EntityStatusEnum"/>
<xs:element name="EntityExpirationDate" type="xs:dateTime" minOccurs="0"/>
<xs:element name="EntityExpirationReason" type="lei:EntityExpirationReasonEnum" minOccurs="0"/>
<xs:element name="SuccessorEntity" type="lei:SuccessorEntityType" minOccurs="0"/>
<xs:element name="NextVersion" type="lei:EntityNextVersionType" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="EntityNextVersionType">
<xs:sequence>
<xs:any minOccurs="0" maxOccurs="unbounded" processContents="lax"
namespace="##targetNamespace"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="RegistrationType" >
<xs:sequence>
<xs:element name="InitialRegistrationDate" type="xs:dateTime"/>
<xs:element name="LastUpdateDate" type="xs:dateTime"/>
<xs:element name="RegistrationStatus" type="lei:RegistrationStatusEnum"/>
<xs:element name="NextRenewalDate" type="xs:dateTime" />
<xs:element name="ManagingLOU" type="lei:LEIType"/>
<xs:element name="ValidationSources" type="lei:ValidationSourcesEnum" minOccurs="0"/>
<xs:element name="NextVersion" type="lei:RegistrationNextVersionType" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="RegistrationNextVersionType">
<xs:sequence>
<xs:any minOccurs="0" maxOccurs="unbounded" processContents="lax" namespace="##targetNamespace"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="ExtensionType">
<xs:sequence>
<xs:any minOccurs="0" maxOccurs="unbounded" processContents="lax" namespace="##other"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="AddressType">
<xs:sequence>
<xs:element name="Line1" type="lei:Token500Type"/>
<xs:element name="Line2" type="lei:Token500Type" minOccurs="0"/>
<xs:element name="Line3" type="lei:Token500Type" minOccurs="0"/>
<xs:element name="Line4" type="lei:Token500Type" minOccurs="0"/>
<xs:element name="City" type="lei:Token500Type" />
<xs:element name="Region" type="lei:RegionCodeType" minOccurs="0"/>
<xs:element name="Country" type="lei:CountryCodeType" />
<xs:element name="PostalCode" type="lei:Token500Type" minOccurs="0"/>
</xs:sequence>
<xs:attribute ref="xml:lang" use="optional"/>
</xs:complexType>
<xs:complexType name="AssociatedEntityType">
<xs:choice>
<xs:element name="AssociatedLEI" type="lei:LEIType"/>
<xs:element name="AssociatedEntityName" type="lei:NameType"/>
</xs:choice>
<xs:attribute name="type" type="lei:AssociatedEntityTypeEnum" use="required"/>
</xs:complexType>
<xs:complexType name="BusinessRegisterEntityIDType">
<xs:simpleContent>
<xs:extension base="lei:Token500Type">
<xs:attribute name="register" type="lei:BusinessRegisterEnum"/>
</xs:extension>
</xs:simpleContent>
</xs:complexType>
<xs:simpleType name="JurisdictionCodeType">
<xs:union memberTypes="lei:CountryCodeType lei:RegionCodeType" />
</xs:simpleType>
<xs:complexType name="NameType">
<xs:simpleContent>
<xs:extension base="lei:Token500Type">
<xs:attribute ref="xml:lang" use="optional"/>
</xs:extension>
</xs:simpleContent>
</xs:complexType>
<xs:complexType name="OtherAddressType">
<xs:complexContent>
<xs:extension base="lei:AddressType">
<xs:attribute name="type" type="lei:AddressTypeEnum" use="required"/>
</xs:extension>
</xs:complexContent>
</xs:complexType>
<xs:complexType name="OtherAddressesType">
<xs:sequence>
<xs:element name="OtherAddress" type="lei:OtherAddressType" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="OtherEntityNamesType">
<xs:sequence>
<xs:element name="OtherEntityName" type="lei:OtherEntityNameType" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="OtherEntityNameType">
<xs:complexContent>
<xs:extension base="lei:NameType">
<xs:attribute name="type" type="lei:EntityNameTypeEnum" use="required"/>
</xs:extension>
</xs:complexContent>
</xs:complexType>
<xs:complexType name="SuccessorEntityType">
<xs:choice>
<xs:element name="SuccessorLEI" type="lei:LEIType"/>
<xs:element name="SuccessorEntityName" type="lei:NameType"/>
</xs:choice>
</xs:complexType>
<xs:simpleType name="CountryCodeType">
<xs:restriction base="xs:string">
<xs:minLength value="2"/>
<xs:maxLength value="2"/>
<xs:pattern value="([A-Z]{2})"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="RegionCodeType">
<xs:restriction base="xs:string">
<xs:minLength value="4"/>
<xs:maxLength value="6"/>
<xs:pattern value="([A-Z]{2}-[A-Z0-9]{1,3})"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="LEIType">
<xs:restriction base="xs:string">
<xs:minLength value="20"/>
<xs:maxLength value="20"/>
<xs:pattern value="([0-9A-Z]{18}[0-9]{2})"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="Token500Type">
<xs:restriction base="xs:token">
<xs:maxLength value="500"/>
</xs:restriction>
</xs:simpleType>
All enumeration types provide for forward compatibility by accepting any string as valid at the schema level. -->
The legal values for Version 1.0 are indicated in a 1.0-suffixed type which is unioned with xs:string to -->
form the complete enumeration type. -->
<xs:simpleType name="FileContentEnum1.0">
<xs:restriction base="xs:string">
<!-- Enumeration values for 1.0: -->
<xs:enumeration value="LOU_FULL_PUBLISHED" />
<xs:enumeration value="LOU_DELTA_PUBLISHED" />
<xs:enumeration value="COU_FULL_PUBLISHED" />
<xs:enumeration value="COU_DELTA_PUBLISHED" />
<xs:enumeration value="QUERY_RESPONSE" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="FileContentEnum">
<xs:union memberTypes="lei:FileContentEnum1.0 xs:string" />
</xs:simpleType>
<xs:simpleType name="BusinessRegisterEnum">
<xs:restriction base="xs:string">
<xs:annotation>
<xs:documentation>
<!-- Enumeration values for 1.0 are maintained elsewhere -->
</xs:documentation>
</xs:annotation>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="EntityNameTypeEnum1.0">
<xs:restriction base="xs:string">
<!-- Enumeration values for 1.0: -->
<xs:enumeration value="OTHER_LEGAL" />
<xs:enumeration value="PREFERRED_ASCII_TRANSLITERATED_LEGAL" />
<xs:enumeration value="AUTO_ASCII_TRANSLITERATED_LEGAL" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="EntityNameTypeEnum">
<xs:union memberTypes="lei:EntityNameTypeEnum1.0 xs:string" />
</xs:simpleType>
<xs:simpleType name="AddressTypeEnum1.0">
<xs:restriction base="xs:string">
<!-- Enumeration values for 1.0: -->
<xs:enumeration value="LEGAL_ADDRESS" />
<xs:enumeration value="HEADQUARTERS_ADDRESS" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="AddressTypeEnum">
<xs:union memberTypes="lei:AddressTypeEnum1.0 xs:string" />
</xs:simpleType>
<xs:simpleType name="AssociatedEntityTypeEnum1.0">
<xs:restriction base="xs:string">
<!-- Enumeration values for 1.0: -->
<xs:enumeration value="FUND_FAMILY" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="AssociatedEntityTypeEnum">
<xs:union memberTypes="lei:AssociatedEntityTypeEnum1.0 xs:string" />
</xs:simpleType>
<xs:simpleType name="EntityStatusEnum1.0">
<xs:restriction base="xs:string">
<!-- Enumeration values for 1.0: -->
<xs:enumeration value="ACTIVE" />
<xs:enumeration value="INACTIVE" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="EntityStatusEnum">
<xs:union memberTypes="lei:EntityStatusEnum1.0 xs:string" />
</xs:simpleType>
<xs:simpleType name="EntityExpirationReasonEnum1.0">
<xs:restriction base="xs:string">
<!-- Enumeration values for 1.0: -->
<xs:enumeration value="DISSOLVED" />
<xs:enumeration value="CORPORATE_ACTION" />
<xs:enumeration value="OTHER" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="EntityExpirationReasonEnum">
<xs:union memberTypes="lei:EntityExpirationReasonEnum1.0 xs:string" />
</xs:simpleType>
<xs:simpleType name="RegistrationStatusEnum1.0">
<xs:restriction base="xs:string">
<!-- Enumeration values for 1.0: -->
<xs:enumeration value="PENDING_VALIDATION" />
<xs:enumeration value="ISSUED" />
<xs:enumeration value="DUPLICATE" />
<xs:enumeration value="LAPSED" />
<xs:enumeration value="MERGED" />
<xs:enumeration value="RETIRED" />
<xs:enumeration value="ANNULLED" />
<xs:enumeration value="CANCELLED" />
<xs:enumeration value="TRANSFERRED" />
<xs:enumeration value="PENDING_TRANSFER" />
<xs:enumeration value="PENDING_ARCHIVAL" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="RegistrationStatusEnum">
<xs:union memberTypes="lei:RegistrationStatusEnum1.0 xs:string" />
</xs:simpleType>
<xs:simpleType name="ValidationSourcesEnum1.0">
<xs:restriction base="xs:string">
<!-- Enumeration values for 1.0: -->
<xs:enumeration value="PENDING" />
<xs:enumeration value="ENTITY_SUPPLIED_ONLY" />
<xs:enumeration value="PARTIALLY_CORROBORATED" />
<xs:enumeration value="FULLY_CORROBORATED" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="ValidationSourcesEnum">
<xs:union memberTypes="lei:ValidationSourcesEnum1.0 xs:string" />
</xs:simpleType>
</xs:schema>


and Connor said...

OK, you got some choices here

1) You can get the database to do it for you in object-relational tables

http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb05sto.htm#ADXDB4482

using the "structured storage". This uses object-relational types to preserve the hierarchy. Not sure if that's what you had in mind.

2) You can get the database to do it for you, but you need to create the true relational structures. You can use DBMS_XMLSTORE for this. See http://www.gokhanatil.com/2009/01/how-to-import-data-from-xml.html for an example

3) If you total control over the extract into true relational structures, you use the XMLTABLE syntax to mine the hierarchy and extract attributes. See

https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9523447800346871855

for a simple example of that.

Hope this helps.

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.