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>