XML and SQLLDR

[jinkys]

Hi there

Does anyone have any ideas how to load xml into table using sqlldr, just the data, don't need to load the tags. Can I setup ctl file somehow to handle this?

 

[LKBrwnDBA]

You can't use sql loader to load XML.

You would need to code a PL/SQL procedure to do it.

Check these out:

http://www.tdan.com/i024hy01.htm

http://www.sqlservercentral.com/columnists/dpeterson/isxmltheanswer.asp

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Thargy]

jinkys,

you can use sql plus to load xml.

If you have a table containing an xml datatype, then you have to load the whole field in one go, otherwise the parser will (correctly) reject it as being badly formed xml.

If you have > 4,000 characters, you have to split the xml into chunks of 4,000 or less. Create a staging table with a clob field, and populate it piecemeal with sql plus. Then insert into the target table from the clob table in one go. The xml will parse correctly, and voila, one loaded table.
Don't forget to drop the staging table....

Regards

Tharg

P.S. I use this approach regularly, so I know it works.

Grinding away at things Oracular

 

[SantaMufasa]

See? Easy...Who said working with XML presents challenges? <grin>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Thargy]

Er,

having read the above links, I'm no little concerned with the presence of XML in the database.

Second, why is sqlldr unable to load XML?
Do all the tags upset it or something? I ask, because I intended to use it to circumvent the 4,000 character limit in SQL Plus, and avoid the need for a staging table.

Regards

T

Grinding away at things Oracular

 

[jinkys]

Does anyone have an example procedure to get me on my way?

 

[Thargy]

jinkys,

yup, as I said, I do this a lot. You asked for it, so you're gonna get it

CREATE TABLE XML_IMPORT_TEMP (XML_DATA CLOB);
INSERT INTO XML_IMPORT_TEMP (XML_DATA) values ('');
INSERT INTO MESSAGE_DOCUMENT(MESSAGE_DOCUMENT_ID,DOCUMENT_SCHEMA) VALUES ('1',XMLTYPE.createxml('<xml/>'));
UPDATE XML_IMPORT_TEMP SET XML_DATA = '';
UPDATE XML_IMPORT_TEMP SET XML_DATA = XML_DATA || '<?xml version="1.0" ?>
<xs:schema id="MessageBody" targetNamespace="[URL unfurl="true"]http://tempuri.org/MessageBody.xsd"[/URL] xmlns:mstns="[URL unfurl="true"]http://tempuri.org/MessageBody.xsd"[/URL]
	xmlns="[URL unfurl="true"]http://tempuri.org/MessageBody.xsd"[/URL] xmlns:xs="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema"[/URL]
	xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified">
	<xs:element name="MessageBody" msdata:IsDataSet="true" msdata:Locale="en-GB" msdata:EnforceConstraints="False">
		<xs:complexType>
			<xs:choice maxOccurs="unbounded">
				<xs:element name="TestDoc">

                                                    BLAH, BLAH, BLAH
--WHOPPING GREAT CHUNK OF XML REMOVED
--FOR REASONS OF CONFIDENTIALITY
                                                    BLAH, BLAH BLAH
							<xs:element name="Location';
UPDATE XML_IMPORT_TEMP SET XML_DATA = XML_DATA || '" type="xs:int" minOccurs="0" />
							<xs:element name="OrderNumber" type="xs:string" minOccurs="0" />
							<xs:element name="TestLibraryCode" type="xs:string" minOccurs="0" />
						</xs:sequence>
                                                    BLAH, BLAH, BLAH
--WHOPPING GREAT CHUNK OF XML REMOVED
--FOR REASONS OF CONFIDENTIALITY
                                                    BLAH, BLAH BLAH				<xs:element name="HistCassetteLabelDoc">
					<xs:complexType>
						<xs:sequence>
							<xs:element name="HistPatientBlocksID" type="x';
UPDATE XML_IMPORT_TEMP SET XML_DATA = XML_DATA || 's:int" minOccurs="0" />
							<xs:element name="BlockIndex" type="xs:int" minOccurs="0" />
						</xs:sequence>
					</xs:complexType>
				</xs:element>
				<xs:element name="EmbeddedMessageDoc">
					<xs:complexType>
						<xs:sequence>
                                                    BLAH, BLAH, BLAH
--WHOPPING GREAT CHUNK OF XML REMOVED
--FOR REASONS OF CONFIDENTIALITY
                                                    BLAH, BLAH BLAH
					<xs:complexType>
						<xs:sequence>
							<xs:element name="LabNumber" type="xs:string" minOccurs="0" />
							<xs';
UPDATE XML_IMPORT_TEMP SET XML_DATA = XML_DATA || ':element name="ClinicianID" type="xs:int" minOccurs="0" />
							<xs:element name="SourceID" type="xs:int" minOccurs="0" />
							<xs:element name="ReportSection" type="xs:string" minOccurs="0" msdata:Ordinal="1" />
							<xs:element name="Status" type="xs:string" minOccurs="0" />
						</xs:sequence>
						<xs:attribute name="EventTypeDescription" form="unqualified" type="xs:string" />
					</xs:complexType>
				</xs:element>
			</xs:choice>
		</xs:complexType>
	</xs:element>
</xs:schema>';
UPDATE MESSAGE_DOCUMENT SET DOCUMENT_SCHEMA = (SELECT XMLTYPE.createxml(XML_DATA)  FROM XML_IMPORT_TEMP) WHERE MESSAGE_DOCUMENT_ID = 1;
 DROP TABLE XML_IMPORT_TEMP CASCADE CONSTRAINTS;

COMMIT;

Pardon the gaps, but I work in the biomedical field, so for obvious reasons I can't publish precise details of what we're doing.

the document_schema is where the XML lives in the database, and it has an XML datatype.

Regards

Tharg

Grinding away at things Oracular

 

[jinkys]

Is this for Oracle 8i, I thought this was for 9 and above?

 

[Thargy]

jinkys,

oops! I didn't check that. I'm using 9i, so it's entirely possible that there's no such thing as an xml type in 8.

In that case, just store the data in a CLOB (assuming it's very large) and forget the xml bits. Chopping it up into chunks < 4,000 characters still works though.

Regards

Tharg

Grinding away at things Oracular