7 Replies Latest reply on Mar 7, 2020 10:18 AM by odie_63

    Problem with raeding XMLTABLE

    Mettemusens2

      I'm on 11.2 SE

       

      I have an XML I cannot read - I need the logituda and latitude fieds:  (obs there are 1:m siteMeasurements)  and namespaces involved (and I HATE namespaces):

      Can you help me:

       

      I have tried this but "no rows returned"

       

      select  id, column_value

      from m2xml

              cross join xmltable(XMLNAMESPACES ('xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance' as "x"), 'x:siteMeasurements' passing pointdata

                                  columns lat varchar(10) path 'x:latitude',

                                          lon varchar(10) path 'x:longitude') t

      ;

       

      <d2LogicalModel modelBaseVersion="2" xmlns="http://datex2.eu/schema/2/2_0">

      <exchange>

      <supplierIdentification>

      <country>dk</country>

      <nationalIdentifier>Cowi</nationalIdentifier>

      </supplierIdentification>

      </exchange>

      <payloadPublication xsi:type="MeasuredDataPublication" lang="dk-da" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

      <publicationTime>2020-03-03T07:28:21.809Z</publicationTime>

      <publicationCreator>

      <country>dk</country>

      <nationalIdentifier>Cowi</nationalIdentifier>

      </publicationCreator>

      <measurementSiteTableReference targetClass="MeasurementSiteTable" id="1" version="1"/>

      <headerInformation>

      <confidentiality>noRestriction</confidentiality>

      <informationStatus>real</informationStatus>

      </headerInformation>

      <siteMeasurements>

      <measurementSiteReference targetClass="MeasurementSiteRecord" id="2505ffec619d3f3eb2b47730dcf439b1" version="5acd7196-6095-415a-bd6d-4e33f6c696f0"/>

      <measurementTimeDefault>2020-03-03T06:48:15.000Z</measurementTimeDefault>

      <measuredValue index="1">

      <measuredValue>

      <basicData xsi:type="IndividualVehicleDataValues">

      <pertinentLocation xsi:type="Point">

      <pointByCoordinates>

      <bearing>0</bearing>

      <pointCoordinates>

      <latitude>54.673428</latitude>

      <longitude>11.942446</longitude>

      </pointCoordinates>

      </pointByCoordinates>

      </pertinentLocation>

      <forVehiclesWithCharacteristicsOf>

      <vehicleType>car</vehicleType>

      </forVehiclesWithCharacteristicsOf>

      <individualVehicleSpeed>

      <speed>0.0</speed>

      </individualVehicleSpeed>

      </basicData>

      </measuredValue>

      </measuredValue>

      </siteMeasurements>

      <siteMeasurements>

      <measurementSiteReference targetClass="MeasurementSiteRecord" id="2505ffec619d3f3eb2b47730dcf439b1" version="5acd7196-6095-415a-bd6d-4e33f6c696f0"/>

      <measurementTimeDefault>2020-03-03T06:54:30.000Z</measurementTimeDefault>

      <measuredValue index="1">

      <measuredValue>

      <basicData xsi:type="IndividualVehicleDataValues">

      <pertinentLocation xsi:type="Point">

      <pointByCoordinates>

      <bearing>224</bearing>

      <pointCoordinates>

      <latitude>54.694336</latitude>

      <longitude>11.956245</longitude>

      </pointCoordinates>

      </pointByCoordinates>

      </pertinentLocation>

      <forVehiclesWithCharacteristicsOf>

      <vehicleType>car</vehicleType>

      </forVehiclesWithCharacteristicsOf>

      <individualVehicleSpeed>

      <speed>3.6396303</speed>

      </individualVehicleSpeed>

      </basicData>

      </measuredValue>

      </measuredValue>

      </siteMeasurements>

      </payloadPublication>

      </d2LogicalModel>

       

       

       

       

        • 1. Re: Problem with raeding XMLTABLE
          mNem

          with m2xml (pointdata) as

          (

          select xmltype(

          '

          <d2LogicalModel modelBaseVersion="2" xmlns="http://datex2.eu/schema/2/2_0">

             <exchange>

                <supplierIdentification>

                   <country>dk</country>

                   <nationalIdentifier>Cowi</nationalIdentifier>

                </supplierIdentification>

             </exchange>

             <payloadPublication xsi:type="MeasuredDataPublication" lang="dk-da"

                                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

                <publicationTime>2020-03-03T07:28:21.809Z</publicationTime>

                <publicationCreator>

                   <country>dk</country>

                   <nationalIdentifier>Cowi</nationalIdentifier>

                </publicationCreator>

                <measurementSiteTableReference targetClass="MeasurementSiteTable" id="1" version="1"/>

                <headerInformation>

                   <confidentiality>noRestriction</confidentiality>

                   <informationStatus>real</informationStatus>

                </headerInformation>

                <siteMeasurements>

                   <measurementSiteReference targetClass="MeasurementSiteRecord" id="2505ffec619d3f3eb2b47730dcf439b1"

                                             version="5acd7196-6095-415a-bd6d-4e33f6c696f0"/>

                   <measurementTimeDefault>2020-03-03T06:48:15.000Z</measurementTimeDefault>

                   <measuredValue index="1">

                      <measuredValue>

                         <basicData xsi:type="IndividualVehicleDataValues">

                            <pertinentLocation xsi:type="Point">

                               <pointByCoordinates>

                                  <bearing>0</bearing>

                                  <pointCoordinates>

                                     <latitude>54.673428</latitude>

                                     <longitude>11.942446</longitude>

                                  </pointCoordinates>

                               </pointByCoordinates>

                            </pertinentLocation>

                            <forVehiclesWithCharacteristicsOf>

                               <vehicleType>car</vehicleType>

                            </forVehiclesWithCharacteristicsOf>

                            <individualVehicleSpeed>

                               <speed>0.0</speed>

                            </individualVehicleSpeed>

                         </basicData>

                      </measuredValue>

                   </measuredValue>

                </siteMeasurements>

                <siteMeasurements>

                   <measurementSiteReference targetClass="MeasurementSiteRecord" id="2505ffec619d3f3eb2b47730dcf439b1"

                                             version="5acd7196-6095-415a-bd6d-4e33f6c696f0"/>

                   <measurementTimeDefault>2020-03-03T06:54:30.000Z</measurementTimeDefault>

                   <measuredValue index="1">

                      <measuredValue>

                         <basicData xsi:type="IndividualVehicleDataValues">

                            <pertinentLocation xsi:type="Point">

                               <pointByCoordinates>

                                  <bearing>224</bearing>

                                  <pointCoordinates>

                                     <latitude>54.694336</latitude>

                                     <longitude>11.956245</longitude>

                                  </pointCoordinates>

                               </pointByCoordinates>

                            </pertinentLocation>

                            <forVehiclesWithCharacteristicsOf>

                               <vehicleType>car</vehicleType>

                            </forVehiclesWithCharacteristicsOf>

                            <individualVehicleSpeed>

                               <speed>3.6396303</speed>

                            </individualVehicleSpeed>

                         </basicData>

                      </measuredValue>

                   </measuredValue>

                </siteMeasurements>

             </payloadPublication>

          </d2LogicalModel>

          '

          ) from dual

          )

          select  t.*

          from m2xml cross join xmltable(

          XMLNAMESPACES (default 'http://datex2.eu/schema/2/2_0')

          ,

          '/d2LogicalModel/payloadPublication/siteMeasurements/measuredValue/measuredValue/basicData/pertinentLocation/pointByCoordinates/pointCoordinates'

          passing pointdata

          columns

          lat varchar(10) path 'latitude',

          lon varchar(10) path 'longitude'

          ) t

          ;

           

           

          which field is referred to by id in the select ?

          • 2. Re: Problem with raeding XMLTABLE
            Mettemusens2

            Thanks a lot :-)

             

            There is really no id ....

             

            But I would like to read country and nationalIdentifier

            in

            <exchange>

                  <supplierIdentification>

                     <country>dk</country>

                     <nationalIdentifier>Cowi</nationalIdentifier>

                  </supplierIdentification>

               </exchange>

             

            as well.

            • 3. Re: Problem with raeding XMLTABLE
              mNem

              select  t1.country, t1.nationalidentifier, t2.lat, t2.lon

              from m2xml

              , xmltable(

                  XMLNAMESPACES (default 'http://datex2.eu/schema/2/2_0')

                  ,

                  '/d2LogicalModel'

                  passing pointdata

                  columns

                    country varchar2(30)             path 'exchange/supplierIdentification/country'

                  , nationalIdentifier varchar2(30)  path 'exchange/supplierIdentification/nationalIdentifier'

                  , sm xmltype                       path 'payloadPublication/siteMeasurements'

              ) t1

              , xmltable(

                  XMLNAMESPACES (default 'http://datex2.eu/schema/2/2_0')

                  ,

                  '/siteMeasurements/measuredValue/measuredValue/basicData/pertinentLocation/pointByCoordinates/pointCoordinates'

                  passing t1.sm

                  columns

                    lat varchar(10) path 'latitude'

                  , lon varchar(10) path 'longitude'

              ) t2

              ;

              1 person found this helpful
              • 4. Re: Problem with raeding XMLTABLE
                Mettemusens2

                I had hoped I could use just an extractvalue for this, but theses 2 tries gives me NULL

                 

                like this:

                 

                select  id, t.*

                , extractvalue (pointdata,'//*:d2LogicalModel/exchange/supplierIdentification/nationalIdentifier') nationalIdentifier

                , extractvalue (pointdata,'//*:nationalIdentifier') nationalIdentifier

                from m2xml cross join xmltable(

                XMLNAMESPACES (default 'http://datex2.eu/schema/2/2_0') , '//siteMeasurements'

                passing pointdata

                columns

                lat varchar(10) path '//latitude',

                lon varchar(10) path '//longitude',

                measurementTimeDefault varchar(30) path 'measurementTimeDefault',

                bearing number  path '//bearing',

                indeks          path '//@index',

                nr              FOR ORDINALITY

                ) t

                ;

                • 5. Re: Problem with raeding XMLTABLE
                  mNem

                  - extractvalue() is deprecated, so its usage is not encouraged.

                  - avoid using // to short cut the xpath.

                   

                  Do you have any issues with what was posted earlier? Post if you have.

                   

                  fyi, the call was missing the namespace...

                  extractvalue (pointdata,'/d2LogicalModel/exchange/supplierIdentification/nationalIdentifier', 'xmlns="http://datex2.eu/schema/2/2_0"') nationalIdentifier

                  1 person found this helpful
                  • 6. Re: Problem with raeding XMLTABLE
                    Mettemusens2

                    Hi :-)

                     

                    No the first one worked perfectly - is was just easier to understand the latter for me :-)

                    But I did not realize that it was depriceated, so I'll use the first one.

                     

                    PS   I hate namespaces ... they always mess up .....

                     

                    Have a nice weekend
                    Mette

                    • 7. Re: Problem with raeding XMLTABLE
                      odie_63

                      Mettemusens2 wrote:

                       

                       

                      PS I hate namespaces ... they always mess up .....

                      I hear that quite often

                      However, there's really nothing special about namespaces, once we understand what they are.

                       

                      I wrote a little guide a few years ago : https://odieweblog.wordpress.com/2016/06/07/xml-namespaces-101/

                      Hope it helps in the future.