6 Replies Latest reply: May 11, 2012 1:58 AM by 936056 RSS

    Xml parsing issue

    936056
      Hi Friends,

      I have a question as like below.

      We have the contacts and alternate contacts request as like below.

      <TXLife>

      <UserAuthRequest>

      <UserLoginName>hqtest24</UserLoginName>

      <UserDate>2012-04-20</UserDate>

      <UserTime>14:22:00.000</UserTime>

      </UserAuthRequest>

      <TXLifeRequest>

      <TransRefGUID>0ab5fbde-a454-49c9-8650-c16a6c978e3c</TransRefGUID>

      <TransType tc="1203">OLI_TRANS_TRNHLD</TransType>

      <TransExeDate>2012-04-20</TransExeDate>

      <TransExeTime>14:21:59.000</TransExeTime>

      <InquiryLevel tc="3">OLI_INQUIRY_OBJRELOBJ</InquiryLevel>

      <InquiryView>

      <InquiryViewCode>PLANUPDATE_CHBM_1203A</InquiryViewCode>

      </InquiryView>

      <OLifE>

      <SourceInfo>

      <SourceInfoName>CHBM Admin Tool</SourceInfoName>

      </SourceInfo>

      <Holding id="Holding1">

      <HoldingTypeCode tc="6">GroupMaster</HoldingTypeCode>

      <HoldingStatus tc="23">INCOMPLETE</HoldingStatus>

      <Policy id="Policy1">

      <CarrierAdminSystem>CHBM</CarrierAdminSystem>

      <PolNumber>CHB0001032</PolNumber>

      </Policy>

      <Attachment id="Attachment1">

      <DateCreated>2012-04-20</DateCreated>

      <UserCode>hqtest25</UserCode>

      <AttachmentBasicType tc="1">Text</AttachmentBasicType>

      <Description>Create BusinessClient</Description>

      <AttachmentType tc="1009800001">Transaction Log</AttachmentType>

      <AttachmentLocation tc="1">Inline</AttachmentLocation>

      <OLifEExtension VendorCode="0098" ExtensionCode="Attachment">

      <AttachmentExtension>

      <CreationTime>14:21:58.000</CreationTime>

      <Sequence>1</Sequence>

      </AttachmentExtension>

      </OLifEExtension>

      </Attachment>

      </Holding>

      <Activity id="Activity1" HoldingID="Holding1">

      <UserCode>hqtest24</UserCode>

      <Opened>2012-04-20</Opened>

      <ActivityCode>CHBM10001</ActivityCode>

      <OLifEExtension VendorCode="0098" ExtensionCode="Activity">

      <ActivityExtension>

      <SubActivityCode>CHBM20001</SubActivityCode>

      <OpenedTime>14:22:00.788</OpenedTime>

      </ActivityExtension>

      </OLifEExtension>

      </Activity>

      <Party id="Client1">

      <PartyTypeCode tc="2">OLI_PT_ORG</PartyTypeCode>

      <FullName>Bradrrrr JK Corp</FullName>

      <Organization>

      <DBA>Bradrrrr JK Corp</DBA>

      <OLifEExtension VendorCode="0098">

      <OrganizationExtension>

      <BusinessDesc>Do Nothing</BusinessDesc>

      </OrganizationExtension>

      </OLifEExtension>

      </Organization>

      <Address>

      <AddressTypeCode tc="2">OLI_ADTYPE_BUS</AddressTypeCode>

      <Line1>Line 1</Line1>

      <Line2>Line 2</Line2>

      <Line3>Line 3</Line3>

      <City>New York</City>

      <AddressState>NY</AddressState>

      <Zip>10001</Zip>

      <AddressCountry>US</AddressCountry>

      </Address>

      <Phone>

      <PhoneTypeCode tc="2">OLI_PHONETYPE_BUS</PhoneTypeCode>

      <DialNumber>646-646-6666</DialNumber>

      </Phone>

      <Client>

      <ClientTypeCode tc="1">OLI_CLITYPE_CLIENT</ClientTypeCode>

      <ClientStatus tc="2">INACTIVE</ClientStatus>

      </Client>

      <OLifEExtension VendorCode="0098" ExtensionCode="Party">

      <PartyExtension>

      <FiscalYearEndMoDay>2012-12-30</FiscalYearEndMoDay>

      </PartyExtension>

      </OLifEExtension>

      </Party>

      <Party id="Contact1">

      <PartyTypeCode tc="1">OLI_PT_PERSON</PartyTypeCode>

      <Person>

      <FirstName>Bradiiii</FirstName>

      <LastName>Joneiii</LastName>

      </Person>

      <Phone>

      <PhoneTypeCode tc="2">OLI_PHONETYPE_BUS</PhoneTypeCode>

      <DialNumber>212-999-4567</DialNumber>

      </Phone>

      <Phone>

      <PhoneTypeCode tc="19">OLI_PHONETYPE_FAX</PhoneTypeCode>

      <DialNumber>212-555-3333</DialNumber>

      </Phone>

      <EMailAddress>

      <EMailType tc="1">Business</EMailType>

      <AddrLine>Tomy@smith.com</AddrLine>

      </EMailAddress>

      </Party>

      <Party id="AlternateContact1">

      <PartyTypeCode tc="1">OLI_PT_PERSON</PartyTypeCode>

      <Person>

      <FirstName>Bradiiii</FirstName>

      <LastName>Joneiii</LastName>

      </Person>

      <Phone>

      <PhoneTypeCode tc="2">OLI_PHONETYPE_BUS</PhoneTypeCode>

      <DialNumber>212-999-4567</DialNumber>

      </Phone>

      <Phone>

      <PhoneTypeCode tc="19">OLI_PHONETYPE_FAX</PhoneTypeCode>

      <DialNumber>212-555-3333</DialNumber>

      </Phone>

      <EMailAddress>

      <EMailType tc="1">Business</EMailType>

      <AddrLine>Tomy@smith.com</AddrLine>

      </EMailAddress>

      </Party>

      <Relation id="Relation1" OriginatingObjectID="Client1" RelatedObjectID="Contact1">

      <OriginatingObjectType tc="6">Party</OriginatingObjectType>

      <RelatedObjectType tc="6">Party</RelatedObjectType>

      <RelationRoleCode tc="150">Authorized Person</RelationRoleCode>

      <InterestPercent>-1</InterestPercent>

      </Relation>

      <Relation id="Relation2" OriginatingObjectID="Holding1" RelatedObjectID="Contact1">

      <OriginatingObjectType tc="4">Holding</OriginatingObjectType>

      <RelatedObjectType tc="6">Party</RelatedObjectType>

      <RelationRoleCode tc="150">Authorized Person</RelationRoleCode>

      </Relation>

      <Relation id="Relation3" OriginatingObjectID="Holding1" RelatedObjectID="Client1">

      <OriginatingObjectType tc="4">Holding</OriginatingObjectType>

      <RelatedObjectType tc="6">Party</RelatedObjectType>

      <RelationRoleCode tc="54">Plan Sponsor</RelationRoleCode>

      </Relation>

      <Relation id="Relation4" OriginatingObjectID="Client1" RelatedObjectID="AlternateContact1">

      <OriginatingObjectType tc="6">Party</OriginatingObjectType>

      <RelatedObjectType tc="6">Party</RelatedObjectType>

      <RelationRoleCode tc="150">Authorized Person</RelationRoleCode>

      <InterestPercent>-1</InterestPercent>

      <Sequence>2</Sequence>

      </Relation>

      <Relation id="Relation5" OriginatingObjectID="Holding1" RelatedObjectID="AlternateContact1">

      <OriginatingObjectType tc="4">Holding</OriginatingObjectType>

      <RelatedObjectType tc="6">Party</RelatedObjectType>

      <RelationRoleCode tc="150">Authorized Person</RelationRoleCode>

      <Sequence>2</Sequence>

      </Relation>

      </OLifE>

      </TXLifeRequest>

      </TXLife>



      From the above request we have to parse the contact and alternate contact details.

      In the procedure we are using as like below

      SELECT t.vCntFirstName,
      t.vCntLastName,
      t.vAltCntFirstName,
      t.vAltCntLastName,
      INTO vorgcity,
      vCntFirstName,
      vCntLastName,
      vAltCntFirstName,
      vAltCntLastName,
      FROM (SELECT xData doc
      FROM dual
      ) temp_table,
      XMLTable ( '/TXLife/TXLifeRequest/OLifE' passing doc
      COLUMNS
      vCntFirstName VARCHAR2(100) path 'Party[@id=/OLifE/Relation[RelationRoleCode/@tc=150]/@RelatedObjectID]/Person/FirstName',
      vCntLastName VARCHAR2(100) path 'Party[@id=/OLifE/Relation[RelationRoleCode/@tc=150]/@RelatedObjectID]/Person/LastName',
      vAltCntFirstName VARCHAR2(100) path 'Party[@id=/OLifE/Relation[RelationRoleCode/@tc=150][Sequence=2]/@RelatedObjectID]/Person/FirstName',
      vAltCntLastName VARCHAR2(100) path 'Party[@id=/OLifE/Relation[RelationRoleCode/@tc=150][Sequence=2]/@RelatedObjectID]/Person/LastName',
      ) t;

      Here while parsing the data for contacts its going to the exception because more than one record fetched.

      To differentiate the alternate contacts we have sequence tag is there in the request.

      If we want to differentiate the contacts, we have to keep the sequence tag for contacts relation also.

      I tried to use like below but at that time that vCntFirstName value is returning as NULL.

      vCntFirstName VARCHAR2(100) path 'Party[@id=/OLifE/Relation[RelationRoleCode/@tc=150][Sequence=NULL]/@RelatedObjectID]/Person/FirstName'

      vCntFirstName VARCHAR2(100) path 'Party[@id=/OLifE/Relation[RelationRoleCode/@tc=150][Sequence!=2]/@RelatedObjectID]/Person/FirstName'


      But with out using the sequence tag in the relation for contacts, is there any way to to parse the data from contacts.


      Regards,
      Jyothirmai
        • 1. Re: Xml parsing issue
          odie_63
          Hi,

          Sorry, it's not clear what output you require exactly.

          Do you want something like this :
          SQL> select x2.*, x3.*
            2  from xmltable(
            3         '/TXLife/TXLifeRequest/OLifE'
            4         passing xmltype(bfilename('TEST_DIR','txlife.xml'),nls_charset_id('AL32UTF8'))
            5         columns
            6           relations       xmltype       path 'Relation[RelationRoleCode/@tc=150]'
            7         , parties         xmltype       path 'Party'
            8       ) x1
            9     , xmltable(
           10         '/Relation'
           11         passing x1.relations
           12         columns id              varchar2(30) path '@id'
           13               , RelatedObjectID varchar2(30) path '@RelatedObjectID'
           14       ) x2
           15     , xmltable(
           16         '/Party[@id=$ROID]'
           17         passing x1.parties
           18               , x2.RelatedObjectID as "ROID"
           19         columns FirstName varchar2(30) path 'Person/FirstName'
           20               , LastName  varchar2(30) path 'Person/LastName'
           21       ) x3
           22  ;
           
          ID                             RELATEDOBJECTID                FIRSTNAME                      LASTNAME
          ------------------------------ ------------------------------ ------------------------------ ------------------------------
          Relation1                      Contact1                       Bradiiii                       Joneiii
          Relation2                      Contact1                       Bradiiii                       Joneiii
          Relation4                      AlternateContact1              Bradiiii                       Joneiii
          Relation5                      AlternateContact1              Bradiiii                       Joneiii
           
          • 2. Re: Xml parsing issue
            936056
            Hi Odie,

            I need the output as like below

            CntFirstName CntLastName AltCntFirstName AltCntLastName
            ------------------ ------------------- ------------------------ ----------------------
            Bradiiii Joneiii Bradiiii Joneiii


            I have to retrieve all the details of the party(that means firstname,lastname,dialnumber,addrline) which is under the contact by comparing the party id with the RelatedObjectID whose RelationRoleCode is 150.


            <Party id="Contact1">

            <PartyTypeCode tc="1">OLI_PT_PERSON</PartyTypeCode>

            <Person>

            <FirstName>Bradiiii</FirstName>

            <LastName>Joneiii</LastName>

            </Person>

            <Phone>

            <PhoneTypeCode tc="2">OLI_PHONETYPE_BUS</PhoneTypeCode>

            <DialNumber>212-999-4567</DialNumber>

            </Phone>

            <Phone>

            <PhoneTypeCode tc="19">OLI_PHONETYPE_FAX</PhoneTypeCode>

            <DialNumber>212-555-3333</DialNumber>

            </Phone>

            <EMailAddress>

            <EMailType tc="1">Business</EMailType>

            <AddrLine>Tomy@smith.com</AddrLine>

            </EMailAddress>

            </Party>

            While comparing almost there is same criteria in the contact and alternate contact relations,that's why its retrieving multiple records.

            In the request for relation if we give as like below its giving proper results.



            <Relation id="Relation1" OriginatingObjectID="Client1" RelatedObjectID="Contact1">

            <OriginatingObjectType tc="6">Party</OriginatingObjectType>

            <RelatedObjectType tc="6">Party</RelatedObjectType>

            <RelationRoleCode tc="150">Authorized Person</RelationRoleCode>

            <InterestPercent>-1</InterestPercent>

            <Sequence>1</Sequence>

            </Relation>

            <Relation id="Relation2" OriginatingObjectID="Holding1" RelatedObjectID="Contact1">

            <OriginatingObjectType tc="4">Holding</OriginatingObjectType>

            <RelatedObjectType tc="6">Party</RelatedObjectType>

            <RelationRoleCode tc="150">Authorized Person</RelationRoleCode>

            <Sequence>1</Sequence>

            </Relation>

            <Relation id="Relation4" OriginatingObjectID="Client1" RelatedObjectID="AlternateContact1">

            <OriginatingObjectType tc="6">Party</OriginatingObjectType>

            <RelatedObjectType tc="6">Party</RelatedObjectType>

            <RelationRoleCode tc="150">Authorized Person</RelationRoleCode>

            <InterestPercent>-1</InterestPercent>

            <Sequence>2</Sequence>

            </Relation>

            <Relation id="Relation5" OriginatingObjectID="Holding1" RelatedObjectID="AlternateContact1">

            <OriginatingObjectType tc="4">Holding</OriginatingObjectType>

            <RelatedObjectType tc="6">Party</RelatedObjectType>

            <RelationRoleCode tc="150">Authorized Person</RelationRoleCode>

            <Sequence>2</Sequence>

            </Relation>


            But with out using <Sequence>1</Sequence> tag in the contact,is there any way to retrieve the contact details.


            Regards,
            Jyothirmai k
            • 3. Re: Xml parsing issue
              odie_63
              Is there any rule that says whether a relation represents a contact or an alternate contact (besides giving them a distinct sequence number) ?
              • 4. Re: Xml parsing issue
                936056
                Hi Odie,

                The relationrolecode 150 represents the primary Contacts/Alternate Contacts,in that the sequence 2 represents the alternate contacts.

                That's why we have given the sequence tag in the relation for alternate contacts.

                But for primary contact there is no sequence tag in the relation.

                To retrieve the alternate contact details we have used as like below.


                SELECT t.vAltCntFirstName,
                t.vAltCntLastName
                INTO
                vAltCntFirstName,
                vAltCntLastName
                FROM (SELECT xData doc
                FROM dual
                ) temp_table,
                XMLTable ( '/TXLife/TXLifeRequest/OLifE' passing doc
                COLUMNS
                vAltCntFirstName VARCHAR2(100) path 'Party[@id=/OLifE/Relation[RelationRoleCode/@tc=150][Sequence=2]/@RelatedObjectID]/Person/FirstName',
                vAltCntLastName VARCHAR2(100) path 'Party[@id=/OLifE/Relation[RelationRoleCode/@tc=150][Sequence=2]/@RelatedObjectID]/Person/LastName'
                ) t;


                But for the primary contacts we tried to use like below,but its returning contact firstname,and lastname values as null.

                SELECT t.vCntFirstName,
                t.vCntLastName
                INTO
                vCntFirstName,
                vCntLastName
                FROM (SELECT xData doc
                FROM dual
                ) temp_table,
                XMLTable ( '/TXLife/TXLifeRequest/OLifE' passing doc
                COLUMNS
                vCntFirstName VARCHAR2(100) path 'Party[@id=/OLifE/Relation[RelationRoleCode/@tc=150][Sequence=NULL]/@RelatedObjectID]/Person/FirstName',
                vCntLastName VARCHAR2(100) path 'Party[@id=/OLifE/Relation[RelationRoleCode/@tc=150][Sequence=NULL]/@RelatedObjectID]/Person/LastName'
                ) t;


                Please let us know is there any way to retrieve the primary contact details with out having sequence tag in the relation for contact.

                Regards,
                Jyothirmai K
                • 5. Re: Xml parsing issue
                  odie_63
                  OK, thanks for explaining again.

                  You can test if a node exists with the not() operator, for example :
                  SQL> SELECT t.*
                    2  FROM XMLTable (
                    3         '/TXLife/TXLifeRequest/OLifE'
                    4         passing xmltype(bfilename('TEST_DIR','txlife.xml'),nls_charset_id('AL32UTF8'))
                    5         COLUMNS
                    6           CntFirstName    VARCHAR2(30) path 'Party[@id=/OLifE/Relation[RelationRoleCode/@tc=150][not(Sequence)]/@RelatedObjectID]/Person/FirstName'
                    7         , CntLastName     VARCHAR2(30) path 'Party[@id=/OLifE/Relation[RelationRoleCode/@tc=150][not(Sequence)]/@RelatedObjectID]/Person/LastName'
                    8         , AltCntFirstName VARCHAR2(30) path 'Party[@id=/OLifE/Relation[RelationRoleCode/@tc=150][Sequence="2"]/@RelatedObjectID]/Person/FirstName'
                    9         , AltCntLastName  VARCHAR2(30) path 'Party[@id=/OLifE/Relation[RelationRoleCode/@tc=150][Sequence="2"]/@RelatedObjectID]/Person/LastName'
                   10       ) t
                   11  ;
                   
                  CNTFIRSTNAME                   CNTLASTNAME                    ALTCNTFIRSTNAME                ALTCNTLASTNAME
                  ------------------------------ ------------------------------ ------------------------------ ------------------------------
                  Bradiiii                       Joneiii                        Bradiiii                       Joneiii
                   
                  BTW, why are you using a SELECT FROM dual to pass the variable?
                  You can use the variable directly in the PASSING clause.
                  • 6. Re: Xml parsing issue
                    936056
                    Hi Odie,

                    I tried with the solution what you have provided.
                    Its working fine.

                    Thanks you very much for your help.

                    Regards,
                    Jyothirmai K