Retrieve a value from multiple xml nodes and xml attributes in DB2

Question

Need help to retrieve a value under multiple xml nodes and xml attributes. Below is the xml and the query. I am trying get ID 12945 from agent2 .Any help greatly appreciated.

 <Message>
    <PartyRole xsi:type="isf:Agent1"/>
        <Id>12645</Id>
    </PartyRole>
    <PartyRole xsi:type="isf:Agent2"/>
        <Id>12945</Id>
    </PartyRole>                        
    <PartyRole xsi:type="isf:Agent3">
        <Id>52345</Id>
    </PartyRole>                        
 <Message>

Query: XMLCAST(XMLQUERY('$file/*:Message/*:PartyRole/*:PartyRole[@xsi = "isf:Agent2"]/*:Id' PASSING XMLPARSE(DOCUMENT T1."XML_COLUMN") AS "file") AS CHAR(5)) AS AgentID

question from:https://stackoverflow.com/questions/65833044/retrieve-a-value-from-multiple-xml-nodes-and-xml-attributes-in-db2

Answer 1

Your xml is not valid! or you have just pasted a portion of it.

You have a namespace alias, that is not defined anywhere xsi. Also, first two nodes are self-closed, the the "childs" are hanging...

Anyway.

I tried to "guess" what would be your good xml... If using this one:

<Message xmlns:xsi="anything">
    <PartyRole xsi:type="isf:Agent1">
        <Id>12645</Id>
    </PartyRole>
    <PartyRole xsi:type="isf:Agent2">
        <Id>12945</Id>
    </PartyRole>                        
    <PartyRole xsi:type="isf:Agent3">
        <Id>52345</Id>
    </PartyRole>                        
 </Message>

Following query works as your desire:

with goodxml(cxml) as  ( values xmlparse( document ' <Message xmlns:xsi="anything">
    <PartyRole xsi:type="isf:Agent1">
        <Id>12645</Id>
    </PartyRole>
    <PartyRole xsi:type="isf:Agent2">
        <Id>12945</Id>
    </PartyRole>                        
    <PartyRole xsi:type="isf:Agent3">
        <Id>52345</Id>
    </PartyRole>                        
 </Message>' ) )

SELECT XMLCAST( XMLQUERY('$file/Message/PartyRole[@*:type="isf:Agent2"]/Id' 
                   PASSING CXML AS "file") AS CHAR(5)) AS AgentID 
    FROM goodxml    

---

AGENTID
-------
12945

  1 record(s) selected.