asked on

Create Oracle XML table and query data

Looking to get started by creating an XML table in Oracle, and seeing an example of how to query that data. Sample data attached.

talahi

ASKER

<kml xmlns="http://www.opengis.net/kml/2.2">
<Document>
<Placemark>
<name>AVON_PARK</name>
<description>Avon Park, (FL)</description>
<Point>
<coordinates>-81.2000000, 27.6400000</coordinates>
</Point>
</Placemark>
<Placemark>
<name>1_DEFAULT</name>
<description>1 Default (North Pole)</description>
<Point>
<coordinates>0.0000000, 90.0000000</coordinates>
</Point>
</Placemark>
<Placemark>
<name>ALTUS_OK</name>
<description>Altus, (OK)</description>
<Point>
<coordinates>-98.9764000, 34.3625000</coordinates>
</Point>
</Placemark>
<Placemark>
<name>ALTUS_TX</name>
<description>Altus, TX</description>
<Point>
<coordinates>-100.2900000, 34.9600000</coordinates>
</Point>
</Placemark>
<Placemark>
<name>Pine_Hill_AL</name>
<description>Pine Hill, AL</description>
<Point>
<coordinates>-87.5630000, 31.9290000</coordinates>
</Point>
</Placemark>
</Document>
</kml>

slightwv (䄆 Netminder)

Assuming it is stored as an XMLTYPE, try this as a starting point:

select 
	name,
	description,
	coordinates
from your_xml_table,
	xmltable(
		xmlnamespaces(default 'http://www.opengis.net/kml/2.2'),
		'/kml/Document/Placemark'
		passing(myxml)
		columns
			name varchar2(100) path 'name',
			description varchar2(100) path 'description',
			coordinates varchar2(100) path 'Point/coordinates'
	)
;

Open in new window

If it is a varchar2, make this change:
passing(xmltype(myxml))

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

talahi

ASKER

Thank-you. Everything worked perfectly. One last question. I've tried various versions of this control file but I get different errors each time. I want to load a much larger document with the same format. Any suggestions?

load data
infile 'd:\xml_sample_data.txt'
replace
into table testxml
xmltype(XMLDATA)
(
xml_sample_data filler char(20),
XMLDATA lobfile(xml_sample_data) terminated by eof
)

Insert option in effect for this table: REPLACE

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
XML_SAMPLE_DATA FIRST 20 CHARACTER
(FILLER FIELD)
XMLDATA DERIVED * EOF CHARACTER
Dynamic LOBFILE. Filename in field XML_SAMPLE_DATA

SQL*Loader-502: unable to open data file '<kml xmlns="http://w' for field XMLDATA table TESTXML
SQL*Loader-554: error opening file
SQL*Loader-509: System error: The device does not recognize the command.
SQL*Loader-502: unable to open data file ' <Document>' for field XMLDATA table TESTXML
SQL*Loader-554: error opening file
SQL*Loader-509: System error: The device does not recognize the command.
SQL*Loader-502: unable to open data file ' <Placemark>' for field XMLDATA table TESTXML
SQL*Loader-554: error opening file
SQL*Loader-509: System error: The device does not recognize the command.
SQL*Loader-502: unable to open data file ' <name>AVON_PAR' for field XMLDATA table TESTXML
SQL*Loader-554: error opening file
SQL*Loader-509: System error: The device does not recognize the command.
SQL*Loader-502: unable to open data file ' <description>A' for field XMLDATA table TESTXML
SQL*Loader-554: error opening file

slightwv (䄆 Netminder)

Never tried using sqlldr to load up XML files.

From the docs example:
https://docs.oracle.com/en/database/oracle/oracle-database/21/adxdb/how-to-load-XML-data.html#GUID-9B6141F4-E606-4365-B1EF-2BA391A43C09

'd:\xml_sample_data.txt' is a list of filenames holding the XML. It seems this file is the XML itself.

Thus the error:
SQL*Loader-502: unable to open data file '<kml xmlns="http://w' for field XMLDATA

talahi

ASKER

Sorry that was a dumb question. Insert statement works perfectly.

talahi

ASKER

Thanks again. You two are the best