[SOLVED] Not able to write where condition on complex type with array of tags

Not able to write where condition on complex type with array of tags

Iam new to the XML database, facing issue while writing where condition on id of transportEquipment.

My xml is :

below xml taken from the database. this xml has the multiple id in transportequipment we need to write where condition on id. iam able write where condition on localreferencenumber and identifier and other. but failed to write on "id" of transportEquipment, bcz here these are many.

<?xml version = '1.0' encoding = 'UTF-8' standalone = 'yes'?>
<dms:declaration xsi:schemaLocation="http://www.minihouse.eu/myunidoc/dms/schema/dmsimport" xmlns:common="http://www.minihouse.eu/myunidoc/schema/common" xmlns:dms="http://www.minihouse.eu/myunidoc/dms/schema/dmsimport" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <common:declarationHeader>
      <common:identifier>1183798</common:identifier>
      <common:version>3</common:version>
      <common:kindDeclaration>DMS-IMPORT.NL</common:kindDeclaration>
      <common:localReferenceNumber>NLDMS111111150010950</common:localReferenceNumber>
      <common:initialUserAgent>Human</common:initialUserAgent>
      <common:creationTime>2015-04-24T13:23:00</common:creationTime>
      <common:organization>
         <common:organizationId>100547</common:organizationId>
         <common:organizationName>TestOCHercules</common:organizationName>
         <common:line>adress2</common:line>
         <common:streetNo>2</common:streetNo>
         <common:city>Amsterdam</common:city>
         <common:postalcode>1001 AA</common:postalcode>
         <common:country>NL</common:country>
         <common:permitNumber>NLDMS111111</common:permitNumber>
         <common:x400Number>00</common:x400Number>
         <common:userId>testSC</common:userId>
      </common:organization>
      <common:status>
         <common:customsStatus>DRF</common:customsStatus>
         <common:isOpen>1</common:isOpen>
         <common:processStep>91</common:processStep>
         <common:messageSent>1</common:messageSent>
         <common:messageSuccess>0</common:messageSuccess>
         <common:lastReceivedRelevantMessage>0</common:lastReceivedRelevantMessage>
         <common:isFallBackDeclaration>0</common:isFallBackDeclaration>
      </common:status>
      <common:username>testSC testSC</common:username>
   </common:declarationHeader>
   <dms:dmsHeader></dms:dmsHeader>
   <dms:declarationTypeCode>
      <dms:declarationSymbol>IM</dms:declarationSymbol>
      <dms:declarationType>B</dms:declarationType>
   </dms:declarationTypeCode>
   <dms:goodsShipments>
      <dms:sequenceNumeric>1</dms:sequenceNumeric>
      <dms:consignment sequenceNumeric="1">
         <dms:transportEquipment>
            <dms:sequenceNumeric>1</dms:sequenceNumeric>
            <dms:sealID>1234</dms:sealID>
            <dms:id>abcd</dms:id>
         </dms:transportEquipment>
         <dms:transportEquipment>
            <dms:sequenceNumeric>2</dms:sequenceNumeric>
            <dms:sealID>2345</dms:sealID>
            <dms:id>bcde</dms:id>
         </dms:transportEquipment>
         <dms:transportEquipment>
            <dms:sequenceNumeric>3</dms:sequenceNumeric>
            <dms:sealID>3456</dms:sealID>
            <dms:id>cdef</dms:id>
         </dms:transportEquipment>
         <dms:transportEquipment>
            <dms:sequenceNumeric>4</dms:sequenceNumeric>
            <dms:sealID>4567</dms:sealID>
            <dms:id>defg</dms:id>
         </dms:transportEquipment>
         <dms:transportEquipment>
            <dms:sequenceNumeric>5</dms:sequenceNumeric>
            <dms:sealID>5678</dms:sealID>
            <dms:id>efgh</dms:id>
         </dms:transportEquipment>
      </dms:consignment>
   </dms:goodsShipments>
</dms:declaration>

and Iam not able to add where condition to below query

select * from (select x.id "id",x.creationTime "creationTime",x.localReferenceNumber "localReferenceNumber",x.customsStatus "customsStatus",x.messageSent "messageSent",x.messageSuccess "messageSuccess",x.processStep "processStep",x.lastReceivedRelevantMessage "lastReceivedRelevantMessage",x.isFallBackDeclaration "isFallBackDeclaration",x.declarationType "declarationType" from dmsimport_decl,xmltable(xmlnamespaces('http://www.minihouse.eu/myunidoc/schema/common' as "c",default 'http://www.minihouse.eu/myunidoc/dms/schema/dmsimport','http://www.minihouse.eu/myunidoc/dms/schema/dmsimport' as "d"),'/declaration' passing object_value columns id number(19) path 'c:declarationHeader/c:identifier/text()',creationTime timestamp path 'c:declarationHeader/c:creationTime/text()',localReferenceNumber varchar2(35 char) path 'c:declarationHeader/c:localReferenceNumber/text()',customsStatus varchar2(3) path 'c:declarationHeader/c:status/c:customsStatus/text()',messageSent number(1) path 'c:declarationHeader/c:status/c:messageSent/text()',messageSuccess number(1) path 'c:declarationHeader/c:status/c:messageSuccess/text()',processStep number(4) path 'c:declarationHeader/c:status/c:processStep/text()',lastReceivedRelevantMessage number(4) path 'c:declarationHeader/c:status/c:lastReceivedRelevantMessage/text()',isFallBackDeclaration number(1) path 'c:declarationHeader/c:status/c:isFallBackDeclaration/text()',declarationType varchar2(1) path 'declarationTypeCode/declarationType/text()',username varchar2(81) path 'c:declarationHeader/c:username/text()') x where   lower(localReferenceNumber) = 'nldms1234567890001234' order by creationTime desc,x.localReferenceNumber desc ) where rownum <= 1000

Solution

below query will help

SELECT * FROM XXXtabelXXX XMLTable(
XMLNAMESPACES(  DEFAULT 'http://www.minihouse.eu/myunidoc/dms/schema/dmsimport',
'http://www.minihouse.eu/myunidoc/schema/common' AS "c"),
'/declaration' PASSING d.object_value COLUMNS
  lrn VARCHAR2(35 CHAR) PATH 'c:declarationHeader/c:localReferenceNumber/text()',
  declarationId VARCHAR2(35 CHAR) PATH 'c:declarationHeader/c:identifier/text()',
  transport xmlType PATH 'goodsShipments/consignment/transportEquipment') xmlDataAlias,
  XMLTable (XMLNAMESPACES( DEFAULT 'http://www.minihouse.eu/myunidoc/dms/schema/dmsimport'),
  'transportEquipment' passing transport COLUMNS
  id VARCHAR2(4 CHAR) PATH 'id' ) transportInfo
 WHERE transportInfo.id = 'abcd'