Home » SQL & PL/SQL » SQL & PL/SQL » XML Generation (Oracle 11g, XP)
| XML Generation [message #685757] |
Thu, 17 March 2022 20:58  |
 |
rajiv.oradev
Messages: 15
Registered: March 2022
|
Junior Member |
|
|
Hi I am new to XML and I am trying to get the output but I am gettng 9 rows instead of 1.
The XML format is attached.
Drop table a;
CREATE TABLE A
( household_id NUMBER(10,0),
person_id number(10,0)
) ;
Insert into a values (95,236);
Insert into a values (95,268);
Insert into a values (95,281);
CREATE TABLE B
( "TB4_ID_PK" NUMBER(10,0),
"HOUSEHOLD_ID" NUMBER(10,0) NOT NULL ENABLE,
"PERSONID" NUMBER(10,0) NOT NULL ENABLE,
"CONSUMER_ID" NUMBER(10,0) NOT NULL ENABLE,
"MEDICAIDID" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"DOB" VARCHAR2(10 BYTE),
"CASENUMBER" VARCHAR2(25 BYTE),
"HOHPERSONID" NUMBER(10,0),
"HOH_IND" VARCHAR2(3 BYTE),
"APPLYING_FOR" VARCHAR2(4 BYTE),
"CONSUMER_STATUS_CODE" VARCHAR2(4 BYTE) NOT NULL ENABLE,
"CONSUMER_STATUS_START_DATE" DATE NOT NULL ENABLE,
"CONSUMER_STATUS_END_DATE" DATE,
"DECISION_STATUS_CODE" VARCHAR2(4 BYTE),
"ELIGIBILITY_STATUS_BEGIN_DATE" VARCHAR2(10 BYTE),
"ELIGIBILITY_STATUS_END_DATE" VARCHAR2(10 BYTE),
"FIRST_NAME" VARCHAR2(25 BYTE),
"MIDDLE_INITIAL" VARCHAR2(20 BYTE),
"LAST_NAME" VARCHAR2(35 BYTE) NOT NULL ENABLE,
"ADDRESS_LINE1" VARCHAR2(35 BYTE),
"ADDRESS_LINE2" VARCHAR2(35 BYTE),
"CITY" VARCHAR2(40 BYTE),
"STATE" VARCHAR2(2 BYTE),
"ZIPCODE_DESC" VARCHAR2(10 BYTE),
"ZIPCODE_EXT" VARCHAR2(5 BYTE),
"COUNTY" VARCHAR2(40 BYTE),
"REGIIONALOFFICEPHONENUMBER" VARCHAR2(27 BYTE),
"REGIIONALOFFICEFAXNUMBER" VARCHAR2(21 BYTE),
"CASEWORKERID" NUMBER(10,0),
"CASEWORKEREMAILID" VARCHAR2(100 BYTE),
"CASEWORKERFIRSTNAME" VARCHAR2(50 BYTE),
"CASEWORKERLASTNAME" VARCHAR2(50 BYTE),
"SUFFIXNAME" VARCHAR2(10 BYTE),
"SSN" VARCHAR2(25 BYTE),
"DEATHDATE" VARCHAR2(10 BYTE),
"USCITIZENSHIPIND" VARCHAR2(5 BYTE),
"CELLPHONE" VARCHAR2(4 BYTE),
"CELLPHONENUMBER" VARCHAR2(10 BYTE),
"CELLPHONEEXTENSION" VARCHAR2(5 BYTE),
"HOMEPHONE" VARCHAR2(4 BYTE),
"HOMEPHONENUMBER" VARCHAR2(10 BYTE),
"HOMEPHONEEXTENSION" VARCHAR2(5 BYTE),
"WORKPHONE" VARCHAR2(4 BYTE),
"WORKPHONENUMBER" VARCHAR2(10 BYTE),
"WORKPHONEEXTENSION" VARCHAR2(5 BYTE),
"OTHERPHONE" VARCHAR2(4 BYTE),
"OTHERPHONENUMBER" VARCHAR2(10 BYTE),
"OTHERPHONEEXTENSION" VARCHAR2(5 BYTE),
"HOUSEHOLDEMAILADDRESS" VARCHAR2(256 BYTE),
"CATEGORYOFELIGIBILITYTYPE" VARCHAR2(4 BYTE),
"INCARCERATIONDATE" DATE,
"INCARCERATIONRELEASEDATE" DATE,
"REGIONALOFFICESTREETADDRESS1" VARCHAR2(35 BYTE),
"REGIONALOFFICESTREETADDRESS2" VARCHAR2(35 BYTE),
"REGIONALOFFICECITY" VARCHAR2(40 BYTE),
"REGIONALOFFICESTATE" VARCHAR2(2 BYTE),
"REGIONALOFFICEZIP" VARCHAR2(10 BYTE),
"REGIONALOFFICEZIPEXT" VARCHAR2(5 BYTE),
"REGIONALOFFICECOUNTY" VARCHAR2(40 BYTE),
"MAILING_ADDRESS_LINE1" VARCHAR2(35 BYTE),
"MAILING_ADDRESS_LINE2" VARCHAR2(35 BYTE),
"MAILING_CITY" VARCHAR2(40 BYTE),
"MAILING_STATE" VARCHAR2(2 BYTE),
"MAILING_ZIPCODE" VARCHAR2(10 BYTE),
"MAILING_ZIPCODE_EXT" VARCHAR2(5 BYTE),
"MAILING_COUNTY" VARCHAR2(40 BYTE),
"DECISIONSTATUSCODEDESC" VARCHAR2(80 BYTE),
"GENDERDESC" VARCHAR2(80 BYTE),
"MARITALSTATUSDESC" VARCHAR2(80 BYTE),
"ELIGIBILITYDECISIONID" NUMBER(10,0),
"ELIGIBILITYDECISIONPROGRAMID" NUMBER(10,0),
"RELATIONSHIPTOHOH" VARCHAR2(80 BYTE),
"DECISIONREASON" VARCHAR2(80 BYTE)
) ;
Insert into B (TB4_ID_PK,HOUSEHOLD_ID,PERSONID,CONSUMER_ID,MEDICAIDID,DOB,CASENUMBER,HOHPERSONID,HOH_IND,APPLYING_FOR,CONSUMER_STATUS_CODE,CONSUMER_STATUS_START_DATE,CONSUMER_STATUS_END_DATE,DECISION_STATUS_CODE,ELIGIBILITY_STATUS_BEGIN_DATE,ELIGIBILITY_STATUS_END_DATE,FIRST_NAME,MIDDLE_INITIAL,LAST_NAME,ADDRESS_LINE1,ADDRESS_LINE2,CITY,STATE,ZIPCODE_DESC,ZIPCODE_EXT,COUNTY,REGIIONALOFFICEPHONENUMBER,REGIIONALOFFICEFAXNUMBER,CASEWORKERID,CASEWORKEREMAILID,CASEWORKERFIRSTNAME,CASEWORKERLASTNAME,SUFFIXNAME,SSN,DEATHDATE,USCITIZENSHIPIND,CELLPHONE,CELLPHONENUMBER,CELLPHONEEXTENSION,HOMEPHONE,HOMEPHONENUMBER,HOMEPHONEEXTENSION,WORKPHONE,WORKPHONENUMBER,WORKPHONEEXTENSION,OTHERPHONE,OTHERPHONENUMBER,OTHERPHONEEXTENSION,HOUSEHOLDEMAILADDRESS,CATEGORYOFELIGIBILITYTYPE,INCARCERATIONDATE,INCARCERATIONRELEASEDATE,REGIONALOFFICESTREETADDRESS1,REGIONALOFFICESTREETADDRESS2,REGIONALOFFICECITY,REGIONALOFFICESTATE,REGIONALOFFICEZIP,REGIONALOFFICEZIPEXT,REGIONALOFFICECOUNTY,MAILING_ADDRESS_LINE1,MAILING_ADDRESS_LINE2,MAILING_CITY,MAILING_STATE,MAILING_ZIPCODE,MAILING_ZIPCODE_EXT,MAILING_COUNTY,DECISIONSTATUSCODEDESC,GENDERDESC,MARITALSTATUSDESC,ELIGIBILITYDECISIONID,ELIGIBILITYDECISIONPROGRAMID,RELATIONSHIPTOHOH,DECISIONREASON) values (7187,95,236,29174906,'301869568','1947-09-15','101569748',236,'Yes','ABD','PROC',to_date('01-NOV-21','DD-MON-RR'),null,'A','2021-11-01',null,'Annie','B','Jaynes','401 Lilac St',null,'Hattiesburg','MS','39401','5501','Lamar','6012645386','6012611244',122710,'Ontario.Lewis@medicaid.ms.gov','Ontario','Lewis',null,'428985760',null,'false','CELL','6015960680',null,null,null,null,null,null,null,null,null,null,null,'051',null,null,'6971 Lincoln Rd Ext',null,'Hattiesburg','MS','39402','3227',null,null,null,null,null,null,null,null,'Approved','Female','Widowed',165839036,22,'Self',null);
Insert into B (TB4_ID_PK,HOUSEHOLD_ID,PERSONID,CONSUMER_ID,MEDICAIDID,DOB,CASENUMBER,HOHPERSONID,HOH_IND,APPLYING_FOR,CONSUMER_STATUS_CODE,CONSUMER_STATUS_START_DATE,CONSUMER_STATUS_END_DATE,DECISION_STATUS_CODE,ELIGIBILITY_STATUS_BEGIN_DATE,ELIGIBILITY_STATUS_END_DATE,FIRST_NAME,MIDDLE_INITIAL,LAST_NAME,ADDRESS_LINE1,ADDRESS_LINE2,CITY,STATE,ZIPCODE_DESC,ZIPCODE_EXT,COUNTY,REGIIONALOFFICEPHONENUMBER,REGIIONALOFFICEFAXNUMBER,CASEWORKERID,CASEWORKEREMAILID,CASEWORKERFIRSTNAME,CASEWORKERLASTNAME,SUFFIXNAME,SSN,DEATHDATE,USCITIZENSHIPIND,CELLPHONE,CELLPHONENUMBER,CELLPHONEEXTENSION,HOMEPHONE,HOMEPHONENUMBER,HOMEPHONEEXTENSION,WORKPHONE,WORKPHONENUMBER,WORKPHONEEXTENSION,OTHERPHONE,OTHERPHONENUMBER,OTHERPHONEEXTENSION,HOUSEHOLDEMAILADDRESS,CATEGORYOFELIGIBILITYTYPE,INCARCERATIONDATE,INCARCERATIONRELEASEDATE,REGIONALOFFICESTREETADDRESS1,REGIONALOFFICESTREETADDRESS2,REGIONALOFFICECITY,REGIONALOFFICESTATE,REGIONALOFFICEZIP,REGIONALOFFICEZIPEXT,REGIONALOFFICECOUNTY,MAILING_ADDRESS_LINE1,MAILING_ADDRESS_LINE2,MAILING_CITY,MAILING_STATE,MAILING_ZIPCODE,MAILING_ZIPCODE_EXT,MAILING_COUNTY,DECISIONSTATUSCODEDESC,GENDERDESC,MARITALSTATUSDESC,ELIGIBILITYDECISIONID,ELIGIBILITYDECISIONPROGRAMID,RELATIONSHIPTOHOH,DECISIONREASON) values (34005,95,281,28451269,'385792441','2003-10-10','101569748',236,'No','MAGI','PROC',to_date('01-JUL-21','DD-MON-RR'),null,'A','2021-07-01',null,'Michael','J','Hubbard','401 Lilac St',null,'Hattiesburg','MS','39401','5501','Lamar','6012645386','6012611244',122710,'Ontario.Lewis@medicaid.ms.gov','Ontario','Lewis',null,'425952940',null,'false','CELL','6015960680',null,null,null,null,null,null,null,null,null,null,null,'073',null,null,'6971 Lincoln Rd Ext',null,'Hattiesburg','MS','39402','3227',null,null,null,null,null,null,null,null,'Approved','Male','Single',164825122,61,'Grandchild',null);
Insert into B (TB4_ID_PK,HOUSEHOLD_ID,PERSONID,CONSUMER_ID,MEDICAIDID,DOB,CASENUMBER,HOHPERSONID,HOH_IND,APPLYING_FOR,CONSUMER_STATUS_CODE,CONSUMER_STATUS_START_DATE,CONSUMER_STATUS_END_DATE,DECISION_STATUS_CODE,ELIGIBILITY_STATUS_BEGIN_DATE,ELIGIBILITY_STATUS_END_DATE,FIRST_NAME,MIDDLE_INITIAL,LAST_NAME,ADDRESS_LINE1,ADDRESS_LINE2,CITY,STATE,ZIPCODE_DESC,ZIPCODE_EXT,COUNTY,REGIIONALOFFICEPHONENUMBER,REGIIONALOFFICEFAXNUMBER,CASEWORKERID,CASEWORKEREMAILID,CASEWORKERFIRSTNAME,CASEWORKERLASTNAME,SUFFIXNAME,SSN,DEATHDATE,USCITIZENSHIPIND,CELLPHONE,CELLPHONENUMBER,CELLPHONEEXTENSION,HOMEPHONE,HOMEPHONENUMBER,HOMEPHONEEXTENSION,WORKPHONE,WORKPHONENUMBER,WORKPHONEEXTENSION,OTHERPHONE,OTHERPHONENUMBER,OTHERPHONEEXTENSION,HOUSEHOLDEMAILADDRESS,CATEGORYOFELIGIBILITYTYPE,INCARCERATIONDATE,INCARCERATIONRELEASEDATE,REGIONALOFFICESTREETADDRESS1,REGIONALOFFICESTREETADDRESS2,REGIONALOFFICECITY,REGIONALOFFICESTATE,REGIONALOFFICEZIP,REGIONALOFFICEZIPEXT,REGIONALOFFICECOUNTY,MAILING_ADDRESS_LINE1,MAILING_ADDRESS_LINE2,MAILING_CITY,MAILING_STATE,MAILING_ZIPCODE,MAILING_ZIPCODE_EXT,MAILING_COUNTY,DECISIONSTATUSCODEDESC,GENDERDESC,MARITALSTATUSDESC,ELIGIBILITYDECISIONID,ELIGIBILITYDECISIONPROGRAMID,RELATIONSHIPTOHOH,DECISIONREASON) values (9895,95,268,30366624,'385492804','2002-12-17','101569748',236,'No','MAGI','PROC',to_date('01-JAN-22','DD-MON-RR'),null,'A','2022-01-01',null,'Kardeem','D','Walker','401 Lilac St',null,'Hattiesburg','MS','39401','5501','Lamar','6012645386','6012611244',122710,'Ontario.Lewis@medicaid.ms.gov','Ontario','Lewis',null,'426936260',null,'false','CELL','6015960680',null,null,null,null,null,null,null,null,null,null,null,'073',null,null,'6971 Lincoln Rd Ext',null,'Hattiesburg','MS','39402','3227',null,null,null,null,null,null,null,null,'Approved','Male','Single',167286962,61,'Grandchild',null);
CREATE TABLE "C"
( "HOUSEHOLD_ID" NUMBER(10,0) NOT NULL ENABLE,
"PERSON_ID" NUMBER(10,0),
"CONSUMER_ID" NUMBER,
"RESOURCECATEGORYTYPE" VARCHAR2(80 BYTE),
"RESOURCEDETAILCATEGORYTYPE" VARCHAR2(80 BYTE),
"RESOURCEAMOUNT" NUMBER(10,2),
"RESOURCEOWNERSHIPTYPE" VARCHAR2(1 BYTE),
"INSTITUTION_NAME" VARCHAR2(80 BYTE),
"KEYTYPE" CHAR(14 BYTE),
"KEYVALUE" VARCHAR2(30 BYTE)
) ;
Insert into C (HOUSEHOLD_ID,PERSON_ID,CONSUMER_ID,RESOURCECATEGORYTYPE,RESOURCEDETAILCATEGORYTYPE,RESOURCEAMOUNT,RESOURCEOWNERSHIPTYPE,INSTITUTION_NAME,KEYTYPE,KEYVALUE) values (95,236,29174906,'Liquid','Checking Account',52,'N','BancorpSouth Bank',null,null);
Insert into C (HOUSEHOLD_ID,PERSON_ID,CONSUMER_ID,RESOURCECATEGORYTYPE,RESOURCEDETAILCATEGORYTYPE,RESOURCEAMOUNT,RESOURCEOWNERSHIPTYPE,INSTITUTION_NAME,KEYTYPE,KEYVALUE) values (95,236,29174906,'Liquid','Checking Account',906.85,'N','Regions Bank','Account Number','266261937');
Insert into C (HOUSEHOLD_ID,PERSON_ID,CONSUMER_ID,RESOURCECATEGORYTYPE,RESOURCEDETAILCATEGORYTYPE,RESOURCEAMOUNT,RESOURCEOWNERSHIPTYPE,INSTITUTION_NAME,KEYTYPE,KEYVALUE) values (95,268,30366624,null,null,null,null,null,null,null);
Insert into C (HOUSEHOLD_ID,PERSON_ID,CONSUMER_ID,RESOURCECATEGORYTYPE,RESOURCEDETAILCATEGORYTYPE,RESOURCEAMOUNT,RESOURCEOWNERSHIPTYPE,INSTITUTION_NAME,KEYTYPE,KEYVALUE) values (95,281,28451269,null,null,null,null,null,null,null);
Select XMLElement("CaseTypeRow",
XMLElement( "ProgramType", 'Medicaid'),
XMLElement( "ActionType", 'InitialLoad'),
XMLElement( "KeyInfo",
XMLElement("KeyTypeRow",
XMLElement("KeyType", 'CaseID'),
XMLElement("KeyValue", Casenumber)),
XMLElement("KeyTypeRow",
XMLElement("KeyType", 'HouseholdID'),
XMLElement("KeyValue", b.Household_ID))
),
XMLElement( "OfficeInfo",
XMLElement("AddressRow",
XMLElement("AddressLine1", REGIONALOFFICESTREETADDRESS1),
XMLElement("City", REGIONALOFFICECITY),
XMLElement("State", REGIONALOFFICESTATE),
XMLElement("Zipcode", REGIONALOFFICEZIP)),
XMLElement("Phone", Regiionalofficephonenumber),
XMLElement("FaxNumber", Regiionalofficefaxnumber)),
XMLElement("CaseWorkerInfo",
XMLElement("CaseworkerID", CaseworkerID),
XMLElement("EmailID", CaseworkeremailID),
XMLElement("FirstName", CaseworkerFirstName),
XMLElement("LastName", CaseworkerLastName)),
XMLElement("PersonInfo",
XMLElement("PersonRow",
XMLElement("ProgramType", 'Medicaid'),
XMLElement( "KeyInfo",
XMLElement("KeyTypeRow",
XMLElement("KeyType", 'MedicaidID'),
XMLElement("KeyValue", Medicaidid)),
XMLElement("KeyTypeRow",
XMLElement("KeyType", 'PersonID'),
XMLElement("KeyValue", PersonID))),
XMLElement("PersonIdentityInfo",
XMLElement("NameInfo",
XMLElement("FirstName", First_Name),
XMLElement("MiddleName", Middle_Initial),
XMLElement("LastName", Last_Name)),
XMLElement("DateOfBirth", DOB),
XMLElement("SSN", SSN),
XMLElement("Gender", Genderdesc),
XMLElement("MaritalStatus", Maritalstatusdesc),
XMLElement("DeceasedDate", DeathDate),
XMLElement("USCitizenshipInd", USCitizenshipInd)),
XMLElement("ContactInfo",
XMLElement("AddressType",
XMLElement("AddressRow",
XMLElement("AddressCategoryType", 'Home'),
XMLElement("AddressLine1", Address_Line1),
XMLElement("AddressLine2", Address_Line2),
XMLElement("City", City),
XMLElement("State", State),
XMLElement("ZipCode", Zipcode_desc),
XMLElement("ZipCodeExt", Zipcode_ext),
XMLElement("County", County))),
XMLElement("PhoneType",
XMLElement("PhoneRow",
XMLElement("PhoneCategoryType", 'Home'),
XMLElement("Phone", homephonenumber),
XMLElement("PhoneExt", homephoneextension))),
XMLElement("EmailID", householdemailaddress)),
XMLElement("EligibilityInfo",
XMLElement("EligibilityDetailRow",
XMLElement("ProgramType", 'Medicaid'),
XMLElement("EligibilityBeginDate", ELIGIBILITY_STATUS_BEGIN_DATE),
XMLElement("CategoryOfEligibilityType", CATEGORYOFELIGIBILITYTYPE),
XMLElement("DecisionStatus", DECISIONSTATUSCODEDESC),
XMLElement("DecisionReason", homephonenumber))),
XMLElement("IncarcerationInfo",
XMLElement("IncarcerationRow",'')),
XMLElement("ResourceInfo",
(Select
XMLAGG(
XMLElement("ResourceRow",
XMLElement("ResourceCategory", ResourceCategoryType),
XMLElement("ResourceDetailCategoryType", ResourceDetailCategoryType),
XMLElement("ResourceAmount", ResourceAmount),
XMLElement("ResourceOwnershipType", ResourceOwnershipType),
XMLElement("InstitutionName", institution_name)))
from c tcr
where tcr.consumer_id = b.consumer_id
)
),
XMLElement("FamilyCompositionInfo",
XMLElement("RelationshipToHOH",''))))
)
as xml
from a , b
where a.household_id = b.HOUSEHOLD_ID;
-
Attachment: output.txt
(Size: 13.08KB, Downloaded 259 times)
|
|
|
|
| XML help needed [message #685758 is a reply to message #685757] |
Thu, 17 March 2022 21:14  |
|
rajiv.oradev
Messages: 15
Registered: March 2022
|
Junior Member |
|
|
Hi, I am new to XML and need 1 tag but due to multiple rows in tables I am getting multiple rows instead of 1 row with aggregated one inside tag.
Drop table a;
CREATE TABLE A
( household_id NUMBER(10,0),
person_id number(10,0)
) ;
Insert into a values (95,236);
Insert into a values (95,268);
Insert into a values (95,281);
CREATE TABLE B
( "TB4_ID_PK" NUMBER(10,0),
"HOUSEHOLD_ID" NUMBER(10,0) NOT NULL ENABLE,
"PERSONID" NUMBER(10,0) NOT NULL ENABLE,
"CONSUMER_ID" NUMBER(10,0) NOT NULL ENABLE,
"MEDICAIDID" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"DOB" VARCHAR2(10 BYTE),
"CASENUMBER" VARCHAR2(25 BYTE),
"HOHPERSONID" NUMBER(10,0),
"HOH_IND" VARCHAR2(3 BYTE),
"APPLYING_FOR" VARCHAR2(4 BYTE),
"CONSUMER_STATUS_CODE" VARCHAR2(4 BYTE) NOT NULL ENABLE,
"CONSUMER_STATUS_START_DATE" DATE NOT NULL ENABLE,
"CONSUMER_STATUS_END_DATE" DATE,
"DECISION_STATUS_CODE" VARCHAR2(4 BYTE),
"ELIGIBILITY_STATUS_BEGIN_DATE" VARCHAR2(10 BYTE),
"ELIGIBILITY_STATUS_END_DATE" VARCHAR2(10 BYTE),
"FIRST_NAME" VARCHAR2(25 BYTE),
"MIDDLE_INITIAL" VARCHAR2(20 BYTE),
"LAST_NAME" VARCHAR2(35 BYTE) NOT NULL ENABLE,
"ADDRESS_LINE1" VARCHAR2(35 BYTE),
"ADDRESS_LINE2" VARCHAR2(35 BYTE),
"CITY" VARCHAR2(40 BYTE),
"STATE" VARCHAR2(2 BYTE),
"ZIPCODE_DESC" VARCHAR2(10 BYTE),
"ZIPCODE_EXT" VARCHAR2(5 BYTE),
"COUNTY" VARCHAR2(40 BYTE),
"REGIIONALOFFICEPHONENUMBER" VARCHAR2(27 BYTE),
"REGIIONALOFFICEFAXNUMBER" VARCHAR2(21 BYTE),
"CASEWORKERID" NUMBER(10,0),
"CASEWORKEREMAILID" VARCHAR2(100 BYTE),
"CASEWORKERFIRSTNAME" VARCHAR2(50 BYTE),
"CASEWORKERLASTNAME" VARCHAR2(50 BYTE),
"SUFFIXNAME" VARCHAR2(10 BYTE),
"SSN" VARCHAR2(25 BYTE),
"DEATHDATE" VARCHAR2(10 BYTE),
"USCITIZENSHIPIND" VARCHAR2(5 BYTE),
"CELLPHONE" VARCHAR2(4 BYTE),
"CELLPHONENUMBER" VARCHAR2(10 BYTE),
"CELLPHONEEXTENSION" VARCHAR2(5 BYTE),
"HOMEPHONE" VARCHAR2(4 BYTE),
"HOMEPHONENUMBER" VARCHAR2(10 BYTE),
"HOMEPHONEEXTENSION" VARCHAR2(5 BYTE),
"WORKPHONE" VARCHAR2(4 BYTE),
"WORKPHONENUMBER" VARCHAR2(10 BYTE),
"WORKPHONEEXTENSION" VARCHAR2(5 BYTE),
"OTHERPHONE" VARCHAR2(4 BYTE),
"OTHERPHONENUMBER" VARCHAR2(10 BYTE),
"OTHERPHONEEXTENSION" VARCHAR2(5 BYTE),
"HOUSEHOLDEMAILADDRESS" VARCHAR2(256 BYTE),
"CATEGORYOFELIGIBILITYTYPE" VARCHAR2(4 BYTE),
"INCARCERATIONDATE" DATE,
"INCARCERATIONRELEASEDATE" DATE,
"REGIONALOFFICESTREETADDRESS1" VARCHAR2(35 BYTE),
"REGIONALOFFICESTREETADDRESS2" VARCHAR2(35 BYTE),
"REGIONALOFFICECITY" VARCHAR2(40 BYTE),
"REGIONALOFFICESTATE" VARCHAR2(2 BYTE),
"REGIONALOFFICEZIP" VARCHAR2(10 BYTE),
"REGIONALOFFICEZIPEXT" VARCHAR2(5 BYTE),
"REGIONALOFFICECOUNTY" VARCHAR2(40 BYTE),
"MAILING_ADDRESS_LINE1" VARCHAR2(35 BYTE),
"MAILING_ADDRESS_LINE2" VARCHAR2(35 BYTE),
"MAILING_CITY" VARCHAR2(40 BYTE),
"MAILING_STATE" VARCHAR2(2 BYTE),
"MAILING_ZIPCODE" VARCHAR2(10 BYTE),
"MAILING_ZIPCODE_EXT" VARCHAR2(5 BYTE),
"MAILING_COUNTY" VARCHAR2(40 BYTE),
"DECISIONSTATUSCODEDESC" VARCHAR2(80 BYTE),
"GENDERDESC" VARCHAR2(80 BYTE),
"MARITALSTATUSDESC" VARCHAR2(80 BYTE),
"ELIGIBILITYDECISIONID" NUMBER(10,0),
"ELIGIBILITYDECISIONPROGRAMID" NUMBER(10,0),
"RELATIONSHIPTOHOH" VARCHAR2(80 BYTE),
"DECISIONREASON" VARCHAR2(80 BYTE)
) ;
Insert into B (TB4_ID_PK,HOUSEHOLD_ID,PERSONID,CONSUMER_ID,MEDICAIDID,DOB,CASENUMBER,HOHPERSONID,HOH_IND,APPLYING_FOR,CONSUMER_STATUS_CODE,CONSUMER_STATUS_START_DATE,CONSUMER_STATUS_END_DATE,DECISION_STATUS_CODE,ELIGIBILITY_STATUS_BEGIN_DATE,ELIGIBILITY_STATUS_END_DATE,FIRST_NAME,MIDDLE_INITIAL,LAST_NAME,ADDRESS_LINE1,ADDRESS_LINE2,CITY,STATE,ZIPCODE_DESC,ZIPCODE_EXT,COUNTY,REGIIONALOFFICEPHONENUMBER,REGIIONALOFFICEFAXNUMBER,CASEWORKERID,CASEWORKEREMAILID,CASEWORKERFIRSTNAME,CASEWORKERLASTNAME,SUFFIXNAME,SSN,DEATHDATE,USCITIZENSHIPIND,CELLPHONE,CELLPHONENUMBER,CELLPHONEEXTENSION,HOMEPHONE,HOMEPHONENUMBER,HOMEPHONEEXTENSION,WORKPHONE,WORKPHONENUMBER,WORKPHONEEXTENSION,OTHERPHONE,OTHERPHONENUMBER,OTHERPHONEEXTENSION,HOUSEHOLDEMAILADDRESS,CATEGORYOFELIGIBILITYTYPE,INCARCERATIONDATE,INCARCERATIONRELEASEDATE,REGIONALOFFICESTREETADDRESS1,REGIONALOFFICESTREETADDRESS2,REGIONALOFFICECITY,REGIONALOFFICESTATE,REGIONALOFFICEZIP,REGIONALOFFICEZIPEXT,REGIONALOFFICECOUNTY,MAILING_ADDRESS_LINE1,MAILING_ADDRESS_LINE2,MAILING_CITY,MAILING_STATE,MAILING_ZIPCODE,MAILING_ZIPCODE_EXT,MAILING_COUNTY,DECISIONSTATUSCODEDESC,GENDERDESC,MARITALSTATUSDESC,ELIGIBILITYDECISIONID,ELIGIBILITYDECISIONPROGRAMID,RELATIONSHIPTOHOH,DECISIONREASON) values (7187,95,236,29174906,'301869568','1947-09-15','101569748',236,'Yes','ABD','PROC',to_date('01-NOV-21','DD-MON-RR'),null,'A','2021-11-01',null,'Annie','B','Jaynes','401 Lilac St',null,'Hattiesburg','MS','39401','5501','Lamar','6012645386','6012611244',122710,'Ontario.Lewis@medicaid.ms.gov','Ontario','Lewis',null,'428985760',null,'false','CELL','6015960680',null,null,null,null,null,null,null,null,null,null,null,'051',null,null,'6971 Lincoln Rd Ext',null,'Hattiesburg','MS','39402','3227',null,null,null,null,null,null,null,null,'Approved','Female','Widowed',165839036,22,'Self',null);
Insert into B (TB4_ID_PK,HOUSEHOLD_ID,PERSONID,CONSUMER_ID,MEDICAIDID,DOB,CASENUMBER,HOHPERSONID,HOH_IND,APPLYING_FOR,CONSUMER_STATUS_CODE,CONSUMER_STATUS_START_DATE,CONSUMER_STATUS_END_DATE,DECISION_STATUS_CODE,ELIGIBILITY_STATUS_BEGIN_DATE,ELIGIBILITY_STATUS_END_DATE,FIRST_NAME,MIDDLE_INITIAL,LAST_NAME,ADDRESS_LINE1,ADDRESS_LINE2,CITY,STATE,ZIPCODE_DESC,ZIPCODE_EXT,COUNTY,REGIIONALOFFICEPHONENUMBER,REGIIONALOFFICEFAXNUMBER,CASEWORKERID,CASEWORKEREMAILID,CASEWORKERFIRSTNAME,CASEWORKERLASTNAME,SUFFIXNAME,SSN,DEATHDATE,USCITIZENSHIPIND,CELLPHONE,CELLPHONENUMBER,CELLPHONEEXTENSION,HOMEPHONE,HOMEPHONENUMBER,HOMEPHONEEXTENSION,WORKPHONE,WORKPHONENUMBER,WORKPHONEEXTENSION,OTHERPHONE,OTHERPHONENUMBER,OTHERPHONEEXTENSION,HOUSEHOLDEMAILADDRESS,CATEGORYOFELIGIBILITYTYPE,INCARCERATIONDATE,INCARCERATIONRELEASEDATE,REGIONALOFFICESTREETADDRESS1,REGIONALOFFICESTREETADDRESS2,REGIONALOFFICECITY,REGIONALOFFICESTATE,REGIONALOFFICEZIP,REGIONALOFFICEZIPEXT,REGIONALOFFICECOUNTY,MAILING_ADDRESS_LINE1,MAILING_ADDRESS_LINE2,MAILING_CITY,MAILING_STATE,MAILING_ZIPCODE,MAILING_ZIPCODE_EXT,MAILING_COUNTY,DECISIONSTATUSCODEDESC,GENDERDESC,MARITALSTATUSDESC,ELIGIBILITYDECISIONID,ELIGIBILITYDECISIONPROGRAMID,RELATIONSHIPTOHOH,DECISIONREASON) values (34005,95,281,28451269,'385792441','2003-10-10','101569748',236,'No','MAGI','PROC',to_date('01-JUL-21','DD-MON-RR'),null,'A','2021-07-01',null,'Michael','J','Hubbard','401 Lilac St',null,'Hattiesburg','MS','39401','5501','Lamar','6012645386','6012611244',122710,'Ontario.Lewis@medicaid.ms.gov','Ontario','Lewis',null,'425952940',null,'false','CELL','6015960680',null,null,null,null,null,null,null,null,null,null,null,'073',null,null,'6971 Lincoln Rd Ext',null,'Hattiesburg','MS','39402','3227',null,null,null,null,null,null,null,null,'Approved','Male','Single',164825122,61,'Grandchild',null);
Insert into B (TB4_ID_PK,HOUSEHOLD_ID,PERSONID,CONSUMER_ID,MEDICAIDID,DOB,CASENUMBER,HOHPERSONID,HOH_IND,APPLYING_FOR,CONSUMER_STATUS_CODE,CONSUMER_STATUS_START_DATE,CONSUMER_STATUS_END_DATE,DECISION_STATUS_CODE,ELIGIBILITY_STATUS_BEGIN_DATE,ELIGIBILITY_STATUS_END_DATE,FIRST_NAME,MIDDLE_INITIAL,LAST_NAME,ADDRESS_LINE1,ADDRESS_LINE2,CITY,STATE,ZIPCODE_DESC,ZIPCODE_EXT,COUNTY,REGIIONALOFFICEPHONENUMBER,REGIIONALOFFICEFAXNUMBER,CASEWORKERID,CASEWORKEREMAILID,CASEWORKERFIRSTNAME,CASEWORKERLASTNAME,SUFFIXNAME,SSN,DEATHDATE,USCITIZENSHIPIND,CELLPHONE,CELLPHONENUMBER,CELLPHONEEXTENSION,HOMEPHONE,HOMEPHONENUMBER,HOMEPHONEEXTENSION,WORKPHONE,WORKPHONENUMBER,WORKPHONEEXTENSION,OTHERPHONE,OTHERPHONENUMBER,OTHERPHONEEXTENSION,HOUSEHOLDEMAILADDRESS,CATEGORYOFELIGIBILITYTYPE,INCARCERATIONDATE,INCARCERATIONRELEASEDATE,REGIONALOFFICESTREETADDRESS1,REGIONALOFFICESTREETADDRESS2,REGIONALOFFICECITY,REGIONALOFFICESTATE,REGIONALOFFICEZIP,REGIONALOFFICEZIPEXT,REGIONALOFFICECOUNTY,MAILING_ADDRESS_LINE1,MAILING_ADDRESS_LINE2,MAILING_CITY,MAILING_STATE,MAILING_ZIPCODE,MAILING_ZIPCODE_EXT,MAILING_COUNTY,DECISIONSTATUSCODEDESC,GENDERDESC,MARITALSTATUSDESC,ELIGIBILITYDECISIONID,ELIGIBILITYDECISIONPROGRAMID,RELATIONSHIPTOHOH,DECISIONREASON) values (9895,95,268,30366624,'385492804','2002-12-17','101569748',236,'No','MAGI','PROC',to_date('01-JAN-22','DD-MON-RR'),null,'A','2022-01-01',null,'Kardeem','D','Walker','401 Lilac St',null,'Hattiesburg','MS','39401','5501','Lamar','6012645386','6012611244',122710,'Ontario.Lewis@medicaid.ms.gov','Ontario','Lewis',null,'426936260',null,'false','CELL','6015960680',null,null,null,null,null,null,null,null,null,null,null,'073',null,null,'6971 Lincoln Rd Ext',null,'Hattiesburg','MS','39402','3227',null,null,null,null,null,null,null,null,'Approved','Male','Single',167286962,61,'Grandchild',null);
CREATE TABLE "C"
( "HOUSEHOLD_ID" NUMBER(10,0) NOT NULL ENABLE,
"PERSON_ID" NUMBER(10,0),
"CONSUMER_ID" NUMBER,
"RESOURCECATEGORYTYPE" VARCHAR2(80 BYTE),
"RESOURCEDETAILCATEGORYTYPE" VARCHAR2(80 BYTE),
"RESOURCEAMOUNT" NUMBER(10,2),
"RESOURCEOWNERSHIPTYPE" VARCHAR2(1 BYTE),
"INSTITUTION_NAME" VARCHAR2(80 BYTE),
"KEYTYPE" CHAR(14 BYTE),
"KEYVALUE" VARCHAR2(30 BYTE)
) ;
Insert into C (HOUSEHOLD_ID,PERSON_ID,CONSUMER_ID,RESOURCECATEGORYTYPE,RESOURCEDETAILCATEGORYTYPE,RESOURCEAMOUNT,RESOURCEOWNERSHIPTYPE,INSTITUTION_NAME,KEYTYPE,KEYVALUE) values (95,236,29174906,'Liquid','Checking Account',52,'N','BancorpSouth Bank',null,null);
Insert into C (HOUSEHOLD_ID,PERSON_ID,CONSUMER_ID,RESOURCECATEGORYTYPE,RESOURCEDETAILCATEGORYTYPE,RESOURCEAMOUNT,RESOURCEOWNERSHIPTYPE,INSTITUTION_NAME,KEYTYPE,KEYVALUE) values (95,236,29174906,'Liquid','Checking Account',906.85,'N','Regions Bank','Account Number','266261937');
Insert into C (HOUSEHOLD_ID,PERSON_ID,CONSUMER_ID,RESOURCECATEGORYTYPE,RESOURCEDETAILCATEGORYTYPE,RESOURCEAMOUNT,RESOURCEOWNERSHIPTYPE,INSTITUTION_NAME,KEYTYPE,KEYVALUE) values (95,268,30366624,null,null,null,null,null,null,null);
Insert into C (HOUSEHOLD_ID,PERSON_ID,CONSUMER_ID,RESOURCECATEGORYTYPE,RESOURCEDETAILCATEGORYTYPE,RESOURCEAMOUNT,RESOURCEOWNERSHIPTYPE,INSTITUTION_NAME,KEYTYPE,KEYVALUE) values (95,281,28451269,null,null,null,null,null,null,null);
This is the output that needs to be generated....new to xml i am not sure how to wrie but I am getting duplicates. Need 1 xml tag like this
<eligibilityChangeRequestType>
<ArrayOfCaseTypeRow>
<CaseTypeRow>
<ProgramType>Medicaid</ProgramType>
<ActionType>InitialLoad</ActionType>
<KeyInfo>
<KeyTypeRow>
<KeyType>CaseID</KeyType>
<KeyValue>101569748</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>HouseholdID</KeyType>
<KeyValue>95</KeyValue>
</KeyTypeRow>
</KeyInfo>
<OfficeInfo>
<AddressRow>
<AddressLine1>6971 Lincoln Rd Ext</AddressLine1>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39402</ZipCode>
<ZipCodeExt>3227</ZipCodeExt>
</AddressRow>
<Phone>6012645386</Phone>
<FaxNumber>6012611244</FaxNumber>
</OfficeInfo>
<CaseWorkerInfo>
<CaseworkerID>122710</CaseworkerID>
<EmailID>Ontario.Lewis@medicaid.ms.gov</EmailID>
<FirstName>Ontario</FirstName>
<LastName>Lewis</LastName>
</CaseWorkerInfo>
<PersonInfo>
<PersonRow>
<ProgramType>Medicaid</ProgramType>
<KeyInfo>
<KeyTypeRow>
<KeyType>MedicaidID</KeyType>
<KeyValue>301869568</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>PersonID</KeyType>
<KeyValue>236</KeyValue>
</KeyTypeRow>
</KeyInfo>
<PersonIdentityInfo>
<NameInfo>
<FirstName>Annie</FirstName>
<MiddleName>B</MiddleName>
<LastName>Jaynes</LastName>
</NameInfo>
<DateOfBirth>1947-09-15</DateOfBirth>
<SSN>428985760</SSN>
<Gender>Female</Gender>
<MaritalStatus>Widowed</MaritalStatus>
<USCitizenshipInd>false</USCitizenshipInd>
</PersonIdentityInfo>
<ContactInfo>
<AddressType>
<AddressRow>
<AddressCategoryType>Home</AddressCategoryType>
<AddressLine1>401 Lilac St</AddressLine1>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39401</ZipCode>
<ZipCodeExt>5501</ZipCodeExt>
<County>Lamar</County>
</AddressRow>
</AddressType>
<PhoneType>
<PhoneRow>
<PhoneCategoryType>CELL</PhoneCategoryType>
<Phone>6015960680</Phone>
<PhoneExt></PhoneExt>
</PhoneRow>
</PhoneType>
<EmailID></EmailID>
</ContactInfo>
<EligibilityInfo>
<EligibilityDetailtRow>
<ProgramType>Medicaid</ProgramType>
<EligibilityBeginDate>2021-11-01-05:00</EligibilityBeginDate>
<CategoryOfEligibilityType>Specified Low Income Medicare Beneficiary</CategoryOfEligibilityType>
<DecisionStatus>Approved</DecisionStatus>
</EligibilityDetailtRow>
</EligibilityInfo>
<IncarcerationInfo>
<IncarcerationRow/>
</IncarcerationInfo>
<ResourceInInfo>
<ResourceRow>
<ResourceCategoryType>Liquid</ResourceCategoryType>
<ResourceDetailCategoryType>Checking Account</ResourceDetailCategoryType>
<ResourceAmount>52.0</ResourceAmount>
<ResourceOwnershipType>Sole</ResourceOwnershipType>
<InstitutionName>BancorpSouth Bank</InstitutionName>
</ResourceRow>
<ResourceRow>
<ResourceCategoryType>Liquid</ResourceCategoryType>
<ResourceDetailCategoryType>Checking Account</ResourceDetailCategoryType>
<ResourceAmount>906.85</ResourceAmount>
<ResourceOwnershipType>Sole</ResourceOwnershipType>
<InstitutionName>Regions Bank</InstitutionName>
</ResourceRow>
</ResourceInInfo>
<FamilyCompositionInfo>
<RelationshipToHOH>Self</RelationshipToHOH>
</FamilyCompositionInfo>
<ExpenseInfo/>
</PersonRow>
<PersonRow>
<ProgramType>Medicaid</ProgramType>
<KeyInfo>
<KeyTypeRow>
<KeyType>MedicaidID</KeyType>
<KeyValue>385492804</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>PersonID</KeyType>
<KeyValue>268</KeyValue>
</KeyTypeRow>
</KeyInfo>
<PersonIdentityInfo>
<NameInfo>
<FirstName>Kardeem</FirstName>
<MiddleName>D</MiddleName>
<LastName>Walker</LastName>
</NameInfo>
<DateOfBirth>2002-12-17</DateOfBirth>
<SSN>426936260</SSN>
<Gender>Male</Gender>
<MaritalStatus>Single</MaritalStatus>
<USCitizenshipInd>false</USCitizenshipInd>
</PersonIdentityInfo>
<ContactInfo>
<AddressType>
<AddressRow>
<AddressCategoryType>Home</AddressCategoryType>
<AddressLine1>401 Lilac St</AddressLine1>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39401</ZipCode>
<ZipCodeExt>5501</ZipCodeExt>
<County>Lamar</County>
</AddressRow>
</AddressType>
<PhoneType>
<PhoneRow>
<PhoneCategoryType>CELL</PhoneCategoryType>
<Phone>6015960680</Phone>
<PhoneExt></PhoneExt>
</PhoneRow>
</PhoneType>
<EmailID></EmailID>
</ContactInfo>
<EligibilityInfo>
<EligibilityDetailtRow>
<ProgramType>Medicaid</ProgramType>
<EligibilityBeginDate>2022-01-01-06:00</EligibilityBeginDate>
<CategoryOfEligibilityType>Child</CategoryOfEligibilityType>
<DecisionStatus>Approved</DecisionStatus>
</EligibilityDetailtRow>
</EligibilityInfo>
<IncarcerationInfo>
<IncarcerationRow/>
</IncarcerationInfo>
<IncomeInfo>
<IncomeRow>
<IncomeSource>Verified - Electronic Data Source</IncomeSource>
<IncomeCategoryType>Social Security Benefit (RSDI)</IncomeCategoryType>
<IncomeAmount>940.0</IncomeAmount>
<IncomeFrequency>Monthly</IncomeFrequency>
</IncomeRow>
</IncomeInfo>
<FamilyCompositionInfo>
<RelationshipToHOH>Grandchild</RelationshipToHOH>
</FamilyCompositionInfo>
<ExpenseInfo/>
</PersonRow>
<PersonRow>
<ProgramType>Medicaid</ProgramType>
<KeyInfo>
<KeyTypeRow>
<KeyType>MedicaidID</KeyType>
<KeyValue>385792441</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>PersonID</KeyType>
<KeyValue>281</KeyValue>
</KeyTypeRow>
</KeyInfo>
<PersonIdentityInfo>
<NameInfo>
<FirstName>Michael</FirstName>
<MiddleName>J</MiddleName>
<LastName>Hubbard</LastName>
</NameInfo>
<DateOfBirth>2003-10-10</DateOfBirth>
<SSN>425952940</SSN>
<Gender>Male</Gender>
<MaritalStatus>Single</MaritalStatus>
<USCitizenshipInd>false</USCitizenshipInd>
</PersonIdentityInfo>
<ContactInfo>
<AddressType>
<AddressRow>
<AddressCategoryType>Home</AddressCategoryType>
<AddressLine1>401 Lilac St</AddressLine1>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39401</ZipCode>
<ZipCodeExt>5501</ZipCodeExt>
<County>Lamar</County>
</AddressRow>
</AddressType>
<PhoneType>
<PhoneRow>
<PhoneCategoryType>CELL</PhoneCategoryType>
<Phone>6015960680</Phone>
<PhoneExt></PhoneExt>
</PhoneRow>
</PhoneType>
<EmailID></EmailID>
</ContactInfo>
<EligibilityInfo>
<EligibilityDetailtRow>
<ProgramType>Medicaid</ProgramType>
<EligibilityBeginDate>2021-07-01-05:00</EligibilityBeginDate>
<CategoryOfEligibilityType>Child</CategoryOfEligibilityType>
<DecisionStatus>Approved</DecisionStatus>
</EligibilityDetailtRow>
</EligibilityInfo>
<IncarcerationInfo>
<IncarcerationRow/>
</IncarcerationInfo>
<IncomeInfo>
<IncomeRow>
<IncomeSource>Verified - Electronic Data Source</IncomeSource>
<IncomeCategoryType>Social Security Benefit (RSDI)</IncomeCategoryType>
<IncomeAmount>555.0</IncomeAmount>
<IncomeFrequency>Monthly</IncomeFrequency>
</IncomeRow>
</IncomeInfo>
<FamilyCompositionInfo>
<RelationshipToHOH>Grandchild</RelationshipToHOH>
</FamilyCompositionInfo>
<ExpenseInfo/>
</PersonRow>
</PersonInfo>
</CaseTypeRow>
</ArrayOfCaseTypeRow>
</eligibilityChangeRequestType>
This is the code I wrote
Select XMLElement("CaseTypeRow",
XMLElement( "ProgramType", 'Medicaid'),
XMLElement( "ActionType", 'InitialLoad'),
XMLElement( "KeyInfo",
XMLElement("KeyTypeRow",
XMLElement("KeyType", 'CaseID'),
XMLElement("KeyValue", Casenumber)),
XMLElement("KeyTypeRow",
XMLElement("KeyType", 'HouseholdID'),
XMLElement("KeyValue", b.Household_ID))
),
XMLElement( "OfficeInfo",
XMLElement("AddressRow",
XMLElement("AddressLine1", REGIONALOFFICESTREETADDRESS1),
XMLElement("City", REGIONALOFFICECITY),
XMLElement("State", REGIONALOFFICESTATE),
XMLElement("Zipcode", REGIONALOFFICEZIP)),
XMLElement("Phone", Regiionalofficephonenumber),
XMLElement("FaxNumber", Regiionalofficefaxnumber)),
XMLElement("CaseWorkerInfo",
XMLElement("CaseworkerID", CaseworkerID),
XMLElement("EmailID", CaseworkeremailID),
XMLElement("FirstName", CaseworkerFirstName),
XMLElement("LastName", CaseworkerLastName)),
XMLElement("PersonInfo",
XMLElement("PersonRow",
XMLElement("ProgramType", 'Medicaid'),
XMLElement( "KeyInfo",
XMLElement("KeyTypeRow",
XMLElement("KeyType", 'MedicaidID'),
XMLElement("KeyValue", Medicaidid)),
XMLElement("KeyTypeRow",
XMLElement("KeyType", 'PersonID'),
XMLElement("KeyValue", PersonID))),
XMLElement("PersonIdentityInfo",
XMLElement("NameInfo",
XMLElement("FirstName", First_Name),
XMLElement("MiddleName", Middle_Initial),
XMLElement("LastName", Last_Name)),
XMLElement("DateOfBirth", DOB),
XMLElement("SSN", SSN),
XMLElement("Gender", Genderdesc),
XMLElement("MaritalStatus", Maritalstatusdesc),
XMLElement("DeceasedDate", DeathDate),
XMLElement("USCitizenshipInd", USCitizenshipInd)),
XMLElement("ContactInfo",
XMLElement("AddressType",
XMLElement("AddressRow",
XMLElement("AddressCategoryType", 'Home'),
XMLElement("AddressLine1", Address_Line1),
XMLElement("AddressLine2", Address_Line2),
XMLElement("City", City),
XMLElement("State", State),
XMLElement("ZipCode", Zipcode_desc),
XMLElement("ZipCodeExt", Zipcode_ext),
XMLElement("County", County))),
XMLElement("PhoneType",
XMLElement("PhoneRow",
XMLElement("PhoneCategoryType", 'Home'),
XMLElement("Phone", homephonenumber),
XMLElement("PhoneExt", homephoneextension))),
XMLElement("EmailID", householdemailaddress)),
XMLElement("EligibilityInfo",
XMLElement("EligibilityDetailRow",
XMLElement("ProgramType", 'Medicaid'),
XMLElement("EligibilityBeginDate", ELIGIBILITY_STATUS_BEGIN_DATE),
XMLElement("CategoryOfEligibilityType", CATEGORYOFELIGIBILITYTYPE),
XMLElement("DecisionStatus", DECISIONSTATUSCODEDESC),
XMLElement("DecisionReason", homephonenumber))),
XMLElement("IncarcerationInfo",
XMLElement("IncarcerationRow",'')),
XMLElement("ResourceInfo",
(Select
XMLAGG(
XMLElement("ResourceRow",
XMLElement("ResourceCategory", ResourceCategoryType),
XMLElement("ResourceDetailCategoryType", ResourceDetailCategoryType),
XMLElement("ResourceAmount", ResourceAmount),
XMLElement("ResourceOwnershipType", ResourceOwnershipType),
XMLElement("InstitutionName", institution_name)))
from c tcr
where tcr.consumer_id = b.consumer_id
)
),
XMLElement("FamilyCompositionInfo",
XMLElement("RelationshipToHOH",''))))
)
as xml
from a , b
where a.household_id = b.HOUSEHOLD_ID;
|
|
|
|
| Re: XML help needed [message #685760 is a reply to message #685758] |
Fri, 18 March 2022 06:12 |
Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
select XMLSerialize(
DOCUMENT
XMLElement(
"eligibilityChangeRequestType",
xmlattributes('http://softheon.com/2021/02/EligibilityChanges' as "xmlns"),
XMLElement("ArrayOfCaseTypeRow",
XMLAgg(
XMLElement("CaseTypeRow",
XMLElement("ProgramType",'Medicaid'),
XMLElement("ActionType",'InitialLoad'),
XMLElement("KeyInfo",
XMLElement("KeyTypeRow",
XMLElement("KeyType",'CaseID'),
XMLElement("KeyValue",Casenumber)
),
XMLElement("KeyTypeRow",
XMLElement("KeyType",'HouseholdID'),
XMLElement("KeyValue",b.Household_ID)
)
),
XMLElement("OfficeInfo",
XMLElement("AddressRow",
XMLElement("AddressLine1",REGIONALOFFICESTREETADDRESS1),
XMLElement("City",REGIONALOFFICECITY),
XMLElement("State",REGIONALOFFICESTATE),
XMLElement("Zipcode",REGIONALOFFICEZIP)
),
XMLElement("Phone",Regiionalofficephonenumber),
XMLElement("FaxNumber",Regiionalofficefaxnumber)
),
XMLElement("CaseWorkerInfo",
XMLElement("CaseworkerID",CaseworkerID),
XMLElement("EmailID",CaseworkeremailID),
XMLElement("FirstName",CaseworkerFirstName),
XMLElement("LastName",CaseworkerLastName)
),
XMLElement("PersonInfo",
XMLElement("PersonRow",
XMLElement("ProgramType",'Medicaid'),
XMLElement("KeyInfo",
XMLElement("KeyTypeRow",
XMLElement("KeyType",'MedicaidID'),
XMLElement("KeyValue",Medicaidid)
),
XMLElement("KeyTypeRow",
XMLElement("KeyType",'PersonID'),
XMLElement("KeyValue",PersonID)
)
),
XMLElement("PersonIdentityInfo",
XMLElement("NameInfo",
XMLElement("FirstName",First_Name),
XMLElement("MiddleName",Middle_Initial),
XMLElement("LastName",Last_Name)
),
XMLElement("DateOfBirth",DOB),
XMLElement("SSN",SSN),
XMLElement("Gender",Genderdesc),
XMLElement("MaritalStatus",Maritalstatusdesc),
XMLElement("DeceasedDate",DeathDate),
XMLElement("USCitizenshipInd",USCitizenshipInd)
),
XMLElement("ContactInfo",
XMLElement("AddressType",
XMLElement("AddressRow",
XMLElement("AddressCategoryType",'Home'),
XMLElement("AddressLine1",Address_Line1),
XMLElement("AddressLine2",Address_Line2),
XMLElement("City",City),
XMLElement("State",State),
XMLElement("ZipCode",Zipcode_desc),
XMLElement("ZipCodeExt",Zipcode_ext),
XMLElement("County",County)
)
),
XMLElement("PhoneType",
XMLElement("PhoneRow",
XMLElement("PhoneCategoryType",'Home'),
XMLElement("Phone",homephonenumber),
XMLElement("PhoneExt",homephoneextension)
)
),
XMLElement("EmailID",householdemailaddress)
),
XMLElement("EligibilityInfo",
XMLElement("EligibilityDetailRow",
XMLElement("ProgramType",'Medicaid'),
XMLElement("EligibilityBeginDate",ELIGIBILITY_STATUS_BEGIN_DATE),
XMLElement("CategoryOfEligibilityType",CATEGORYOFELIGIBILITYTYPE),
XMLElement("DecisionStatus",DECISIONSTATUSCODEDESC),
XMLElement("DecisionReason",homephonenumber)
)
),
XMLElement("IncarcerationInfo",
XMLElement("IncarcerationRow",'')
),
XMLElement("ResourceInfo",
(select XMLAgg(
XMLElement("ResourceRow",
XMLElement("ResourceCategory",ResourceCategoryType),
XMLElement("ResourceDetailCategoryType",ResourceDetailCategoryType),
XMLElement("ResourceAmount",ResourceAmount),
XMLElement("ResourceOwnershipType",ResourceOwnershipType),
XMLElement("InstitutionName",institution_name)
)
)
from c tcr
where tcr.consumer_id = b.consumer_id
)
),
XMLElement("FamilyCompositionInfo",
XMLElement("RelationshipToHOH",'')
)
)
)
)
)
)
)
INDENT SIZE = 2
) xml
from a ,b
where a.household_id = b.HOUSEHOLD_ID
/
XML
------------------------------------------------------------------------------------------------------------------------------------
<eligibilityChangeRequestType xmlns="http://softheon.com/2021/02/EligibilityChanges">
<ArrayOfCaseTypeRow>
<CaseTypeRow>
<ProgramType>Medicaid</ProgramType>
<ActionType>InitialLoad</ActionType>
<KeyInfo>
<KeyTypeRow>
<KeyType>CaseID</KeyType>
<KeyValue>101569748</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>HouseholdID</KeyType>
<KeyValue>95</KeyValue>
</KeyTypeRow>
</KeyInfo>
<OfficeInfo>
<AddressRow>
<AddressLine1>6971 Lincoln Rd Ext</AddressLine1>
<City>Hattiesburg</City>
<State>MS</State>
<Zipcode>39402</Zipcode>
</AddressRow>
<Phone>6012645386</Phone>
<FaxNumber>6012611244</FaxNumber>
</OfficeInfo>
<CaseWorkerInfo>
<CaseworkerID>122710</CaseworkerID>
<EmailID>Ontario.Lewis@medicaid.ms.gov</EmailID>
<FirstName>Ontario</FirstName>
<LastName>Lewis</LastName>
</CaseWorkerInfo>
<PersonInfo>
<PersonRow>
<ProgramType>Medicaid</ProgramType>
<KeyInfo>
<KeyTypeRow>
<KeyType>MedicaidID</KeyType>
<KeyValue>301869568</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>PersonID</KeyType>
<KeyValue>236</KeyValue>
</KeyTypeRow>
</KeyInfo>
<PersonIdentityInfo>
<NameInfo>
<FirstName>Annie</FirstName>
<MiddleName>B</MiddleName>
<LastName>Jaynes</LastName>
</NameInfo>
<DateOfBirth>1947-09-15</DateOfBirth>
<SSN>428985760</SSN>
<Gender>Female</Gender>
<MaritalStatus>Widowed</MaritalStatus>
<DeceasedDate/>
<USCitizenshipInd>false</USCitizenshipInd>
</PersonIdentityInfo>
<ContactInfo>
<AddressType>
<AddressRow>
<AddressCategoryType>Home</AddressCategoryType>
<AddressLine1>401 Lilac St</AddressLine1>
<AddressLine2/>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39401</ZipCode>
<ZipCodeExt>5501</ZipCodeExt>
<County>Lamar</County>
</AddressRow>
</AddressType>
<PhoneType>
<PhoneRow>
<PhoneCategoryType>Home</PhoneCategoryType>
<Phone/>
<PhoneExt/>
</PhoneRow>
</PhoneType>
<EmailID/>
</ContactInfo>
<EligibilityInfo>
<EligibilityDetailRow>
<ProgramType>Medicaid</ProgramType>
<EligibilityBeginDate>2021-11-01</EligibilityBeginDate>
<CategoryOfEligibilityType>051</CategoryOfEligibilityType>
<DecisionStatus>Approved</DecisionStatus>
<DecisionReason/>
</EligibilityDetailRow>
</EligibilityInfo>
<IncarcerationInfo>
<IncarcerationRow/>
</IncarcerationInfo>
<ResourceInfo>
<ResourceRow>
<ResourceCategory>Liquid</ResourceCategory>
<ResourceDetailCategoryType>Checking Account</ResourceDetailCategoryType>
<ResourceAmount>52</ResourceAmount>
<ResourceOwnershipType>N</ResourceOwnershipType>
<InstitutionName>BancorpSouth Bank</InstitutionName>
</ResourceRow>
<ResourceRow>
<ResourceCategory>Liquid</ResourceCategory>
<ResourceDetailCategoryType>Checking Account</ResourceDetailCategoryType>
<ResourceAmount>906.85</ResourceAmount>
<ResourceOwnershipType>N</ResourceOwnershipType>
<InstitutionName>Regions Bank</InstitutionName>
</ResourceRow>
</ResourceInfo>
<FamilyCompositionInfo>
<RelationshipToHOH/>
</FamilyCompositionInfo>
</PersonRow>
</PersonInfo>
</CaseTypeRow>
<CaseTypeRow>
<ProgramType>Medicaid</ProgramType>
<ActionType>InitialLoad</ActionType>
<KeyInfo>
<KeyTypeRow>
<KeyType>CaseID</KeyType>
<KeyValue>101569748</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>HouseholdID</KeyType>
<KeyValue>95</KeyValue>
</KeyTypeRow>
</KeyInfo>
<OfficeInfo>
<AddressRow>
<AddressLine1>6971 Lincoln Rd Ext</AddressLine1>
<City>Hattiesburg</City>
<State>MS</State>
<Zipcode>39402</Zipcode>
</AddressRow>
<Phone>6012645386</Phone>
<FaxNumber>6012611244</FaxNumber>
</OfficeInfo>
<CaseWorkerInfo>
<CaseworkerID>122710</CaseworkerID>
<EmailID>Ontario.Lewis@medicaid.ms.gov</EmailID>
<FirstName>Ontario</FirstName>
<LastName>Lewis</LastName>
</CaseWorkerInfo>
<PersonInfo>
<PersonRow>
<ProgramType>Medicaid</ProgramType>
<KeyInfo>
<KeyTypeRow>
<KeyType>MedicaidID</KeyType>
<KeyValue>301869568</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>PersonID</KeyType>
<KeyValue>236</KeyValue>
</KeyTypeRow>
</KeyInfo>
<PersonIdentityInfo>
<NameInfo>
<FirstName>Annie</FirstName>
<MiddleName>B</MiddleName>
<LastName>Jaynes</LastName>
</NameInfo>
<DateOfBirth>1947-09-15</DateOfBirth>
<SSN>428985760</SSN>
<Gender>Female</Gender>
<MaritalStatus>Widowed</MaritalStatus>
<DeceasedDate/>
<USCitizenshipInd>false</USCitizenshipInd>
</PersonIdentityInfo>
<ContactInfo>
<AddressType>
<AddressRow>
<AddressCategoryType>Home</AddressCategoryType>
<AddressLine1>401 Lilac St</AddressLine1>
<AddressLine2/>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39401</ZipCode>
<ZipCodeExt>5501</ZipCodeExt>
<County>Lamar</County>
</AddressRow>
</AddressType>
<PhoneType>
<PhoneRow>
<PhoneCategoryType>Home</PhoneCategoryType>
<Phone/>
<PhoneExt/>
</PhoneRow>
</PhoneType>
<EmailID/>
</ContactInfo>
<EligibilityInfo>
<EligibilityDetailRow>
<ProgramType>Medicaid</ProgramType>
<EligibilityBeginDate>2021-11-01</EligibilityBeginDate>
<CategoryOfEligibilityType>051</CategoryOfEligibilityType>
<DecisionStatus>Approved</DecisionStatus>
<DecisionReason/>
</EligibilityDetailRow>
</EligibilityInfo>
<IncarcerationInfo>
<IncarcerationRow/>
</IncarcerationInfo>
<ResourceInfo>
<ResourceRow>
<ResourceCategory>Liquid</ResourceCategory>
<ResourceDetailCategoryType>Checking Account</ResourceDetailCategoryType>
<ResourceAmount>52</ResourceAmount>
<ResourceOwnershipType>N</ResourceOwnershipType>
<InstitutionName>BancorpSouth Bank</InstitutionName>
</ResourceRow>
<ResourceRow>
<ResourceCategory>Liquid</ResourceCategory>
<ResourceDetailCategoryType>Checking Account</ResourceDetailCategoryType>
<ResourceAmount>906.85</ResourceAmount>
<ResourceOwnershipType>N</ResourceOwnershipType>
<InstitutionName>Regions Bank</InstitutionName>
</ResourceRow>
</ResourceInfo>
<FamilyCompositionInfo>
<RelationshipToHOH/>
</FamilyCompositionInfo>
</PersonRow>
</PersonInfo>
</CaseTypeRow>
<CaseTypeRow>
<ProgramType>Medicaid</ProgramType>
<ActionType>InitialLoad</ActionType>
<KeyInfo>
<KeyTypeRow>
<KeyType>CaseID</KeyType>
<KeyValue>101569748</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>HouseholdID</KeyType>
<KeyValue>95</KeyValue>
</KeyTypeRow>
</KeyInfo>
<OfficeInfo>
<AddressRow>
<AddressLine1>6971 Lincoln Rd Ext</AddressLine1>
<City>Hattiesburg</City>
<State>MS</State>
<Zipcode>39402</Zipcode>
</AddressRow>
<Phone>6012645386</Phone>
<FaxNumber>6012611244</FaxNumber>
</OfficeInfo>
<CaseWorkerInfo>
<CaseworkerID>122710</CaseworkerID>
<EmailID>Ontario.Lewis@medicaid.ms.gov</EmailID>
<FirstName>Ontario</FirstName>
<LastName>Lewis</LastName>
</CaseWorkerInfo>
<PersonInfo>
<PersonRow>
<ProgramType>Medicaid</ProgramType>
<KeyInfo>
<KeyTypeRow>
<KeyType>MedicaidID</KeyType>
<KeyValue>301869568</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>PersonID</KeyType>
<KeyValue>236</KeyValue>
</KeyTypeRow>
</KeyInfo>
<PersonIdentityInfo>
<NameInfo>
<FirstName>Annie</FirstName>
<MiddleName>B</MiddleName>
<LastName>Jaynes</LastName>
</NameInfo>
<DateOfBirth>1947-09-15</DateOfBirth>
<SSN>428985760</SSN>
<Gender>Female</Gender>
<MaritalStatus>Widowed</MaritalStatus>
<DeceasedDate/>
<USCitizenshipInd>false</USCitizenshipInd>
</PersonIdentityInfo>
<ContactInfo>
<AddressType>
<AddressRow>
<AddressCategoryType>Home</AddressCategoryType>
<AddressLine1>401 Lilac St</AddressLine1>
<AddressLine2/>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39401</ZipCode>
<ZipCodeExt>5501</ZipCodeExt>
<County>Lamar</County>
</AddressRow>
</AddressType>
<PhoneType>
<PhoneRow>
<PhoneCategoryType>Home</PhoneCategoryType>
<Phone/>
<PhoneExt/>
</PhoneRow>
</PhoneType>
<EmailID/>
</ContactInfo>
<EligibilityInfo>
<EligibilityDetailRow>
<ProgramType>Medicaid</ProgramType>
<EligibilityBeginDate>2021-11-01</EligibilityBeginDate>
<CategoryOfEligibilityType>051</CategoryOfEligibilityType>
<DecisionStatus>Approved</DecisionStatus>
<DecisionReason/>
</EligibilityDetailRow>
</EligibilityInfo>
<IncarcerationInfo>
<IncarcerationRow/>
</IncarcerationInfo>
<ResourceInfo>
<ResourceRow>
<ResourceCategory>Liquid</ResourceCategory>
<ResourceDetailCategoryType>Checking Account</ResourceDetailCategoryType>
<ResourceAmount>52</ResourceAmount>
<ResourceOwnershipType>N</ResourceOwnershipType>
<InstitutionName>BancorpSouth Bank</InstitutionName>
</ResourceRow>
<ResourceRow>
<ResourceCategory>Liquid</ResourceCategory>
<ResourceDetailCategoryType>Checking Account</ResourceDetailCategoryType>
<ResourceAmount>906.85</ResourceAmount>
<ResourceOwnershipType>N</ResourceOwnershipType>
<InstitutionName>Regions Bank</InstitutionName>
</ResourceRow>
</ResourceInfo>
<FamilyCompositionInfo>
<RelationshipToHOH/>
</FamilyCompositionInfo>
</PersonRow>
</PersonInfo>
</CaseTypeRow>
<CaseTypeRow>
<ProgramType>Medicaid</ProgramType>
<ActionType>InitialLoad</ActionType>
<KeyInfo>
<KeyTypeRow>
<KeyType>CaseID</KeyType>
<KeyValue>101569748</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>HouseholdID</KeyType>
<KeyValue>95</KeyValue>
</KeyTypeRow>
</KeyInfo>
<OfficeInfo>
<AddressRow>
<AddressLine1>6971 Lincoln Rd Ext</AddressLine1>
<City>Hattiesburg</City>
<State>MS</State>
<Zipcode>39402</Zipcode>
</AddressRow>
<Phone>6012645386</Phone>
<FaxNumber>6012611244</FaxNumber>
</OfficeInfo>
<CaseWorkerInfo>
<CaseworkerID>122710</CaseworkerID>
<EmailID>Ontario.Lewis@medicaid.ms.gov</EmailID>
<FirstName>Ontario</FirstName>
<LastName>Lewis</LastName>
</CaseWorkerInfo>
<PersonInfo>
<PersonRow>
<ProgramType>Medicaid</ProgramType>
<KeyInfo>
<KeyTypeRow>
<KeyType>MedicaidID</KeyType>
<KeyValue>385792441</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>PersonID</KeyType>
<KeyValue>281</KeyValue>
</KeyTypeRow>
</KeyInfo>
<PersonIdentityInfo>
<NameInfo>
<FirstName>Michael</FirstName>
<MiddleName>J</MiddleName>
<LastName>Hubbard</LastName>
</NameInfo>
<DateOfBirth>2003-10-10</DateOfBirth>
<SSN>425952940</SSN>
<Gender>Male</Gender>
<MaritalStatus>Single</MaritalStatus>
<DeceasedDate/>
<USCitizenshipInd>false</USCitizenshipInd>
</PersonIdentityInfo>
<ContactInfo>
<AddressType>
<AddressRow>
<AddressCategoryType>Home</AddressCategoryType>
<AddressLine1>401 Lilac St</AddressLine1>
<AddressLine2/>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39401</ZipCode>
<ZipCodeExt>5501</ZipCodeExt>
<County>Lamar</County>
</AddressRow>
</AddressType>
<PhoneType>
<PhoneRow>
<PhoneCategoryType>Home</PhoneCategoryType>
<Phone/>
<PhoneExt/>
</PhoneRow>
</PhoneType>
<EmailID/>
</ContactInfo>
<EligibilityInfo>
<EligibilityDetailRow>
<ProgramType>Medicaid</ProgramType>
<EligibilityBeginDate>2021-07-01</EligibilityBeginDate>
<CategoryOfEligibilityType>073</CategoryOfEligibilityType>
<DecisionStatus>Approved</DecisionStatus>
<DecisionReason/>
</EligibilityDetailRow>
</EligibilityInfo>
<IncarcerationInfo>
<IncarcerationRow/>
</IncarcerationInfo>
<ResourceInfo>
<ResourceRow>
<ResourceCategory/>
<ResourceDetailCategoryType/>
<ResourceAmount/>
<ResourceOwnershipType/>
<InstitutionName/>
</ResourceRow>
</ResourceInfo>
<FamilyCompositionInfo>
<RelationshipToHOH/>
</FamilyCompositionInfo>
</PersonRow>
</PersonInfo>
</CaseTypeRow>
<CaseTypeRow>
<ProgramType>Medicaid</ProgramType>
<ActionType>InitialLoad</ActionType>
<KeyInfo>
<KeyTypeRow>
<KeyType>CaseID</KeyType>
<KeyValue>101569748</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>HouseholdID</KeyType>
<KeyValue>95</KeyValue>
</KeyTypeRow>
</KeyInfo>
<OfficeInfo>
<AddressRow>
<AddressLine1>6971 Lincoln Rd Ext</AddressLine1>
<City>Hattiesburg</City>
<State>MS</State>
<Zipcode>39402</Zipcode>
</AddressRow>
<Phone>6012645386</Phone>
<FaxNumber>6012611244</FaxNumber>
</OfficeInfo>
<CaseWorkerInfo>
<CaseworkerID>122710</CaseworkerID>
<EmailID>Ontario.Lewis@medicaid.ms.gov</EmailID>
<FirstName>Ontario</FirstName>
<LastName>Lewis</LastName>
</CaseWorkerInfo>
<PersonInfo>
<PersonRow>
<ProgramType>Medicaid</ProgramType>
<KeyInfo>
<KeyTypeRow>
<KeyType>MedicaidID</KeyType>
<KeyValue>385792441</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>PersonID</KeyType>
<KeyValue>281</KeyValue>
</KeyTypeRow>
</KeyInfo>
<PersonIdentityInfo>
<NameInfo>
<FirstName>Michael</FirstName>
<MiddleName>J</MiddleName>
<LastName>Hubbard</LastName>
</NameInfo>
<DateOfBirth>2003-10-10</DateOfBirth>
<SSN>425952940</SSN>
<Gender>Male</Gender>
<MaritalStatus>Single</MaritalStatus>
<DeceasedDate/>
<USCitizenshipInd>false</USCitizenshipInd>
</PersonIdentityInfo>
<ContactInfo>
<AddressType>
<AddressRow>
<AddressCategoryType>Home</AddressCategoryType>
<AddressLine1>401 Lilac St</AddressLine1>
<AddressLine2/>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39401</ZipCode>
<ZipCodeExt>5501</ZipCodeExt>
<County>Lamar</County>
</AddressRow>
</AddressType>
<PhoneType>
<PhoneRow>
<PhoneCategoryType>Home</PhoneCategoryType>
<Phone/>
<PhoneExt/>
</PhoneRow>
</PhoneType>
<EmailID/>
</ContactInfo>
<EligibilityInfo>
<EligibilityDetailRow>
<ProgramType>Medicaid</ProgramType>
<EligibilityBeginDate>2021-07-01</EligibilityBeginDate>
<CategoryOfEligibilityType>073</CategoryOfEligibilityType>
<DecisionStatus>Approved</DecisionStatus>
<DecisionReason/>
</EligibilityDetailRow>
</EligibilityInfo>
<IncarcerationInfo>
<IncarcerationRow/>
</IncarcerationInfo>
<ResourceInfo>
<ResourceRow>
<ResourceCategory/>
<ResourceDetailCategoryType/>
<ResourceAmount/>
<ResourceOwnershipType/>
<InstitutionName/>
</ResourceRow>
</ResourceInfo>
<FamilyCompositionInfo>
<RelationshipToHOH/>
</FamilyCompositionInfo>
</PersonRow>
</PersonInfo>
</CaseTypeRow>
<CaseTypeRow>
<ProgramType>Medicaid</ProgramType>
<ActionType>InitialLoad</ActionType>
<KeyInfo>
<KeyTypeRow>
<KeyType>CaseID</KeyType>
<KeyValue>101569748</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>HouseholdID</KeyType>
<KeyValue>95</KeyValue>
</KeyTypeRow>
</KeyInfo>
<OfficeInfo>
<AddressRow>
<AddressLine1>6971 Lincoln Rd Ext</AddressLine1>
<City>Hattiesburg</City>
<State>MS</State>
<Zipcode>39402</Zipcode>
</AddressRow>
<Phone>6012645386</Phone>
<FaxNumber>6012611244</FaxNumber>
</OfficeInfo>
<CaseWorkerInfo>
<CaseworkerID>122710</CaseworkerID>
<EmailID>Ontario.Lewis@medicaid.ms.gov</EmailID>
<FirstName>Ontario</FirstName>
<LastName>Lewis</LastName>
</CaseWorkerInfo>
<PersonInfo>
<PersonRow>
<ProgramType>Medicaid</ProgramType>
<KeyInfo>
<KeyTypeRow>
<KeyType>MedicaidID</KeyType>
<KeyValue>385792441</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>PersonID</KeyType>
<KeyValue>281</KeyValue>
</KeyTypeRow>
</KeyInfo>
<PersonIdentityInfo>
<NameInfo>
<FirstName>Michael</FirstName>
<MiddleName>J</MiddleName>
<LastName>Hubbard</LastName>
</NameInfo>
<DateOfBirth>2003-10-10</DateOfBirth>
<SSN>425952940</SSN>
<Gender>Male</Gender>
<MaritalStatus>Single</MaritalStatus>
<DeceasedDate/>
<USCitizenshipInd>false</USCitizenshipInd>
</PersonIdentityInfo>
<ContactInfo>
<AddressType>
<AddressRow>
<AddressCategoryType>Home</AddressCategoryType>
<AddressLine1>401 Lilac St</AddressLine1>
<AddressLine2/>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39401</ZipCode>
<ZipCodeExt>5501</ZipCodeExt>
<County>Lamar</County>
</AddressRow>
</AddressType>
<PhoneType>
<PhoneRow>
<PhoneCategoryType>Home</PhoneCategoryType>
<Phone/>
<PhoneExt/>
</PhoneRow>
</PhoneType>
<EmailID/>
</ContactInfo>
<EligibilityInfo>
<EligibilityDetailRow>
<ProgramType>Medicaid</ProgramType>
<EligibilityBeginDate>2021-07-01</EligibilityBeginDate>
<CategoryOfEligibilityType>073</CategoryOfEligibilityType>
<DecisionStatus>Approved</DecisionStatus>
<DecisionReason/>
</EligibilityDetailRow>
</EligibilityInfo>
<IncarcerationInfo>
<IncarcerationRow/>
</IncarcerationInfo>
<ResourceInfo>
<ResourceRow>
<ResourceCategory/>
<ResourceDetailCategoryType/>
<ResourceAmount/>
<ResourceOwnershipType/>
<InstitutionName/>
</ResourceRow>
</ResourceInfo>
<FamilyCompositionInfo>
<RelationshipToHOH/>
</FamilyCompositionInfo>
</PersonRow>
</PersonInfo>
</CaseTypeRow>
<CaseTypeRow>
<ProgramType>Medicaid</ProgramType>
<ActionType>InitialLoad</ActionType>
<KeyInfo>
<KeyTypeRow>
<KeyType>CaseID</KeyType>
<KeyValue>101569748</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>HouseholdID</KeyType>
<KeyValue>95</KeyValue>
</KeyTypeRow>
</KeyInfo>
<OfficeInfo>
<AddressRow>
<AddressLine1>6971 Lincoln Rd Ext</AddressLine1>
<City>Hattiesburg</City>
<State>MS</State>
<Zipcode>39402</Zipcode>
</AddressRow>
<Phone>6012645386</Phone>
<FaxNumber>6012611244</FaxNumber>
</OfficeInfo>
<CaseWorkerInfo>
<CaseworkerID>122710</CaseworkerID>
<EmailID>Ontario.Lewis@medicaid.ms.gov</EmailID>
<FirstName>Ontario</FirstName>
<LastName>Lewis</LastName>
</CaseWorkerInfo>
<PersonInfo>
<PersonRow>
<ProgramType>Medicaid</ProgramType>
<KeyInfo>
<KeyTypeRow>
<KeyType>MedicaidID</KeyType>
<KeyValue>385492804</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>PersonID</KeyType>
<KeyValue>268</KeyValue>
</KeyTypeRow>
</KeyInfo>
<PersonIdentityInfo>
<NameInfo>
<FirstName>Kardeem</FirstName>
<MiddleName>D</MiddleName>
<LastName>Walker</LastName>
</NameInfo>
<DateOfBirth>2002-12-17</DateOfBirth>
<SSN>426936260</SSN>
<Gender>Male</Gender>
<MaritalStatus>Single</MaritalStatus>
<DeceasedDate/>
<USCitizenshipInd>false</USCitizenshipInd>
</PersonIdentityInfo>
<ContactInfo>
<AddressType>
<AddressRow>
<AddressCategoryType>Home</AddressCategoryType>
<AddressLine1>401 Lilac St</AddressLine1>
<AddressLine2/>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39401</ZipCode>
<ZipCodeExt>5501</ZipCodeExt>
<County>Lamar</County>
</AddressRow>
</AddressType>
<PhoneType>
<PhoneRow>
<PhoneCategoryType>Home</PhoneCategoryType>
<Phone/>
<PhoneExt/>
</PhoneRow>
</PhoneType>
<EmailID/>
</ContactInfo>
<EligibilityInfo>
<EligibilityDetailRow>
<ProgramType>Medicaid</ProgramType>
<EligibilityBeginDate>2022-01-01</EligibilityBeginDate>
<CategoryOfEligibilityType>073</CategoryOfEligibilityType>
<DecisionStatus>Approved</DecisionStatus>
<DecisionReason/>
</EligibilityDetailRow>
</EligibilityInfo>
<IncarcerationInfo>
<IncarcerationRow/>
</IncarcerationInfo>
<ResourceInfo>
<ResourceRow>
<ResourceCategory/>
<ResourceDetailCategoryType/>
<ResourceAmount/>
<ResourceOwnershipType/>
<InstitutionName/>
</ResourceRow>
</ResourceInfo>
<FamilyCompositionInfo>
<RelationshipToHOH/>
</FamilyCompositionInfo>
</PersonRow>
</PersonInfo>
</CaseTypeRow>
<CaseTypeRow>
<ProgramType>Medicaid</ProgramType>
<ActionType>InitialLoad</ActionType>
<KeyInfo>
<KeyTypeRow>
<KeyType>CaseID</KeyType>
<KeyValue>101569748</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>HouseholdID</KeyType>
<KeyValue>95</KeyValue>
</KeyTypeRow>
</KeyInfo>
<OfficeInfo>
<AddressRow>
<AddressLine1>6971 Lincoln Rd Ext</AddressLine1>
<City>Hattiesburg</City>
<State>MS</State>
<Zipcode>39402</Zipcode>
</AddressRow>
<Phone>6012645386</Phone>
<FaxNumber>6012611244</FaxNumber>
</OfficeInfo>
<CaseWorkerInfo>
<CaseworkerID>122710</CaseworkerID>
<EmailID>Ontario.Lewis@medicaid.ms.gov</EmailID>
<FirstName>Ontario</FirstName>
<LastName>Lewis</LastName>
</CaseWorkerInfo>
<PersonInfo>
<PersonRow>
<ProgramType>Medicaid</ProgramType>
<KeyInfo>
<KeyTypeRow>
<KeyType>MedicaidID</KeyType>
<KeyValue>385492804</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>PersonID</KeyType>
<KeyValue>268</KeyValue>
</KeyTypeRow>
</KeyInfo>
<PersonIdentityInfo>
<NameInfo>
<FirstName>Kardeem</FirstName>
<MiddleName>D</MiddleName>
<LastName>Walker</LastName>
</NameInfo>
<DateOfBirth>2002-12-17</DateOfBirth>
<SSN>426936260</SSN>
<Gender>Male</Gender>
<MaritalStatus>Single</MaritalStatus>
<DeceasedDate/>
<USCitizenshipInd>false</USCitizenshipInd>
</PersonIdentityInfo>
<ContactInfo>
<AddressType>
<AddressRow>
<AddressCategoryType>Home</AddressCategoryType>
<AddressLine1>401 Lilac St</AddressLine1>
<AddressLine2/>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39401</ZipCode>
<ZipCodeExt>5501</ZipCodeExt>
<County>Lamar</County>
</AddressRow>
</AddressType>
<PhoneType>
<PhoneRow>
<PhoneCategoryType>Home</PhoneCategoryType>
<Phone/>
<PhoneExt/>
</PhoneRow>
</PhoneType>
<EmailID/>
</ContactInfo>
<EligibilityInfo>
<EligibilityDetailRow>
<ProgramType>Medicaid</ProgramType>
<EligibilityBeginDate>2022-01-01</EligibilityBeginDate>
<CategoryOfEligibilityType>073</CategoryOfEligibilityType>
<DecisionStatus>Approved</DecisionStatus>
<DecisionReason/>
</EligibilityDetailRow>
</EligibilityInfo>
<IncarcerationInfo>
<IncarcerationRow/>
</IncarcerationInfo>
<ResourceInfo>
<ResourceRow>
<ResourceCategory/>
<ResourceDetailCategoryType/>
<ResourceAmount/>
<ResourceOwnershipType/>
<InstitutionName/>
</ResourceRow>
</ResourceInfo>
<FamilyCompositionInfo>
<RelationshipToHOH/>
</FamilyCompositionInfo>
</PersonRow>
</PersonInfo>
</CaseTypeRow>
<CaseTypeRow>
<ProgramType>Medicaid</ProgramType>
<ActionType>InitialLoad</ActionType>
<KeyInfo>
<KeyTypeRow>
<KeyType>CaseID</KeyType>
<KeyValue>101569748</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>HouseholdID</KeyType>
<KeyValue>95</KeyValue>
</KeyTypeRow>
</KeyInfo>
<OfficeInfo>
<AddressRow>
<AddressLine1>6971 Lincoln Rd Ext</AddressLine1>
<City>Hattiesburg</City>
<State>MS</State>
<Zipcode>39402</Zipcode>
</AddressRow>
<Phone>6012645386</Phone>
<FaxNumber>6012611244</FaxNumber>
</OfficeInfo>
<CaseWorkerInfo>
<CaseworkerID>122710</CaseworkerID>
<EmailID>Ontario.Lewis@medicaid.ms.gov</EmailID>
<FirstName>Ontario</FirstName>
<LastName>Lewis</LastName>
</CaseWorkerInfo>
<PersonInfo>
<PersonRow>
<ProgramType>Medicaid</ProgramType>
<KeyInfo>
<KeyTypeRow>
<KeyType>MedicaidID</KeyType>
<KeyValue>385492804</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>PersonID</KeyType>
<KeyValue>268</KeyValue>
</KeyTypeRow>
</KeyInfo>
<PersonIdentityInfo>
<NameInfo>
<FirstName>Kardeem</FirstName>
<MiddleName>D</MiddleName>
<LastName>Walker</LastName>
</NameInfo>
<DateOfBirth>2002-12-17</DateOfBirth>
<SSN>426936260</SSN>
<Gender>Male</Gender>
<MaritalStatus>Single</MaritalStatus>
<DeceasedDate/>
<USCitizenshipInd>false</USCitizenshipInd>
</PersonIdentityInfo>
<ContactInfo>
<AddressType>
<AddressRow>
<AddressCategoryType>Home</AddressCategoryType>
<AddressLine1>401 Lilac St</AddressLine1>
<AddressLine2/>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39401</ZipCode>
<ZipCodeExt>5501</ZipCodeExt>
<County>Lamar</County>
</AddressRow>
</AddressType>
<PhoneType>
<PhoneRow>
<PhoneCategoryType>Home</PhoneCategoryType>
<Phone/>
<PhoneExt/>
</PhoneRow>
</PhoneType>
<EmailID/>
</ContactInfo>
<EligibilityInfo>
<EligibilityDetailRow>
<ProgramType>Medicaid</ProgramType>
<EligibilityBeginDate>2022-01-01</EligibilityBeginDate>
<CategoryOfEligibilityType>073</CategoryOfEligibilityType>
<DecisionStatus>Approved</DecisionStatus>
<DecisionReason/>
</EligibilityDetailRow>
</EligibilityInfo>
<IncarcerationInfo>
<IncarcerationRow/>
</IncarcerationInfo>
<ResourceInfo>
<ResourceRow>
<ResourceCategory/>
<ResourceDetailCategoryType/>
<ResourceAmount/>
<ResourceOwnershipType/>
<InstitutionName/>
</ResourceRow>
</ResourceInfo>
<FamilyCompositionInfo>
<RelationshipToHOH/>
</FamilyCompositionInfo>
</PersonRow>
</PersonInfo>
</CaseTypeRow>
</ArrayOfCaseTypeRow>
</eligibilityChangeRequestType>
SQL>
|
|
|
|
|
|
| Re: XML help needed [message #685762 is a reply to message #685761] |
Fri, 18 March 2022 09:07 |
Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
select XMLSerialize(
DOCUMENT
XMLElement(
"eligibilityChangeRequestType",
xmlattributes('http://softheon.com/2021/02/EligibilityChanges' as "xmlns"),
XMLElement("ArrayOfCaseTypeRow",
XMLAgg(
XMLElement("CaseTypeRow",
XMLElement("ProgramType",'Medicaid'),
XMLElement("ActionType",'InitialLoad'),
XMLElement("KeyInfo",
XMLElement("KeyTypeRow",
XMLElement("KeyType",'CaseID'),
XMLElement("KeyValue",Casenumber)
),
XMLElement("KeyTypeRow",
XMLElement("KeyType",'HouseholdID'),
XMLElement("KeyValue",b.Household_ID)
)
),
XMLElement("OfficeInfo",
XMLElement("AddressRow",
XMLElement("AddressLine1",REGIONALOFFICESTREETADDRESS1),
XMLElement("City",REGIONALOFFICECITY),
XMLElement("State",REGIONALOFFICESTATE),
XMLElement("Zipcode",REGIONALOFFICEZIP)
),
XMLElement("Phone",Regiionalofficephonenumber),
XMLElement("FaxNumber",Regiionalofficefaxnumber)
),
XMLElement("CaseWorkerInfo",
XMLElement("CaseworkerID",CaseworkerID),
XMLElement("EmailID",CaseworkeremailID),
XMLElement("FirstName",CaseworkerFirstName),
XMLElement("LastName",CaseworkerLastName)
),
XMLElement("PersonInfo",
XMLAgg(
XMLElement("PersonRow",
XMLElement("ProgramType",'Medicaid'),
XMLElement("KeyInfo",
XMLElement("KeyTypeRow",
XMLElement("KeyType",'MedicaidID'),
XMLElement("KeyValue",Medicaidid)
),
XMLElement("KeyTypeRow",
XMLElement("KeyType",'PersonID'),
XMLElement("KeyValue",PersonID)
)
),
XMLElement("PersonIdentityInfo",
XMLElement("NameInfo",
XMLElement("FirstName",First_Name),
XMLElement("MiddleName",Middle_Initial),
XMLElement("LastName",Last_Name)
),
XMLElement("DateOfBirth",DOB),
XMLElement("SSN",SSN),
XMLElement("Gender",Genderdesc),
XMLElement("MaritalStatus",Maritalstatusdesc),
XMLElement("DeceasedDate",DeathDate),
XMLElement("USCitizenshipInd",USCitizenshipInd)
),
XMLElement("ContactInfo",
XMLElement("AddressType",
XMLElement("AddressRow",
XMLElement("AddressCategoryType",'Home'),
XMLElement("AddressLine1",Address_Line1),
XMLElement("AddressLine2",Address_Line2),
XMLElement("City",City),
XMLElement("State",State),
XMLElement("ZipCode",Zipcode_desc),
XMLElement("ZipCodeExt",Zipcode_ext),
XMLElement("County",County)
)
),
XMLElement("PhoneType",
XMLElement("PhoneRow",
XMLElement("PhoneCategoryType",'Home'),
XMLElement("Phone",homephonenumber),
XMLElement("PhoneExt",homephoneextension)
)
),
XMLElement("EmailID",householdemailaddress)
),
XMLElement("EligibilityInfo",
XMLElement("EligibilityDetailRow",
XMLElement("ProgramType",'Medicaid'),
XMLElement("EligibilityBeginDate",ELIGIBILITY_STATUS_BEGIN_DATE),
XMLElement("CategoryOfEligibilityType",CATEGORYOFELIGIBILITYTYPE),
XMLElement("DecisionStatus",DECISIONSTATUSCODEDESC),
XMLElement("DecisionReason",homephonenumber)
)
),
XMLElement("IncarcerationInfo",
XMLElement("IncarcerationRow",'')
),
XMLElement("ResourceInfo",
(select XMLAgg(
XMLElement("ResourceRow",
XMLElement("ResourceCategory",ResourceCategoryType),
XMLElement("ResourceDetailCategoryType",ResourceDetailCategoryType),
XMLElement("ResourceAmount",ResourceAmount),
XMLElement("ResourceOwnershipType",ResourceOwnershipType),
XMLElement("InstitutionName",institution_name)
)
)
from c tcr
where tcr.consumer_id = b.consumer_id
)
),
XMLElement("FamilyCompositionInfo",
XMLElement("RelationshipToHOH",'')
)
)
)
)
)
)
)
)
indent size = 2
) xml
from a,
b
where a.household_id = b.household_id
group by b.household_id,
b.Casenumber,
b.REGIONALOFFICESTREETADDRESS1,
b.REGIONALOFFICECITY,
b.REGIONALOFFICESTATE,
b.REGIONALOFFICEZIP,
b.Regiionalofficephonenumber,
b.Regiionalofficefaxnumber,
b.CaseworkerID,
b.CaseworkeremailID,
b.CaseworkerFirstName,
b.CaseworkerLastName
/
XML
--------------------------------------------------------------------------------------
<eligibilityChangeRequestType xmlns="http://softheon.com/2021/02/EligibilityChanges">
<ArrayOfCaseTypeRow>
<CaseTypeRow>
<ProgramType>Medicaid</ProgramType>
<ActionType>InitialLoad</ActionType>
<KeyInfo>
<KeyTypeRow>
<KeyType>CaseID</KeyType>
<KeyValue>101569748</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>HouseholdID</KeyType>
<KeyValue>95</KeyValue>
</KeyTypeRow>
</KeyInfo>
<OfficeInfo>
<AddressRow>
<AddressLine1>6971 Lincoln Rd Ext</AddressLine1>
<City>Hattiesburg</City>
<State>MS</State>
<Zipcode>39402</Zipcode>
</AddressRow>
<Phone>6012645386</Phone>
<FaxNumber>6012611244</FaxNumber>
</OfficeInfo>
<CaseWorkerInfo>
<CaseworkerID>122710</CaseworkerID>
<EmailID>Ontario.Lewis@medicaid.ms.gov</EmailID>
<FirstName>Ontario</FirstName>
<LastName>Lewis</LastName>
</CaseWorkerInfo>
<PersonInfo>
<PersonRow>
<ProgramType>Medicaid</ProgramType>
<KeyInfo>
<KeyTypeRow>
<KeyType>MedicaidID</KeyType>
<KeyValue>301869568</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>PersonID</KeyType>
<KeyValue>236</KeyValue>
</KeyTypeRow>
</KeyInfo>
<PersonIdentityInfo>
<NameInfo>
<FirstName>Annie</FirstName>
<MiddleName>B</MiddleName>
<LastName>Jaynes</LastName>
</NameInfo>
<DateOfBirth>1947-09-15</DateOfBirth>
<SSN>428985760</SSN>
<Gender>Female</Gender>
<MaritalStatus>Widowed</MaritalStatus>
<DeceasedDate/>
<USCitizenshipInd>false</USCitizenshipInd>
</PersonIdentityInfo>
<ContactInfo>
<AddressType>
<AddressRow>
<AddressCategoryType>Home</AddressCategoryType>
<AddressLine1>401 Lilac St</AddressLine1>
<AddressLine2/>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39401</ZipCode>
<ZipCodeExt>5501</ZipCodeExt>
<County>Lamar</County>
</AddressRow>
</AddressType>
<PhoneType>
<PhoneRow>
<PhoneCategoryType>Home</PhoneCategoryType>
<Phone/>
<PhoneExt/>
</PhoneRow>
</PhoneType>
<EmailID/>
</ContactInfo>
<EligibilityInfo>
<EligibilityDetailRow>
<ProgramType>Medicaid</ProgramType>
<EligibilityBeginDate>2021-11-01</EligibilityBeginDate>
<CategoryOfEligibilityType>051</CategoryOfEligibilityType>
<DecisionStatus>Approved</DecisionStatus>
<DecisionReason/>
</EligibilityDetailRow>
</EligibilityInfo>
<IncarcerationInfo>
<IncarcerationRow/>
</IncarcerationInfo>
<ResourceInfo>
<ResourceRow>
<ResourceCategory>Liquid</ResourceCategory>
<ResourceDetailCategoryType>Checking Account</ResourceDetailCategoryType>
<ResourceAmount>52</ResourceAmount>
<ResourceOwnershipType>N</ResourceOwnershipType>
<InstitutionName>BancorpSouth Bank</InstitutionName>
</ResourceRow>
<ResourceRow>
<ResourceCategory>Liquid</ResourceCategory>
<ResourceDetailCategoryType>Checking Account</ResourceDetailCategoryType>
<ResourceAmount>906.85</ResourceAmount>
<ResourceOwnershipType>N</ResourceOwnershipType>
<InstitutionName>Regions Bank</InstitutionName>
</ResourceRow>
</ResourceInfo>
<FamilyCompositionInfo>
<RelationshipToHOH/>
</FamilyCompositionInfo>
</PersonRow>
<PersonRow>
<ProgramType>Medicaid</ProgramType>
<KeyInfo>
<KeyTypeRow>
<KeyType>MedicaidID</KeyType>
<KeyValue>385492804</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>PersonID</KeyType>
<KeyValue>268</KeyValue>
</KeyTypeRow>
</KeyInfo>
<PersonIdentityInfo>
<NameInfo>
<FirstName>Kardeem</FirstName>
<MiddleName>D</MiddleName>
<LastName>Walker</LastName>
</NameInfo>
<DateOfBirth>2002-12-17</DateOfBirth>
<SSN>426936260</SSN>
<Gender>Male</Gender>
<MaritalStatus>Single</MaritalStatus>
<DeceasedDate/>
<USCitizenshipInd>false</USCitizenshipInd>
</PersonIdentityInfo>
<ContactInfo>
<AddressType>
<AddressRow>
<AddressCategoryType>Home</AddressCategoryType>
<AddressLine1>401 Lilac St</AddressLine1>
<AddressLine2/>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39401</ZipCode>
<ZipCodeExt>5501</ZipCodeExt>
<County>Lamar</County>
</AddressRow>
</AddressType>
<PhoneType>
<PhoneRow>
<PhoneCategoryType>Home</PhoneCategoryType>
<Phone/>
<PhoneExt/>
</PhoneRow>
</PhoneType>
<EmailID/>
</ContactInfo>
<EligibilityInfo>
<EligibilityDetailRow>
<ProgramType>Medicaid</ProgramType>
<EligibilityBeginDate>2022-01-01</EligibilityBeginDate>
<CategoryOfEligibilityType>073</CategoryOfEligibilityType>
<DecisionStatus>Approved</DecisionStatus>
<DecisionReason/>
</EligibilityDetailRow>
</EligibilityInfo>
<IncarcerationInfo>
<IncarcerationRow/>
</IncarcerationInfo>
<ResourceInfo>
<ResourceRow>
<ResourceCategory/>
<ResourceDetailCategoryType/>
<ResourceAmount/>
<ResourceOwnershipType/>
<InstitutionName/>
</ResourceRow>
</ResourceInfo>
<FamilyCompositionInfo>
<RelationshipToHOH/>
</FamilyCompositionInfo>
</PersonRow>
<PersonRow>
<ProgramType>Medicaid</ProgramType>
<KeyInfo>
<KeyTypeRow>
<KeyType>MedicaidID</KeyType>
<KeyValue>385492804</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>PersonID</KeyType>
<KeyValue>268</KeyValue>
</KeyTypeRow>
</KeyInfo>
<PersonIdentityInfo>
<NameInfo>
<FirstName>Kardeem</FirstName>
<MiddleName>D</MiddleName>
<LastName>Walker</LastName>
</NameInfo>
<DateOfBirth>2002-12-17</DateOfBirth>
<SSN>426936260</SSN>
<Gender>Male</Gender>
<MaritalStatus>Single</MaritalStatus>
<DeceasedDate/>
<USCitizenshipInd>false</USCitizenshipInd>
</PersonIdentityInfo>
<ContactInfo>
<AddressType>
<AddressRow>
<AddressCategoryType>Home</AddressCategoryType>
<AddressLine1>401 Lilac St</AddressLine1>
<AddressLine2/>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39401</ZipCode>
<ZipCodeExt>5501</ZipCodeExt>
<County>Lamar</County>
</AddressRow>
</AddressType>
<PhoneType>
<PhoneRow>
<PhoneCategoryType>Home</PhoneCategoryType>
<Phone/>
<PhoneExt/>
</PhoneRow>
</PhoneType>
<EmailID/>
</ContactInfo>
<EligibilityInfo>
<EligibilityDetailRow>
<ProgramType>Medicaid</ProgramType>
<EligibilityBeginDate>2022-01-01</EligibilityBeginDate>
<CategoryOfEligibilityType>073</CategoryOfEligibilityType>
<DecisionStatus>Approved</DecisionStatus>
<DecisionReason/>
</EligibilityDetailRow>
</EligibilityInfo>
<IncarcerationInfo>
<IncarcerationRow/>
</IncarcerationInfo>
<ResourceInfo>
<ResourceRow>
<ResourceCategory/>
<ResourceDetailCategoryType/>
<ResourceAmount/>
<ResourceOwnershipType/>
<InstitutionName/>
</ResourceRow>
</ResourceInfo>
<FamilyCompositionInfo>
<RelationshipToHOH/>
</FamilyCompositionInfo>
</PersonRow>
<PersonRow>
<ProgramType>Medicaid</ProgramType>
<KeyInfo>
<KeyTypeRow>
<KeyType>MedicaidID</KeyType>
<KeyValue>385492804</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>PersonID</KeyType>
<KeyValue>268</KeyValue>
</KeyTypeRow>
</KeyInfo>
<PersonIdentityInfo>
<NameInfo>
<FirstName>Kardeem</FirstName>
<MiddleName>D</MiddleName>
<LastName>Walker</LastName>
</NameInfo>
<DateOfBirth>2002-12-17</DateOfBirth>
<SSN>426936260</SSN>
<Gender>Male</Gender>
<MaritalStatus>Single</MaritalStatus>
<DeceasedDate/>
<USCitizenshipInd>false</USCitizenshipInd>
</PersonIdentityInfo>
<ContactInfo>
<AddressType>
<AddressRow>
<AddressCategoryType>Home</AddressCategoryType>
<AddressLine1>401 Lilac St</AddressLine1>
<AddressLine2/>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39401</ZipCode>
<ZipCodeExt>5501</ZipCodeExt>
<County>Lamar</County>
</AddressRow>
</AddressType>
<PhoneType>
<PhoneRow>
<PhoneCategoryType>Home</PhoneCategoryType>
<Phone/>
<PhoneExt/>
</PhoneRow>
</PhoneType>
<EmailID/>
</ContactInfo>
<EligibilityInfo>
<EligibilityDetailRow>
<ProgramType>Medicaid</ProgramType>
<EligibilityBeginDate>2022-01-01</EligibilityBeginDate>
<CategoryOfEligibilityType>073</CategoryOfEligibilityType>
<DecisionStatus>Approved</DecisionStatus>
<DecisionReason/>
</EligibilityDetailRow>
</EligibilityInfo>
<IncarcerationInfo>
<IncarcerationRow/>
</IncarcerationInfo>
<ResourceInfo>
<ResourceRow>
<ResourceCategory/>
<ResourceDetailCategoryType/>
<ResourceAmount/>
<ResourceOwnershipType/>
<InstitutionName/>
</ResourceRow>
</ResourceInfo>
<FamilyCompositionInfo>
<RelationshipToHOH/>
</FamilyCompositionInfo>
</PersonRow>
<PersonRow>
<ProgramType>Medicaid</ProgramType>
<KeyInfo>
<KeyTypeRow>
<KeyType>MedicaidID</KeyType>
<KeyValue>385792441</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>PersonID</KeyType>
<KeyValue>281</KeyValue>
</KeyTypeRow>
</KeyInfo>
<PersonIdentityInfo>
<NameInfo>
<FirstName>Michael</FirstName>
<MiddleName>J</MiddleName>
<LastName>Hubbard</LastName>
</NameInfo>
<DateOfBirth>2003-10-10</DateOfBirth>
<SSN>425952940</SSN>
<Gender>Male</Gender>
<MaritalStatus>Single</MaritalStatus>
<DeceasedDate/>
<USCitizenshipInd>false</USCitizenshipInd>
</PersonIdentityInfo>
<ContactInfo>
<AddressType>
<AddressRow>
<AddressCategoryType>Home</AddressCategoryType>
<AddressLine1>401 Lilac St</AddressLine1>
<AddressLine2/>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39401</ZipCode>
<ZipCodeExt>5501</ZipCodeExt>
<County>Lamar</County>
</AddressRow>
</AddressType>
<PhoneType>
<PhoneRow>
<PhoneCategoryType>Home</PhoneCategoryType>
<Phone/>
<PhoneExt/>
</PhoneRow>
</PhoneType>
<EmailID/>
</ContactInfo>
<EligibilityInfo>
<EligibilityDetailRow>
<ProgramType>Medicaid</ProgramType>
<EligibilityBeginDate>2021-07-01</EligibilityBeginDate>
<CategoryOfEligibilityType>073</CategoryOfEligibilityType>
<DecisionStatus>Approved</DecisionStatus>
<DecisionReason/>
</EligibilityDetailRow>
</EligibilityInfo>
<IncarcerationInfo>
<IncarcerationRow/>
</IncarcerationInfo>
<ResourceInfo>
<ResourceRow>
<ResourceCategory/>
<ResourceDetailCategoryType/>
<ResourceAmount/>
<ResourceOwnershipType/>
<InstitutionName/>
</ResourceRow>
</ResourceInfo>
<FamilyCompositionInfo>
<RelationshipToHOH/>
</FamilyCompositionInfo>
</PersonRow>
<PersonRow>
<ProgramType>Medicaid</ProgramType>
<KeyInfo>
<KeyTypeRow>
<KeyType>MedicaidID</KeyType>
<KeyValue>385792441</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>PersonID</KeyType>
<KeyValue>281</KeyValue>
</KeyTypeRow>
</KeyInfo>
<PersonIdentityInfo>
<NameInfo>
<FirstName>Michael</FirstName>
<MiddleName>J</MiddleName>
<LastName>Hubbard</LastName>
</NameInfo>
<DateOfBirth>2003-10-10</DateOfBirth>
<SSN>425952940</SSN>
<Gender>Male</Gender>
<MaritalStatus>Single</MaritalStatus>
<DeceasedDate/>
<USCitizenshipInd>false</USCitizenshipInd>
</PersonIdentityInfo>
<ContactInfo>
<AddressType>
<AddressRow>
<AddressCategoryType>Home</AddressCategoryType>
<AddressLine1>401 Lilac St</AddressLine1>
<AddressLine2/>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39401</ZipCode>
<ZipCodeExt>5501</ZipCodeExt>
<County>Lamar</County>
</AddressRow>
</AddressType>
<PhoneType>
<PhoneRow>
<PhoneCategoryType>Home</PhoneCategoryType>
<Phone/>
<PhoneExt/>
</PhoneRow>
</PhoneType>
<EmailID/>
</ContactInfo>
<EligibilityInfo>
<EligibilityDetailRow>
<ProgramType>Medicaid</ProgramType>
<EligibilityBeginDate>2021-07-01</EligibilityBeginDate>
<CategoryOfEligibilityType>073</CategoryOfEligibilityType>
<DecisionStatus>Approved</DecisionStatus>
<DecisionReason/>
</EligibilityDetailRow>
</EligibilityInfo>
<IncarcerationInfo>
<IncarcerationRow/>
</IncarcerationInfo>
<ResourceInfo>
<ResourceRow>
<ResourceCategory/>
<ResourceDetailCategoryType/>
<ResourceAmount/>
<ResourceOwnershipType/>
<InstitutionName/>
</ResourceRow>
</ResourceInfo>
<FamilyCompositionInfo>
<RelationshipToHOH/>
</FamilyCompositionInfo>
</PersonRow>
<PersonRow>
<ProgramType>Medicaid</ProgramType>
<KeyInfo>
<KeyTypeRow>
<KeyType>MedicaidID</KeyType>
<KeyValue>385792441</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>PersonID</KeyType>
<KeyValue>281</KeyValue>
</KeyTypeRow>
</KeyInfo>
<PersonIdentityInfo>
<NameInfo>
<FirstName>Michael</FirstName>
<MiddleName>J</MiddleName>
<LastName>Hubbard</LastName>
</NameInfo>
<DateOfBirth>2003-10-10</DateOfBirth>
<SSN>425952940</SSN>
<Gender>Male</Gender>
<MaritalStatus>Single</MaritalStatus>
<DeceasedDate/>
<USCitizenshipInd>false</USCitizenshipInd>
</PersonIdentityInfo>
<ContactInfo>
<AddressType>
<AddressRow>
<AddressCategoryType>Home</AddressCategoryType>
<AddressLine1>401 Lilac St</AddressLine1>
<AddressLine2/>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39401</ZipCode>
<ZipCodeExt>5501</ZipCodeExt>
<County>Lamar</County>
</AddressRow>
</AddressType>
<PhoneType>
<PhoneRow>
<PhoneCategoryType>Home</PhoneCategoryType>
<Phone/>
<PhoneExt/>
</PhoneRow>
</PhoneType>
<EmailID/>
</ContactInfo>
<EligibilityInfo>
<EligibilityDetailRow>
<ProgramType>Medicaid</ProgramType>
<EligibilityBeginDate>2021-07-01</EligibilityBeginDate>
<CategoryOfEligibilityType>073</CategoryOfEligibilityType>
<DecisionStatus>Approved</DecisionStatus>
<DecisionReason/>
</EligibilityDetailRow>
</EligibilityInfo>
<IncarcerationInfo>
<IncarcerationRow/>
</IncarcerationInfo>
<ResourceInfo>
<ResourceRow>
<ResourceCategory/>
<ResourceDetailCategoryType/>
<ResourceAmount/>
<ResourceOwnershipType/>
<InstitutionName/>
</ResourceRow>
</ResourceInfo>
<FamilyCompositionInfo>
<RelationshipToHOH/>
</FamilyCompositionInfo>
</PersonRow>
<PersonRow>
<ProgramType>Medicaid</ProgramType>
<KeyInfo>
<KeyTypeRow>
<KeyType>MedicaidID</KeyType>
<KeyValue>301869568</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>PersonID</KeyType>
<KeyValue>236</KeyValue>
</KeyTypeRow>
</KeyInfo>
<PersonIdentityInfo>
<NameInfo>
<FirstName>Annie</FirstName>
<MiddleName>B</MiddleName>
<LastName>Jaynes</LastName>
</NameInfo>
<DateOfBirth>1947-09-15</DateOfBirth>
<SSN>428985760</SSN>
<Gender>Female</Gender>
<MaritalStatus>Widowed</MaritalStatus>
<DeceasedDate/>
<USCitizenshipInd>false</USCitizenshipInd>
</PersonIdentityInfo>
<ContactInfo>
<AddressType>
<AddressRow>
<AddressCategoryType>Home</AddressCategoryType>
<AddressLine1>401 Lilac St</AddressLine1>
<AddressLine2/>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39401</ZipCode>
<ZipCodeExt>5501</ZipCodeExt>
<County>Lamar</County>
</AddressRow>
</AddressType>
<PhoneType>
<PhoneRow>
<PhoneCategoryType>Home</PhoneCategoryType>
<Phone/>
<PhoneExt/>
</PhoneRow>
</PhoneType>
<EmailID/>
</ContactInfo>
<EligibilityInfo>
<EligibilityDetailRow>
<ProgramType>Medicaid</ProgramType>
<EligibilityBeginDate>2021-11-01</EligibilityBeginDate>
<CategoryOfEligibilityType>051</CategoryOfEligibilityType>
<DecisionStatus>Approved</DecisionStatus>
<DecisionReason/>
</EligibilityDetailRow>
</EligibilityInfo>
<IncarcerationInfo>
<IncarcerationRow/>
</IncarcerationInfo>
<ResourceInfo>
<ResourceRow>
<ResourceCategory>Liquid</ResourceCategory>
<ResourceDetailCategoryType>Checking Account</ResourceDetailCategoryType>
<ResourceAmount>52</ResourceAmount>
<ResourceOwnershipType>N</ResourceOwnershipType>
<InstitutionName>BancorpSouth Bank</InstitutionName>
</ResourceRow>
<ResourceRow>
<ResourceCategory>Liquid</ResourceCategory>
<ResourceDetailCategoryType>Checking Account</ResourceDetailCategoryType>
<ResourceAmount>906.85</ResourceAmount>
<ResourceOwnershipType>N</ResourceOwnershipType>
<InstitutionName>Regions Bank</InstitutionName>
</ResourceRow>
</ResourceInfo>
<FamilyCompositionInfo>
<RelationshipToHOH/>
</FamilyCompositionInfo>
</PersonRow>
<PersonRow>
<ProgramType>Medicaid</ProgramType>
<KeyInfo>
<KeyTypeRow>
<KeyType>MedicaidID</KeyType>
<KeyValue>301869568</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>PersonID</KeyType>
<KeyValue>236</KeyValue>
</KeyTypeRow>
</KeyInfo>
<PersonIdentityInfo>
<NameInfo>
<FirstName>Annie</FirstName>
<MiddleName>B</MiddleName>
<LastName>Jaynes</LastName>
</NameInfo>
<DateOfBirth>1947-09-15</DateOfBirth>
<SSN>428985760</SSN>
<Gender>Female</Gender>
<MaritalStatus>Widowed</MaritalStatus>
<DeceasedDate/>
<USCitizenshipInd>false</USCitizenshipInd>
</PersonIdentityInfo>
<ContactInfo>
<AddressType>
<AddressRow>
<AddressCategoryType>Home</AddressCategoryType>
<AddressLine1>401 Lilac St</AddressLine1>
<AddressLine2/>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39401</ZipCode>
<ZipCodeExt>5501</ZipCodeExt>
<County>Lamar</County>
</AddressRow>
</AddressType>
<PhoneType>
<PhoneRow>
<PhoneCategoryType>Home</PhoneCategoryType>
<Phone/>
<PhoneExt/>
</PhoneRow>
</PhoneType>
<EmailID/>
</ContactInfo>
<EligibilityInfo>
<EligibilityDetailRow>
<ProgramType>Medicaid</ProgramType>
<EligibilityBeginDate>2021-11-01</EligibilityBeginDate>
<CategoryOfEligibilityType>051</CategoryOfEligibilityType>
<DecisionStatus>Approved</DecisionStatus>
<DecisionReason/>
</EligibilityDetailRow>
</EligibilityInfo>
<IncarcerationInfo>
<IncarcerationRow/>
</IncarcerationInfo>
<ResourceInfo>
<ResourceRow>
<ResourceCategory>Liquid</ResourceCategory>
<ResourceDetailCategoryType>Checking Account</ResourceDetailCategoryType>
<ResourceAmount>52</ResourceAmount>
<ResourceOwnershipType>N</ResourceOwnershipType>
<InstitutionName>BancorpSouth Bank</InstitutionName>
</ResourceRow>
<ResourceRow>
<ResourceCategory>Liquid</ResourceCategory>
<ResourceDetailCategoryType>Checking Account</ResourceDetailCategoryType>
<ResourceAmount>906.85</ResourceAmount>
<ResourceOwnershipType>N</ResourceOwnershipType>
<InstitutionName>Regions Bank</InstitutionName>
</ResourceRow>
</ResourceInfo>
<FamilyCompositionInfo>
<RelationshipToHOH/>
</FamilyCompositionInfo>
</PersonRow>
</PersonInfo>
</CaseTypeRow>
</ArrayOfCaseTypeRow>
</eligibilityChangeRequestType>
SQL> |
|
|
|
|
|
| Re: XML help needed [message #685764 is a reply to message #685763] |
Fri, 18 March 2022 11:15 |
Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
I am not sure what you mean. PersonIdentityInfo is part of PersonRow. Household in your test case caonsists of 3 people so each will have PersonIdentityInfo section.
SY.
|
|
|
|
|
|
| Re: XML help needed [message #685767 is a reply to message #685765] |
Fri, 18 March 2022 12:22 |
Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Ah, a & b join is incomplete. It should be joined by both household is and person id:
select XMLSerialize(
DOCUMENT
XMLElement(
"eligibilityChangeRequestType",
xmlattributes('http://softheon.com/2021/02/EligibilityChanges' as "xmlns"),
XMLElement("ArrayOfCaseTypeRow",
XMLAgg(
XMLElement("CaseTypeRow",
XMLElement("ProgramType",'Medicaid'),
XMLElement("ActionType",'InitialLoad'),
XMLElement("KeyInfo",
XMLElement("KeyTypeRow",
XMLElement("KeyType",'CaseID'),
XMLElement("KeyValue",Casenumber)
),
XMLElement("KeyTypeRow",
XMLElement("KeyType",'HouseholdID'),
XMLElement("KeyValue",b.Household_ID)
)
),
XMLElement("OfficeInfo",
XMLElement("AddressRow",
XMLElement("AddressLine1",REGIONALOFFICESTREETADDRESS1),
XMLElement("City",REGIONALOFFICECITY),
XMLElement("State",REGIONALOFFICESTATE),
XMLElement("Zipcode",REGIONALOFFICEZIP)
),
XMLElement("Phone",Regiionalofficephonenumber),
XMLElement("FaxNumber",Regiionalofficefaxnumber)
),
XMLElement("CaseWorkerInfo",
XMLElement("CaseworkerID",CaseworkerID),
XMLElement("EmailID",CaseworkeremailID),
XMLElement("FirstName",CaseworkerFirstName),
XMLElement("LastName",CaseworkerLastName)
),
XMLElement("PersonInfo",
XMLAgg(
XMLElement("PersonRow",
XMLElement("ProgramType",'Medicaid'),
XMLElement("KeyInfo",
XMLElement("KeyTypeRow",
XMLElement("KeyType",'MedicaidID'),
XMLElement("KeyValue",Medicaidid)
),
XMLElement("KeyTypeRow",
XMLElement("KeyType",'PersonID'),
XMLElement("KeyValue",PersonID)
)
),
XMLElement("PersonIdentityInfo",
XMLElement("NameInfo",
XMLElement("FirstName",First_Name),
XMLElement("MiddleName",Middle_Initial),
XMLElement("LastName",Last_Name)
),
XMLElement("DateOfBirth",DOB),
XMLElement("SSN",SSN),
XMLElement("Gender",Genderdesc),
XMLElement("MaritalStatus",Maritalstatusdesc),
XMLElement("DeceasedDate",DeathDate),
XMLElement("USCitizenshipInd",USCitizenshipInd)
),
XMLElement("ContactInfo",
XMLElement("AddressType",
XMLElement("AddressRow",
XMLElement("AddressCategoryType",'Home'),
XMLElement("AddressLine1",Address_Line1),
XMLElement("AddressLine2",Address_Line2),
XMLElement("City",City),
XMLElement("State",State),
XMLElement("ZipCode",Zipcode_desc),
XMLElement("ZipCodeExt",Zipcode_ext),
XMLElement("County",County)
)
),
XMLElement("PhoneType",
XMLElement("PhoneRow",
XMLElement("PhoneCategoryType",'Home'),
XMLElement("Phone",homephonenumber),
XMLElement("PhoneExt",homephoneextension)
)
),
XMLElement("EmailID",householdemailaddress)
),
XMLElement("EligibilityInfo",
XMLElement("EligibilityDetailRow",
XMLElement("ProgramType",'Medicaid'),
XMLElement("EligibilityBeginDate",ELIGIBILITY_STATUS_BEGIN_DATE),
XMLElement("CategoryOfEligibilityType",CATEGORYOFELIGIBILITYTYPE),
XMLElement("DecisionStatus",DECISIONSTATUSCODEDESC),
XMLElement("DecisionReason",homephonenumber)
)
),
XMLElement("IncarcerationInfo",
XMLElement("IncarcerationRow",'')
),
XMLElement("ResourceInfo",
(select XMLAgg(
XMLElement("ResourceRow",
XMLElement("ResourceCategory",ResourceCategoryType),
XMLElement("ResourceDetailCategoryType",ResourceDetailCategoryType),
XMLElement("ResourceAmount",ResourceAmount),
XMLElement("ResourceOwnershipType",ResourceOwnershipType),
XMLElement("InstitutionName",institution_name)
)
)
from c tcr
where tcr.consumer_id = b.consumer_id
)
),
XMLElement("FamilyCompositionInfo",
XMLElement("RelationshipToHOH",'')
)
)
)
)
)
)
)
)
indent size = 2
) xml
from a,
b
where a.household_id = b.household_id
and a.person_id = b.personid
group by b.household_id,
b.Casenumber,
b.REGIONALOFFICESTREETADDRESS1,
b.REGIONALOFFICECITY,
b.REGIONALOFFICESTATE,
b.REGIONALOFFICEZIP,
b.Regiionalofficephonenumber,
b.Regiionalofficefaxnumber,
b.CaseworkerID,
b.CaseworkeremailID,
b.CaseworkerFirstName,
b.CaseworkerLastName
/
XML
------------------------------------------------------------------------------------------------------------------------------------
<eligibilityChangeRequestType xmlns="http://softheon.com/2021/02/EligibilityChanges">
<ArrayOfCaseTypeRow>
<CaseTypeRow>
<ProgramType>Medicaid</ProgramType>
<ActionType>InitialLoad</ActionType>
<KeyInfo>
<KeyTypeRow>
<KeyType>CaseID</KeyType>
<KeyValue>101569748</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>HouseholdID</KeyType>
<KeyValue>95</KeyValue>
</KeyTypeRow>
</KeyInfo>
<OfficeInfo>
<AddressRow>
<AddressLine1>6971 Lincoln Rd Ext</AddressLine1>
<City>Hattiesburg</City>
<State>MS</State>
<Zipcode>39402</Zipcode>
</AddressRow>
<Phone>6012645386</Phone>
<FaxNumber>6012611244</FaxNumber>
</OfficeInfo>
<CaseWorkerInfo>
<CaseworkerID>122710</CaseworkerID>
<EmailID>Ontario.Lewis@medicaid.ms.gov</EmailID>
<FirstName>Ontario</FirstName>
<LastName>Lewis</LastName>
</CaseWorkerInfo>
<PersonInfo>
<PersonRow>
<ProgramType>Medicaid</ProgramType>
<KeyInfo>
<KeyTypeRow>
<KeyType>MedicaidID</KeyType>
<KeyValue>301869568</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>PersonID</KeyType>
<KeyValue>236</KeyValue>
</KeyTypeRow>
</KeyInfo>
<PersonIdentityInfo>
<NameInfo>
<FirstName>Annie</FirstName>
<MiddleName>B</MiddleName>
<LastName>Jaynes</LastName>
</NameInfo>
<DateOfBirth>1947-09-15</DateOfBirth>
<SSN>428985760</SSN>
<Gender>Female</Gender>
<MaritalStatus>Widowed</MaritalStatus>
<DeceasedDate/>
<USCitizenshipInd>false</USCitizenshipInd>
</PersonIdentityInfo>
<ContactInfo>
<AddressType>
<AddressRow>
<AddressCategoryType>Home</AddressCategoryType>
<AddressLine1>401 Lilac St</AddressLine1>
<AddressLine2/>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39401</ZipCode>
<ZipCodeExt>5501</ZipCodeExt>
<County>Lamar</County>
</AddressRow>
</AddressType>
<PhoneType>
<PhoneRow>
<PhoneCategoryType>Home</PhoneCategoryType>
<Phone/>
<PhoneExt/>
</PhoneRow>
</PhoneType>
<EmailID/>
</ContactInfo>
<EligibilityInfo>
<EligibilityDetailRow>
<ProgramType>Medicaid</ProgramType>
<EligibilityBeginDate>2021-11-01</EligibilityBeginDate>
<CategoryOfEligibilityType>051</CategoryOfEligibilityType>
<DecisionStatus>Approved</DecisionStatus>
<DecisionReason/>
</EligibilityDetailRow>
</EligibilityInfo>
<IncarcerationInfo>
<IncarcerationRow/>
</IncarcerationInfo>
<ResourceInfo>
<ResourceRow>
<ResourceCategory>Liquid</ResourceCategory>
<ResourceDetailCategoryType>Checking Account</ResourceDetailCategoryType>
<ResourceAmount>52</ResourceAmount>
<ResourceOwnershipType>N</ResourceOwnershipType>
<InstitutionName>BancorpSouth Bank</InstitutionName>
</ResourceRow>
<ResourceRow>
<ResourceCategory>Liquid</ResourceCategory>
<ResourceDetailCategoryType>Checking Account</ResourceDetailCategoryType>
<ResourceAmount>906.85</ResourceAmount>
<ResourceOwnershipType>N</ResourceOwnershipType>
<InstitutionName>Regions Bank</InstitutionName>
</ResourceRow>
</ResourceInfo>
<FamilyCompositionInfo>
<RelationshipToHOH/>
</FamilyCompositionInfo>
</PersonRow>
<PersonRow>
<ProgramType>Medicaid</ProgramType>
<KeyInfo>
<KeyTypeRow>
<KeyType>MedicaidID</KeyType>
<KeyValue>385492804</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>PersonID</KeyType>
<KeyValue>268</KeyValue>
</KeyTypeRow>
</KeyInfo>
<PersonIdentityInfo>
<NameInfo>
<FirstName>Kardeem</FirstName>
<MiddleName>D</MiddleName>
<LastName>Walker</LastName>
</NameInfo>
<DateOfBirth>2002-12-17</DateOfBirth>
<SSN>426936260</SSN>
<Gender>Male</Gender>
<MaritalStatus>Single</MaritalStatus>
<DeceasedDate/>
<USCitizenshipInd>false</USCitizenshipInd>
</PersonIdentityInfo>
<ContactInfo>
<AddressType>
<AddressRow>
<AddressCategoryType>Home</AddressCategoryType>
<AddressLine1>401 Lilac St</AddressLine1>
<AddressLine2/>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39401</ZipCode>
<ZipCodeExt>5501</ZipCodeExt>
<County>Lamar</County>
</AddressRow>
</AddressType>
<PhoneType>
<PhoneRow>
<PhoneCategoryType>Home</PhoneCategoryType>
<Phone/>
<PhoneExt/>
</PhoneRow>
</PhoneType>
<EmailID/>
</ContactInfo>
<EligibilityInfo>
<EligibilityDetailRow>
<ProgramType>Medicaid</ProgramType>
<EligibilityBeginDate>2022-01-01</EligibilityBeginDate>
<CategoryOfEligibilityType>073</CategoryOfEligibilityType>
<DecisionStatus>Approved</DecisionStatus>
<DecisionReason/>
</EligibilityDetailRow>
</EligibilityInfo>
<IncarcerationInfo>
<IncarcerationRow/>
</IncarcerationInfo>
<ResourceInfo>
<ResourceRow>
<ResourceCategory/>
<ResourceDetailCategoryType/>
<ResourceAmount/>
<ResourceOwnershipType/>
<InstitutionName/>
</ResourceRow>
</ResourceInfo>
<FamilyCompositionInfo>
<RelationshipToHOH/>
</FamilyCompositionInfo>
</PersonRow>
<PersonRow>
<ProgramType>Medicaid</ProgramType>
<KeyInfo>
<KeyTypeRow>
<KeyType>MedicaidID</KeyType>
<KeyValue>385792441</KeyValue>
</KeyTypeRow>
<KeyTypeRow>
<KeyType>PersonID</KeyType>
<KeyValue>281</KeyValue>
</KeyTypeRow>
</KeyInfo>
<PersonIdentityInfo>
<NameInfo>
<FirstName>Michael</FirstName>
<MiddleName>J</MiddleName>
<LastName>Hubbard</LastName>
</NameInfo>
<DateOfBirth>2003-10-10</DateOfBirth>
<SSN>425952940</SSN>
<Gender>Male</Gender>
<MaritalStatus>Single</MaritalStatus>
<DeceasedDate/>
<USCitizenshipInd>false</USCitizenshipInd>
</PersonIdentityInfo>
<ContactInfo>
<AddressType>
<AddressRow>
<AddressCategoryType>Home</AddressCategoryType>
<AddressLine1>401 Lilac St</AddressLine1>
<AddressLine2/>
<City>Hattiesburg</City>
<State>MS</State>
<ZipCode>39401</ZipCode>
<ZipCodeExt>5501</ZipCodeExt>
<County>Lamar</County>
</AddressRow>
</AddressType>
<PhoneType>
<PhoneRow>
<PhoneCategoryType>Home</PhoneCategoryType>
<Phone/>
<PhoneExt/>
</PhoneRow>
</PhoneType>
<EmailID/>
</ContactInfo>
<EligibilityInfo>
<EligibilityDetailRow>
<ProgramType>Medicaid</ProgramType>
<EligibilityBeginDate>2021-07-01</EligibilityBeginDate>
<CategoryOfEligibilityType>073</CategoryOfEligibilityType>
<DecisionStatus>Approved</DecisionStatus>
<DecisionReason/>
</EligibilityDetailRow>
</EligibilityInfo>
<IncarcerationInfo>
<IncarcerationRow/>
</IncarcerationInfo>
<ResourceInfo>
<ResourceRow>
<ResourceCategory/>
<ResourceDetailCategoryType/>
<ResourceAmount/>
<ResourceOwnershipType/>
<InstitutionName/>
</ResourceRow>
</ResourceInfo>
<FamilyCompositionInfo>
<RelationshipToHOH/>
</FamilyCompositionInfo>
</PersonRow>
</PersonInfo>
</CaseTypeRow>
</ArrayOfCaseTypeRow>
</eligibilityChangeRequestType>
SQL>
|
|
|
|
|
|
|
|
| Re: XML help needed [message #685772 is a reply to message #685771] |
Mon, 21 March 2022 15:45 |
Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Well, you need to find out why is it running out of temp. Is there ORDER BY? It could be that sort is too big not just for in-memory sort but even for disk sort where it runs out of temp space.
Also check exeution plan. It could be doing hash joins. Then try something like USE_NL hint to force nested loops. Or simply add more temp space...
In any case this isn't SQL itself but rather your database settings/temp size issue.
SY.
|
|
|
|
| Re: XML help needed [message #685824 is a reply to message #685767] |
Sat, 02 April 2022 08:50 |
rajivn786
Messages: 159
Registered: January 2010
|
Senior Member |
|
|
SY,
How to preserve the order of person_id ? Is it possible to sort by column while generating XML Since there are multiple person they want in ascending order. I did
create table in the order of person id but that wasnt working.
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Mar 27 18:51:47 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
