apadyLiabnono
New Member
Im trying to query xml files in oracle tables to create a view. Im having trouble using multiple namespaces to query data. For example planStructures is part of the prd: namesspace and the identifiers within externalProductIdentifiers are part of util: namespace. Following is the sql using to query and a snippet of the xml file\[code\] XMLTABLE (XMLNameSpaces( DEFAULT 'http://www.cigna.com/ifp/domains/product/2012/06'),'/planStructure' PASSING root_planStructures.planStructure COLUMNS plnStrctExtPrdId XMLTYPE PATH 'externalProductIdentifiers', plnStrctInfo XMLTYPE PATH 'planStructureInformation', clientInfo XMLTYPE PATH 'clientInformation', financialStruct XMLTYPE PATH 'financialStructure' ) root_planStructure, XMLTABLE (XMLNameSpaces( DEFAULT 'http://www.cigna.com/ifp/domains/product/2012/06'),'/externalProductIdentifiers' PASSING root_planStructure.plnStrctExtPrdId COLUMNS ExtPrdIdUtilID XMLTYPE PATH 'identifier' ) plnStrctPlnStrctExtPrdIdXMLTABLE (DEFAULT 'http://www.cigna.com/ifp/domains/utility/2012/06'), '/identifier' PASSING plnStrctPlnStrctExtPrdId.ExtPrdIdUtilID COLUMNS idType VARCHAR2 (100) PATH '@type', idDate VARCHAR2 (100) PATH '@date', singleValueType VARCHAR2 (100) PATH 'singleValueID/@type', singleValueDate VARCHAR2 (100) PATH 'singleValueID/@date', singleValueID VARCHAR2 (100) PATH 'singleValueID' ) externalProductIdentifiersID\[/code\]sample xml snippet: \[code\]<planStructures> <planStructure> <externalProductIdentifiers> <util:identifier type="HPS"> <util:singleValueID type="HPSPlanID">PPO50SC</util:singleValueID> </util:identifier> <util:identifier type="Colibrium"> <util:singleValueID type="ColibriumPlanID">PPO50SC</util:singleValueID> </util:identifier> <util:identifier type="Salesforce"> <util:singleValueID type="SalesforcePlanID">a0kV0000000SnSiIAK</util:singleValueID> </util:identifier> </externalProductIdentifiers>\[/code\]