Home » SQL & PL/SQL » SQL & PL/SQL » XML Generation (Oracle 11g, XP)
XML Generation [message #685757] Thu, 17 March 2022 20:58 Go to next message
rajiv.oradev
Messages: 23
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 1254 times)
XML help needed [message #685758 is a reply to message #685757] Thu, 17 March 2022 21:14 Go to previous messageGo to next message
rajiv.oradev
Messages: 23
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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>
SY.
Re: XML help needed [message #685761 is a reply to message #685760] Fri, 18 March 2022 07:09 Go to previous messageGo to next message
rajiv.oradev
Messages: 23
Registered: March 2022
Junior Member
SY, Actually tag household_id and casetype row tag should be there only once but here it's repeated 9 times...
Table A has 3 rows, B has 3 rows...Output should be aggregated without duplicates.

This is the output I am expecting
<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>
                 <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>
Re: XML help needed [message #685762 is a reply to message #685761] Fri, 18 March 2022 09:07 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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>
sy.
Re: XML help needed [message #685763 is a reply to message #685762] Fri, 18 March 2022 09:30 Go to previous messageGo to next message
rajiv.oradev
Messages: 23
Registered: March 2022
Junior Member
SY, I think we are close. Now PersonIdentityInfo is repeated 3 times instead of 1. That makes output tags repeated 3 times.
Are we missing aggregate in between thats making it repeated. Lets say name : "Kardeem" it got repeated 3 times...same for other 2 names.
Total output lines should be around 260, ours is like 750+

I appreciate so far what you have done.

Re: XML help needed [message #685764 is a reply to message #685763] Fri, 18 March 2022 11:15 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 #685765 is a reply to message #685764] Fri, 18 March 2022 11:21 Go to previous messageGo to next message
rajiv.oradev
Messages: 23
Registered: March 2022
Junior Member
SY, It consists of 3 people, but it's being repeated 3 times each for 3 members.
This is the block that's being repeated 3 times
 <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>
Re: XML help needed [message #685767 is a reply to message #685765] Fri, 18 March 2022 12:22 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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>
SY.
Re: XML help needed [message #685769 is a reply to message #685767] Fri, 18 March 2022 13:13 Go to previous messageGo to next message
rajiv.oradev
Messages: 23
Registered: March 2022
Junior Member
Thank you so much SY! I see that output now.
I will let u know if any issues but tough to understand XML...
Re: XML help needed [message #685771 is a reply to message #685769] Mon, 21 March 2022 10:55 Go to previous messageGo to next message
rajiv.oradev
Messages: 23
Registered: March 2022
Junior Member
SY, Actually it says : unable to extend temp segment by 64 when I try to run for bigger set. Ultimate goal is to transfer the output of this file into xml.
Re: XML help needed [message #685772 is a reply to message #685771] Mon, 21 March 2022 15:45 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 Go to previous messageGo to next message
rajivn786
Messages: 161
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.
Re: XML help needed [message #685826 is a reply to message #685824] Sat, 02 April 2022 14:21 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Do you RTFM: XMLAGG

XMLAGG(XMLType_instance [ order_by_clause ])
SY.

[Updated on: Sat, 02 April 2022 14:22]

Report message to a moderator

Previous Topic: Data not sorted
Next Topic: Find Difference between 2 columns along with difference data
Goto Forum:
  


Current Time: Fri Mar 29 08:59:54 CDT 2024