Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

chinmay7
New Contributor

extraction from xml value

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>

Tags (1)
2 Replies
MVP
MVP

Re: extraction from xml value

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
New Contributor

Re: extraction from xml value

how put invoicexml field from invoice qvx inside inline load?