extraction from xml value

[chinmay7]

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>

[petter]

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;

[chinmay7]

how put invoicexml field from invoice qvx inside inline load?