2 Replies Latest reply: May 25, 2018 11:15 AM by Chinmay Limaye RSS

    extraction from xml value

    Chinmay Limaye

      Hi,

       

      I am having one field called invoicexml from invoice table.I want to extract party id from that field for role insured.

      Kindly help me to to do the same.

       

      please find below xml value also find attache screen shot

       

      <AssociatedParties xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

          <Party PartyId="3" Role="Agent" ObjectId="18" ObjectReference="NBSH002000018P 29/01/2018 - 29/01/2019" ObjectTypeCode="PTRM" id="P7E9858BCCFE74D1E8BCC1C2B5547DC4B" />

          <Party PartyId="67" Role="Insured" ObjectId="18" ObjectReference="NBSH002000018P 29/01/2018 - 29/01/2019" ObjectTypeCode="PTRM" id="PA009B73F6EED420B93AA8B891C443AD3" />

          <Party PartyId="67" Role="Insured" ObjectId="42" ObjectReference="NBSH002000018P" ObjectTypeCode="POL" id="PD1650420F55B4286BA9EB508A51DD57B" />

          <Party PartyId="67" Role="Payor" ObjectId="18" ObjectReference="NBSH002000018P" ObjectTypeCode="ACT" id="PA326C7A1ADC84446BD3A6AC43603FA21" />

          <Party PartyId="67" Role="Payee" ObjectId="18" ObjectReference="NBSH002000018P" ObjectTypeCode="ACT" id="PBB9D9A092C0C442DBD883AD88F3FC008" />

        </AssociatedParties>

        • Re: extraction from xml value
          Petter Skjolden

          You can use the LOAD ... FROM_FIELD to parse XML contained in a field:

           

          TABLE1:
          LOAD RowNo() AS R#,* INLINE [
          ID,XML
          A,<AssociatedParties><Party PartyId="3" Role="Agent" ObjectId="18" ObjectReference="NBSH002000018P 29/01/2018 - 29/01/2019" ObjectTypeCode="PTRM" id="P7E9858BCCFE74D1E8BCC1C2B5547DC4B" /><Party PartyId="67" Role="Insured" ObjectId="18" ObjectReference="NBSH002000018P 29/01/2018 - 29/01/2019" ObjectTypeCode="PTRM" id="PA009B73F6EED420B93AA8B891C443AD3" /><Party PartyId="67" Role="Insured" ObjectId="42" ObjectReference="NBSH002000018P" ObjectTypeCode="POL" id="PD1650420F55B4286BA9EB508A51DD57B" />    <Party PartyId="67" Role="Payor" ObjectId="18" ObjectReference="NBSH002000018P" ObjectTypeCode="ACT" id="PA326C7A1ADC84446BD3A6AC43603FA21" /><Party PartyId="67" Role="Payee" ObjectId="18" ObjectReference="NBSH002000018P" ObjectTypeCode="ACT" id="PBB9D9A092C0C442DBD883AD88F3FC008"/></AssociatedParties>
          B,<AssociatedParties><Party PartyId="2" Role="Agent" ObjectId="18" ObjectReference="NBSH002000018P 29/01/2018 - 29/01/2019" ObjectTypeCode="PTRM" id="P7E9858BCCFE74D1E8BCC1C2B5547DC4B" /><Party PartyId="77" Role="Insured" ObjectId="18" ObjectReference="NBSH002000018P 29/01/2018 - 29/01/2019" ObjectTypeCode="PTRM" id="PA009B73F6EED420B93AA8B891C443AD3" /><Party PartyId="77" Role="Insured" ObjectId="42" ObjectReference="NBSH002000018P" ObjectTypeCode="POL" id="PD1650420F55B4286BA9EB508A51DD57B" />    <Party PartyId="77" Role="Payor" ObjectId="18" ObjectReference="NBSH002000018P" ObjectTypeCode="ACT" id="PA326C7A1ADC84446BD3A6AC43603FA21" /><Party PartyId="77" Role="Payee" ObjectId="18" ObjectReference="NBSH002000018P" ObjectTypeCode="ACT" id="PBB9D9A092C0C442DBD883AD88F3FC008"/></AssociatedParties>
          ] (txt, no quotes) ;
          
          
          PARTYID:
          LOAD
            RowNo() AS R#,PartyId
          FROM_FIELD( [TABLE1] , [XML] ) (xmlsimple, table is [AssociatedParties/Party])
          WHERE
            Role='Insured' AND ObjectTypeCode='POL'
          ;
          
          
          LEFT JOIN (TABLE1) LOAD * RESIDENT PARTYID;
          
          
          DROP FIELD XML;
          DROP TABLE PARTYID;