CODEWITHSUNDEEP
oracle-databaseqxmlquery
Peter Barnum
Peter Barnum

Reputation: 11

XMLQuery with mult namespaces

Using Oracle 12c

This seems so simple but I must have a mental block on how to get a simple element from an xml string. The following produces a null value instead of 2017. I have tried many different approaches but would like to stick with xmlQuery instead of Extract. Not sure a for is needed but this at least compiles and produces some result. Any thoughts on what I am doing wrong?

with stuff AS (Select xmltype('<singleStreamlinedApplication xmlns:ns1="http://www.YYYYY.org/SSA/Person" xmlns="http://www.YYYYY.org/SSA/Person">
   <ns1:schemaVersion>2.16</ns1:schemaVersion>
   <ns1:changeSource>VVVVVV</ns1:changeSource>
   <ns1:cbmsApplicationId>333333</ns1:cbmsApplicationId>
   <ns1:cbmsCaseId>2222222</ns1:cbmsCaseId>
   <ns1:cbmsAuthorizationId>11111111</ns1:cbmsAuthorizationId>
   <ns1:applicationDate>2012-09-03</ns1:applicationDate>
   <ns1:cbmsUpdateTimestamp>2017-04-02T20:21:34.000Z</ns1:cbmsUpdateTimestamp>
   <ns1:eligibilityDeterminationDate>2017-04-02</ns1:eligibilityDeterminationDate>
<ns1:eligibilityPurpose>QUALIFIED_LIFE_CHANGE_EVENT</ns1:eligibilityPurpose>
<ns1:applicationCoverageYear>2017</ns1:applicationCoverageYear>
<ns1:peakAccountId>5555555</ns1:peakAccountId>
<ns1:applyingForWho>CONTACT_NAME_AND_OTHER_FAMILY_MEMBERS</ns1:applyingForWho>
<ns1:applyingForFinancialAssistanceIndicator>true</ns1:applyingForFinancialAssistanceIndicator>
<ns1:agreement>
<ns1:noHouseholdMemberIncarceratedIndicator>true</ns1:noHouseholdMemberIncarceratedIndicator>
<ns1:dontRenewEligibilityForFinancialBenefitsIndicator>true</ns1:dontRenewEligibilityForFinancialBenefitsIndicator>
<ns1:electronicSignatureIndicator>true</ns1:electronicSignatureIndicator>
</ns1:agreement>
<ns1:taxHousehold>
<ns1:cbmsTaxHouseholdId>999999</ns1:cbmsTaxHouseholdId>
<ns1:cbmsMagiIncomeAmount>0.0</ns1:cbmsMagiIncomeAmount>
<ns1:cbmsFederalPovertyLevel>0</ns1:cbmsFederalPovertyLevel>
<ns1:householdMember>
<ns1:cbmsPersonId>8888888</ns1:cbmsPersonId>
<ns1:name>
<ns1:firstName>XXXXX</ns1:firstName>
<ns1:lastName>YYYYY</ns1:lastName>
</ns1:name>
<ns1:dateOfBirth>1900-10-06</ns1:dateOfBirth>
<ns1:householdContactIndicator>false</ns1:householdContactIndicator>
<ns1:applyingForCoverageIndicator>true</ns1:applyingForCoverageIndicator>
<ns1:relationshipTypeToHouseholdContact>SON_OR_DAUGHTER</ns1:relationshipTypeToHouseholdContact>
<ns1:livesWithHouseholdContactIndicator>true</ns1:livesWithHouseholdContactIndicator>
<ns1:planToFileFTRIndicator>false</ns1:planToFileFTRIndicator>
<ns1:claimedAsDependantOnFTRIndicator>false</ns1:claimedAsDependantOnFTRIndicator>
<ns1:claimedAsDependantOnSomeoneElsesFTRIndicator>false</ns1:claimedAsDependantOnSomeoneElsesFTRIndicator>
<ns1:gender>FEMALE</ns1:gender>
<ns1:parentCaretakerIndicator>true</ns1:parentCaretakerIndicator>
<ns1:disabledIndicator>false</ns1:disabledIndicator>
<ns1:lifeChangeEvent>
<ns1:lifeStatusChangeReportingDate>2017-04-02</ns1:lifeStatusChangeReportingDate>
<ns1:lifeStatusEventDate>2017-04-02</ns1:lifeStatusEventDate>
<ns1:qualifiedLifeChangeEvent>CHANGE_OF_INCOME</ns1:qualifiedLifeChangeEvent>
</ns1:lifeChangeEvent>
</ns1:householdMember>
</ns1:taxHousehold>
</singleStreamlinedApplication>')
xmlcol from dual
)
Select xmlquery('xquery version "1.0";
 declare namespace ns1="http://www.YYYYY.org/SSA/Person";
for $i in /singleStreamlinedApplicationRequest
return $i/ns1:applicationCoverageYear' PASSING  stuff.xmlcol RETURNING CONTENT) plan_year
from stuff

Upvotes: -1

Views: 3023

Answers (1)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

Select xmlquery('xquery version "1.0";
declare default element namespace "http://www.YYYYY.org/SSA/Person";
declare namespace ns1="http://www.YYYYY.org/SSA/Person";
for $i in /singleStreamlinedApplication
return $i/ns1:applicationCoverageYear'
PASSING  stuff.xmlcol RETURNING CONTENT) plan_year
from stuff

Root element is singleStreamlinedApplication and this elemnt is in namespace xmlns="http://www.YYYYY.org/SSA/Person". If you don't include declare default element namespace oracle try to get this element from defult namespace.

Upvotes: 1

Related Questions