Home » SQL & PL/SQL » SQL & PL/SQL » Remove empty tags from XML (Oracle 11g, XP)
Remove empty tags from XML [message #685814] Thu, 31 March 2022 15:33
rajiv.oradev
Messages: 23
Registered: March 2022
Junior Member
Hi, Need some input to remove blank XML tags.

I am using this but not removing empty tags

SELECT deleteXML( xmldoc

                , '//*[not(node())]'

                ) as result

FROM 
(below XML I have)

Here is the XML

<CaseTypeRow>

            <ProgramType>Medicaid</ProgramType>

            <ActionType>InitialLoad</ActionType>

            <KeyInfo>

                <KeyTypeRow>

                    <KeyType>CaseID</KeyType>

                    <KeyValue>121712520</KeyValue>

                </KeyTypeRow>

                <KeyTypeRow>

                    <KeyType>HouseholdID</KeyType>

                    <KeyValue>14283</KeyValue>

                </KeyTypeRow>

            </KeyInfo>

            <OfficeInfo>

                <AddressRow>

                    <AddressLine1>301 Apache Drive</AddressLine1>

                    <City>Mccomb</City>

                    <State>MS</State>

                    <ZipCode>39648</ZipCode>

                </AddressRow>

                <Phone>6012492071</Phone>

                <FaxNumber>6012494629</FaxNumber>

            </OfficeInfo>

            <CaseWorkerInfo>

                <CaseworkerID>114110</CaseworkerID>

                <EmailID></EmailID>

                <FirstName>CO</FirstName>

                <LastName>MCCOMB7</LastName>

            </CaseWorkerInfo>

            <PersonInfo>

                <PersonRow>

                    <ProgramType>Medicaid</ProgramType>

                    <KeyInfo>

                        <KeyTypeRow>

                            <KeyType>MedicaidID</KeyType>

                            <KeyValue>385288069</KeyValue>

                        </KeyTypeRow>

                        <KeyTypeRow>

                            <KeyType>PersonID</KeyType>

                            <KeyValue>40583</KeyValue>

                        </KeyTypeRow>

                    </KeyInfo>

                    <PersonIdentityInfo>

                        <NameInfo>

                            <FirstName>Tyler</FirstName>

                            <MiddleName>J</MiddleName>

                            <LastName>White</LastName>

                        </NameInfo>

                        <DateOfBirth>2002-08-01</DateOfBirth>

                        <SSN>425930096</SSN>

                        <Gender>Male</Gender>

                        <MaritalStatus>Single</MaritalStatus>

                        <USCitizenshipInd>false</USCitizenshipInd>

                    </PersonIdentityInfo>

                    <ContactInfo>

                        <AddressType>

                            <AddressRow>

                                <AddressCategoryType>Home</AddressCategoryType>

                                <AddressLine1>345 New Home Church Rd</AddressLine1>

                                <City>Jayess</City>

                                <State>MS</State>

                                <ZipCode>39641</ZipCode>

                                <ZipCodeExt>8020</ZipCodeExt>

                                <County>Walthall</County>

                            </AddressRow>

                        </AddressType>

                        <PhoneType>

                            <PhoneRow>

                                <PhoneCategoryType>CELL</PhoneCategoryType>

                                <Phone>6016690013</Phone>

                                <PhoneExt></PhoneExt>

                            </PhoneRow>

                        </PhoneType>

                        <EmailID>Whitekathy20@yahoo.com</EmailID>

                    </ContactInfo>

                    <EligibilityInfo>

                        <EligibilityDetailtRow>

                            <ProgramType>Medicaid</ProgramType>

                            <EligibilityBeginDate>2021-09-01-05:00</EligibilityBeginDate>

                            <CategoryOfEligibilityType>Child</CategoryOfEligibilityType>

                            <DecisionStatus>Approved</DecisionStatus>

                        </EligibilityDetailtRow>

                    </EligibilityInfo>

                    <IncarcerationInfo>

                        <IncarcerationRow/>

                    </IncarcerationInfo>

                    <FamilyCompositionInfo>

                        <RelationshipToHOH>Child</RelationshipToHOH>

                    </FamilyCompositionInfo>

                </PersonRow>

                <PersonRow>

                    <ProgramType>Medicaid</ProgramType>

                    <KeyInfo>

                        <KeyTypeRow>

                            <KeyType>MedicaidID</KeyType>

                            <KeyValue>309806174</KeyValue>

                        </KeyTypeRow>

                        <KeyTypeRow>

                            <KeyType>PersonID</KeyType>

                            <KeyValue>40548</KeyValue>

                        </KeyTypeRow>

                    </KeyInfo>

                    <PersonIdentityInfo>

                        <NameInfo>

                            <FirstName>Kathy</FirstName>

                            <MiddleName>R</MiddleName>

                            <LastName>White</LastName>

                        </NameInfo>

                        <DateOfBirth>1960-02-20</DateOfBirth>

                        <SSN>427175897</SSN>

                        <Gender>Female</Gender>

                        <MaritalStatus>Single</MaritalStatus>

                        <USCitizenshipInd>false</USCitizenshipInd>

                    </PersonIdentityInfo>

                    <ContactInfo>

                        <AddressType>

                            <AddressRow>

                                <AddressCategoryType>Home</AddressCategoryType>

                                <AddressLine1>345 New Home Church Rd</AddressLine1>

                                <City>Jayess</City>

                                <State>MS</State>

                                <ZipCode>39641</ZipCode>

                                <ZipCodeExt>8020</ZipCodeExt>

                                <County>Walthall</County>

                            </AddressRow>

                        </AddressType>

                        <PhoneType>

                            <PhoneRow>

                                <PhoneCategoryType>CELL</PhoneCategoryType>

                                <Phone>6016690013</Phone>

                                <PhoneExt></PhoneExt>

                            </PhoneRow>

                        </PhoneType>

                        <EmailID>Whitekathy20@yahoo.com</EmailID>

                    </ContactInfo>

                    <EligibilityInfo>

                        <EligibilityDetailtRow>

                            <ProgramType>Medicaid</ProgramType>

                            <EligibilityBeginDate>2021-12-01-06:00</EligibilityBeginDate>

                            <CategoryOfEligibilityType>Parent/Caretaker</CategoryOfEligibilityType>

                            <DecisionStatus>Denied</DecisionStatus>

                            <DecisionReason>No One Under 18 at Home</DecisionReason>

                        </EligibilityDetailtRow>

                    </EligibilityInfo>

                    <IncarcerationInfo>

                        <IncarcerationRow/>

                    </IncarcerationInfo>

                    <IncomeInfo>

                        <IncomeRow>

                            <IncomeSource>Verified - TALX</IncomeSource>

                            <IncomeCategoryType>Earnings, Wages, Salaries</IncomeCategoryType>

                            <IncomeAmount>0.0</IncomeAmount>

                            <IncomeFrequency>Annually</IncomeFrequency>

                            <IncomeStartDate>2018-08-24</IncomeStartDate>

                            <EmploymentInfo>

                                <EmployerName>Oasis</EmployerName>

                                <EmployementStartDate>2018-08-24</EmployementStartDate>

                                <AddressRow>

                                    <City>West Palm Beach</City>

                                </AddressRow>

                                <PhoneRow/>

                            </EmploymentInfo>

                        </IncomeRow>

                        <IncomeRow>

                            <IncomeSource>Failed to Verify</IncomeSource>

                            <IncomeCategoryType>Earnings, Wages, Salaries</IncomeCategoryType>

                            <IncomeAmount>1052.0</IncomeAmount>

                            <IncomeFrequency>Annually</IncomeFrequency>

                            <EmploymentInfo>

                                <EmployerName>Self-Employment</EmployerName>

                                <AddressRow/>

                                <PhoneRow/>

                            </EmploymentInfo>

                        </IncomeRow>

                        <IncomeRow>

                            <IncomeSource>Failed to Verify</IncomeSource>

                            <IncomeCategoryType>Earnings, Wages, Salaries</IncomeCategoryType>

                            <IncomeAmount>4810.0</IncomeAmount>

                            <IncomeFrequency>Annually</IncomeFrequency>

                            <EmploymentInfo>

                                <EmployerName>Walthall County Schools</EmployerName>

                                <AddressRow/>

                                <PhoneRow/>

                            </EmploymentInfo>

                        </IncomeRow>

                    </IncomeInfo>

                    <FamilyCompositionInfo>

                        <RelationshipToHOH>Self</RelationshipToHOH>

                    </FamilyCompositionInfo>

                    <ExpenseInfo/>

                </PersonRow>

            </PersonInfo>

        </CaseTypeRow>

      

<ArrayOfCaseTypeRow>

           <CaseTypeRow>

                      <ProgramType>Medicaid</ProgramType>

                      <ActionType>InitialLoad</ActionType>

                      <KeyInfo>

                                 <KeyTypeRow>

                                            <KeyType>CaseID</KeyType>

                                            <KeyValue>121712520</KeyValue>

                                 </KeyTypeRow>

                                 <KeyTypeRow>

                                            <KeyType>HouseholdID</KeyType>

                                            <KeyValue>14283</KeyValue>

                                 </KeyTypeRow>

                      </KeyInfo>

                      <OfficeInfo>

                                 <AddressRow>

                                            <AddressLine1>301 Apache Drive</AddressLine1>

                                            <City>Mccomb</City>

                                            <State>MS</State>

                                            <ZipCode>39648</ZipCode>

                                 </AddressRow>

                                 <Phone>6012492071</Phone>

                                 <FaxNumber>6012494629</FaxNumber>

                      </OfficeInfo>

                      <CaseWorkerInfo>

                                 <CaseworkerID>114110</CaseworkerID>

                                 <FirstName>CO</FirstName>

                                 <LastName>MCCOMB7</LastName>

                      </CaseWorkerInfo>

                      <PersonInfo>

                                 <PersonRow>

                                            <ProgramType>Medicaid</ProgramType>

                                            <KeyInfo>

                                                       <KeyTypeRow>

                                                                  <KeyType>MedicaidID</KeyType>

                                                                  <KeyValue>385288069</KeyValue>

                                                       </KeyTypeRow>

                                                       <KeyTypeRow>

                                                                  <KeyType>PersonID</KeyType>

                                                                  <KeyValue>40583</KeyValue>

                                                       </KeyTypeRow>

                                            </KeyInfo>

                                            <PersonIdentityInfo>

                                                       <NameInfo>

                                                                  <FirstName>Tyler</FirstName>

                                                                  <MiddleName>J</MiddleName>

                                                                  <LastName>White</LastName>

                                                       </NameInfo>

                                                       <DateOfBirth>2002-08-01</DateOfBirth>

                                                       <SSN>425930096</SSN>

                                                       <Gender>Male</Gender>

                                                       <MaritalStatus>Single</MaritalStatus>

                                                       <USCitizenshipInd>false</USCitizenshipInd>

                                            </PersonIdentityInfo>

                                            <ContactInfo>

                                                       <AddressType>

                                                                  <AddressRow>

                                                                             <AddressCategoryType>Home</AddressCategoryType>

                                                                             <AddressLine1>345 New Home Church Rd</AddressLine1>

                                                                             <City>Jayess</City>

                                                                             <State>MS</State>

                                                                             <ZipCode>39641</ZipCode>

                                                                             <ZipCodeExt>8020</ZipCodeExt>

                                                                             <County>Walthall</County>

                                                                  </AddressRow>

                                                                  <AddressRow>

                                                                             <AddressCategoryType>Mailing</AddressCategoryType>

                                                                             <County>Walthall</County>

                                                                  </AddressRow>

                                                       </AddressType>

                                                       <PhoneType>

                                                                  <PhoneRow/>

                                                                  <PhoneRow/>

                                                                  <PhoneRow>

                                                                             <PhoneCategoryType>CELL</PhoneCategoryType>

                                                                             <Phone>6016690013</Phone>

                                                                  </PhoneRow>

                                                                  <PhoneRow/>

                                                       </PhoneType>

                                                       <EmailID>Whitekathy20@yahoo.com</EmailID>

                                            </ContactInfo>

                                            <EligibilityInfo>

                                                       <EligibilityDetailRow>

                                                                  <ProgramType>Medicaid</ProgramType>

                                                                  <EligibilityBeginDate>2021-09-01</EligibilityBeginDate>

                                                                  <CategoryOfEligibilityType>Child</CategoryOfEligibilityType>

                                                                  <DecisionStatus>Approved</DecisionStatus>

                                                       </EligibilityDetailRow>

                                            </EligibilityInfo>

                                            <IncarcerationInfo/>

                                            <IncomeInfo>

                                                       <IncomeRow>

                                                                  <EmploymentInfo>

                                                                             <AddressRow/>

                                                                             <PhoneRow/>

                                                                  </EmploymentInfo>

                                                       </IncomeRow>

                                            </IncomeInfo>

                                            <ResourceInfo>

                                                       <ResourceRow/>

                                            </ResourceInfo>

                                            <FamilyCompositionInfo>

                                                       <RelationshipToHOH>Child</RelationshipToHOH>

                                            </FamilyCompositionInfo>

                                            <ExpenseInfo>

                                                       <ExpenseRow/>

                                            </ExpenseInfo>

                                 </PersonRow>

                                 <PersonRow>

                                            <ProgramType>Medicaid</ProgramType>

                                            <KeyInfo>

                                                       <KeyTypeRow>

                                                                  <KeyType>MedicaidID</KeyType>

                                                                  <KeyValue>309806174</KeyValue>

                                                       </KeyTypeRow>

                                                       <KeyTypeRow>

                                                                  <KeyType>PersonID</KeyType>

                                                                  <KeyValue>40548</KeyValue>

                                                       </KeyTypeRow>

                                            </KeyInfo>

                                            <PersonIdentityInfo>

                                                       <NameInfo>

                                                                  <FirstName>Kathy</FirstName>

                                                                  <MiddleName>R</MiddleName>

                                                                  <LastName>White</LastName>

                                                       </NameInfo>

                                                       <DateOfBirth>1960-02-20</DateOfBirth>

                                                       <SSN>427175897</SSN>

                                                       <Gender>Female</Gender>

                                                       <MaritalStatus>Single</MaritalStatus>

                                                       <USCitizenshipInd>false</USCitizenshipInd>

                                            </PersonIdentityInfo>

                                            <ContactInfo>

                                                       <AddressType>

                                                                  <AddressRow>

                                                                             <AddressCategoryType>Home</AddressCategoryType>

                                                                             <AddressLine1>345 New Home Church Rd</AddressLine1>

                                                                             <City>Jayess</City>

                                                                             <State>MS</State>

                                                                             <ZipCode>39641</ZipCode>

                                                                             <ZipCodeExt>8020</ZipCodeExt>

                                                                             <County>Walthall</County>

                                                                  </AddressRow>

                                                                  <AddressRow>

                                                                             <AddressCategoryType>Mailing</AddressCategoryType>

                                                                             <County>Walthall</County>

                                                                  </AddressRow>

                                                       </AddressType>

                                                       <PhoneType>

                                                                  <PhoneRow/>

                                                                  <PhoneRow/>

                                                                  <PhoneRow>

                                                                             <PhoneCategoryType>CELL</PhoneCategoryType>

                                                                             <Phone>6016690013</Phone>

                                                                  </PhoneRow>

                                                                  <PhoneRow/>

                                                       </PhoneType>

                                                       <EmailID>Whitekathy20@yahoo.com</EmailID>

                                            </ContactInfo>

                                            <EligibilityInfo>

                                                       <EligibilityDetailRow>

                                                                  <ProgramType>Medicaid</ProgramType>

                                                                  <EligibilityBeginDate>2021-12-01</EligibilityBeginDate>

                                                                  <CategoryOfEligibilityType>Parent/Caretaker</CategoryOfEligibilityType>

                                                                  <DecisionStatus>Denied</DecisionStatus>

                                                                  <DecisionReason>No One Under 18 at Home</DecisionReason>

                                                       </EligibilityDetailRow>

                                            </EligibilityInfo>

                                            <IncarcerationInfo/>

                                            <IncomeInfo>

                                                       <IncomeRow>

                                                                  <IncomeSource>Verified - TALX</IncomeSource>

                                                                  <IncomeCategoryType>Earnings, Wages, Salaries</IncomeCategoryType>

                                                                  <IncomeAmount>0.00</IncomeAmount>

                                                                  <IncomeFrequency>Annually</IncomeFrequency>

                                                                  <IncomeStartDate>2018-08-24</IncomeStartDate>

                                                                  <EmploymentInfo>

                                                                             <EmployerName>Oasis</EmployerName>

                                                                             <EmploymentStartDate>2018-08-24</EmploymentStartDate>

                                                                             <AddressRow>

                                                                                        <AddressLine1>2054 Vista Parkway #300</AddressLine1>

                                                                                        <City>West Palm Beach</City>

                                                                             </AddressRow>

                                                                             <PhoneRow/>

                                                                  </EmploymentInfo>

                                                       </IncomeRow>

                                                       <IncomeRow>

                                                                  <IncomeSource>Failed to Verify</IncomeSource>

                                                                  <IncomeCategoryType>Earnings, Wages, Salaries</IncomeCategoryType>

                                                                  <IncomeAmount>1052.00</IncomeAmount>

                                                                  <IncomeFrequency>Annually</IncomeFrequency>

                                                                  <EmploymentInfo>

                                                                             <EmployerName>Self-Employment</EmployerName>

                                                                             <AddressRow/>

                                                                             <PhoneRow/>

                                                                  </EmploymentInfo>

                                                       </IncomeRow>

                                                       <IncomeRow>

                                                                  <IncomeSource>Failed to Verify</IncomeSource>

                                                                  <IncomeCategoryType>Earnings, Wages, Salaries</IncomeCategoryType>

                                                                  <IncomeAmount>4810.00</IncomeAmount>

                                                                  <IncomeFrequency>Annually</IncomeFrequency>

                                                                  <EmploymentInfo>

                                                                             <EmployerName>Walthall County Schools</EmployerName>

                                                                             <AddressRow/>

                                                                             <PhoneRow/>

                                                                  </EmploymentInfo>

                                                       </IncomeRow>

                                            </IncomeInfo>

                                            <ResourceInfo>

                                                       <ResourceRow/>

                                            </ResourceInfo>

                                            <FamilyCompositionInfo>

                                                       <RelationshipToHOH>Self</RelationshipToHOH>

                                            </FamilyCompositionInfo>

                                            <ExpenseInfo>

                                                       <ExpenseRow/>

                                            </ExpenseInfo>

                                 </PersonRow>

                      </PersonInfo>

           </CaseTypeRow>

</ArrayOfCaseTypeRow>
Previous Topic: ORA-01722 Invalid Number for numeric values.
Next Topic: Oracle Update to get data from other tables using join
Goto Forum:
  


Current Time: Thu Mar 28 05:39:34 CDT 2024