Query an XML table in Oracle with an existing namespace

Question

This is my XML file inserted into an XMLTYPE Column.

<LandXML xmlns="http://www.landxml.org/schema/LandXML-1.2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="1.0" date="2015-09-07" time="00:00:03" xsi:schemaLocation="http://www.landxml.org/schema/LandXML-1.2 http://www.landxml.org/schema/LandXML-1.2/LandXML-1.2.xsd">
<Units>
    <Metric areaUnit="squareMeter" linearUnit="meter" volumeUnit="cubicMeter" temperatureUnit="celsius" pressureUnit="milliBars" angularUnit="decimal dd.mm.ss" directionUnit="decimal dd.mm.ss"/>
</Units>
<Application name="AAAAA" version="1.0">
    <Author createdBy="BBBBB"/>
</Application>
<CoordinateSystem horizontalDatum="MGA94_Zone55" datum="MGA94_Zone55"/>
<FeatureDictionary name="xml-gov-au-vic-icsm-eplan-cif-protocol" version="1.4"/>
<CgPoints zoneNumber="55">
    <CgPoint name="CGPNT-1" state="existing" pntSurv="control" oID="333700440">5810792.960 340197.350</CgPoint>
    <CgPoint name="CGPNT-2" state="existing" pntSurv="control" oID="333708630">5810806.400 340387.900</CgPoint>
    <CgPoint name="CGPNT-3" state="proposed" pntSurv="reference">5810916.224 340390.442</CgPoint>
    <CgPoint name="CGPNT-4" state="existing" pntSurv="boundary">5810912.368 340383.893</CgPoint>
    <CgPoint name="CGPNT-5" state="existing" pntSurv="boundary">5810914.091 340381.999</CgPoint>
    <CgPoint name="CGPNT-6" state="existing" pntSurv="boundary">5810914.011 340373.680</CgPoint>
    <CgPoint name="CGPNT-7" state="existing" pntSurv="boundary">5810913.994 340371.880</CgPoint>
    <CgPoint name="CGPNT-8" state="existing" pntSurv="boundary">5810913.734 340344.821</CgPoint>
    <CgPoint name="CGPNT-9" state="existing" pntSurv="boundary">5810913.711 340342.381</CgPoint>
</CgPoints>
<Parcels>
    <Parcel name="1-p2\PS601484" class="Lot" state="created" parcelType="Part" parcelFormat="Standard" area="41.160">
        <Center pntRef="CGPNT-49"/>
        <CoordGeom name="CG-1">
            <Line>
                <Start pntRef="CGPNT-39"/>
                <End pntRef="CGPNT-36"/>
            </Line>
            <Line>
                <Start pntRef="CGPNT-36"/>
                <End pntRef="CGPNT-37"/>
            </Line>
            <Line>
                <Start pntRef="CGPNT-37"/>
                <End pntRef="CGPNT-38"/>
            </Line>
            <Line>
                <Start pntRef="CGPNT-38"/>
                <End pntRef="CGPNT-39"/>
            </Line>
        </CoordGeom>
    </Parcel>
    <Parcel name="1-p3\PS601484" class="Lot" state="created" parcelType="Part" parcelFormat="Standard" area="28.592">
        <Center pntRef="CGPNT-50"/>
        <CoordGeom name="CG-2">
            <Line>
                <Start pntRef="CGPNT-40"/>
                <End pntRef="CGPNT-41"/>
            </Line>
            <Line>
                <Start pntRef="CGPNT-41"/>
                <End pntRef="CGPNT-42"/>
            </Line>
            <Line>
                <Start pntRef="CGPNT-42"/>
                <End pntRef="CGPNT-43"/>
            </Line>
            <Line>
                <Start pntRef="CGPNT-43"/>
                <End pntRef="CGPNT-40"/>
            </Line>
        </CoordGeom>
    </Parcel>
    <Parcel name="E1" class="Easement" state="created" parcelType="Single" parcelFormat="Geometry" area="72.297">
        <Center pntRef="CGPNT-51"/>
        <CoordGeom name="CG-3">
            <Line>
                <Start pntRef="CGPNT-10"/>
                <End pntRef="CGPNT-11"/>
            </Line>
            <Line>
                <Start pntRef="CGPNT-11"/>
                <End pntRef="CGPNT-12"/>
            </Line>
            <Line>
                <Start pntRef="CGPNT-12"/>
                <End pntRef="CGPNT-8"/>
            </Line>
            <Line>
                <Start pntRef="CGPNT-8"/>
                <End pntRef="CGPNT-9"/>
            </Line>
            <Line>
                <Start pntRef="CGPNT-9"/>
                <End pntRef="CGPNT-10"/>
            </Line>
        </CoordGeom>
    </Parcel>
    <Parcel name="E2" class="Easement" state="created" parcelType="Single" parcelFormat="Geometry" area="96.590">
        <Center pntRef="CGPNT-52"/>
        <CoordGeom name="CG-4">
            <Line>
                <Start pntRef="CGPNT-48"/>
                <End pntRef="CGPNT-25"/>
            </Line>
            <Line>
                <Start pntRef="CGPNT-25"/>
                <End pntRef="CGPNT-27"/>
            </Line>
            <Line>
                <Start pntRef="CGPNT-27"/>
                <End pntRef="CGPNT-4"/>
            </Line>
            <Line>
                <Start pntRef="CGPNT-4"/>
                <End pntRef="CGPNT-5"/>
            </Line>
            <Line>
                <Start pntRef="CGPNT-5"/>
                <End pntRef="CGPNT-6"/>
            </Line>
            <Line>
                <Start pntRef="CGPNT-6"/>
                <End pntRef="CGPNT-7"/>
            </Line>
            <Line>
                <Start pntRef="CGPNT-7"/>
                <End pntRef="CGPNT-8"/>
            </Line>
            <Line>
                <Start pntRef="CGPNT-8"/>
                <End pntRef="CGPNT-48"/>
            </Line>
        </CoordGeom>
    </Parcel>
</Parcels>
<Survey>
    <SurveyHeader name="PS601484Y" jurisdiction="Victoria" type="surveyed" surveyorFirm="SSSS &amp; WWWW" surveyorReference="B" surveyFormat="Level Land">
        <HeadOfPower name="Subdivision Act 1988"/>
        <AdministrativeDate adminDateType="Date of Survey" adminDate="2006-12-20"/>
        <AdministrativeArea adminAreaName="WHITEHORSE CITY" adminAreaType="LGA" adminAreaCode="372"/>
        <AdministrativeArea adminAreaName="NUNAWADING" adminAreaType="Parish" adminAreaCode="3337"/>
        <Annotation name="ANNO-1" type="Crown Allotment" desc="116A (PART)"/>
    </SurveyHeader>
    <InstrumentSetup id="IS-1" stationName="IS-1" instrumentHeight="0">
        <InstrumentPoint pntRef="CGPNT-1"/>
    </InstrumentSetup>
    <InstrumentSetup id="IS-2" stationName="IS-2" instrumentHeight="0">
        <InstrumentPoint pntRef="CGPNT-2"/>
    </InstrumentSetup>
    <ObservationGroup id="OBSG-1">
        <ReducedObservation name="OBS-1" desc="Traverse" purpose="traverse" setupID="IS-1" targetSetupID="IS-2" azimuth="85.5756" horizDistance="191.023"/>
        <ReducedObservation name="OBS-2" desc="Traverse" purpose="traverse" setupID="IS-2" targetSetupID="IS-2" azimuth="1.1934" horizDistance="109.853"/>
        <ReducedObservation name="OBS-3" desc="Traverse" purpose="traverse" setupID="IS-2" targetSetupID="IS-1" azimuth="239.31" horizDistance="7.600"/>
        <ReducedObservation name="OBS-4" desc="Normal Boundary" purpose="normal" setupID="IS-2" targetSetupID="IS-2" azimuth="312.18" horizDistance="2.560"/>
        <ReducedObservation name="OBS-5" desc="Normal Boundary" purpose="normal" setupID="IS-1" targetSetupID="IS-1" azimuth="179.3051" horizDistance="3.335"/>
    </ObservationGroup>
</Survey>
<Monuments>
    <Monument name="MON-1" pntRef="CGPNT-1" type="Plaque" state="Existing" desc="Exist PM Plaque OK" condition="OK"/>
    <Monument name="MON-2" pntRef="CGPNT-2" type="Plaque" state="Existing" desc="Exist PM Plaque OK" condition="OK"/>
    <Monument name="MON-3" pntRef="CGPNT-3" type="Pipe" state="New" desc="New RM Pipe Plcd" condition="Placed"/>
</Monuments>
</LandXML>

This is my query:

SELECT t.filename, X.*
FROM XML_FILES_CLOB t,
XMLTABLE ('//Parcels/Parcel'
PASSING t.FILECONTENT
COLUMNS filename VARCHAR2(30) PATH 'filename', 
Parcel VARCHAR2(30) PATH '@name') x
Where t.filename='PC373723N';

Aim: Extract the @name of Parcels in the XML file (PC373723N).

XMLTable:

Result:

Problem: It does not retrieve the name of parcels. I think it is because of a namespace that exists in the XML.

Any help is appreciated.

Arkadiusz Łukasiewicz · Accepted Answer · 2016-04-04 06:25:24Z

1

You have to add namespace declaration before xquery string. XMLTABLE (xmlnamesapce, xquery, xmltable_option)

xmlnamespace(defult 'blblbele','abcd' as "xyz")

And in your case you have only default namspace.

SELECT t.filename, X.*
FROM XML_FILES_CLOB t,
XMLTABLE (xmlnamespaces(default 'http://www.landxml.org/schema/LandXML-1.2'),'//Parcels/Parcel'
PASSING t.FILECONTENT
COLUMNS filename VARCHAR2(30) PATH 'filename', 
Parcel VARCHAR2(30) PATH '@name') x
Where t.filename='PC373723N';

It should work. *) column x.filename will be empty becaouse there is no filename element inside xml docuement

answered Apr 4, 2016 at 6:25

Arkadiusz Łukasiewicz

6,3761 gold badge13 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query an XML table in Oracle with an existing namespace

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related