Hi All, I've been fighting with this one for hours now.. (I think I'm using the correct terminology)
Im on SE 12.1.0.1
Given the following XML
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<protocol id="82945242" version="1.0" xsi:schemaLocation="http://www.identisoft.net/protocol protocol.xsd" xmlns="http://www.identisoft.net/protocol" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<dbsearch id="0" luceneSearch="select m from Master m where m.mstIdnumber = 1234" recordCount="100" result="Success">
<record xsi:type="master" firstName="John" lastName="Doe" idnumber="12345" middleName="" gender="M" displayName="John Doe" title="" current="1" mstStartDate="0" mstExpiryDate="0" id="7">
<profile name="Visitor" PDesc="Configure visitors, their tags and access rights" PSysname="DEFAULT" id="41"/>
<company name="Default Company" id="1"/>
<mastertype name="Access Only" id="1"/>
<department name="Visitor" id="1"/>
<site name="Default Site" id="2"/>
</record>
</dbsearch>
</protocol>
I'm trying to extract the value of id on the end of the record node. i.e. I want the value 7 from id="7"
I found an example in the community using xmlquery, but it doesn't work, it always returns null. , here is the example:
with testTable(xml_val) as
(select xmltype('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<protocol id="82945242" version="1.0" xsi:schemaLocation="http://www.identisoft.net/protocol protocol.xsd" xmlns="http://www.identisoft.net/protocol" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<dbsearch id="0" luceneSearch="select m from Master m where m.mstIdnumber = 1234" recordCount="100" result="Success">
<record xsi:type="master" firstName="John" lastName="Doe" idnumber="12345" middleName="" gender="M" displayName="John Doe" title="" current="1" mstStartDate="0" mstExpiryDate="0" id="7">
<profile name="Visitor" PDesc="Configure visitors, their tags and access rights" PSysname="DEFAULT" id="41"/>
<company name="Default Company" id="1"/>
<mastertype name="Access Only" id="1"/>
<department name="Visitor" id="1"/>
<site name="Default Site" id="2"/>
</record>
</dbsearch>
</protocol>
')
from dual)
select xmlcast(xmlquery('/protocol/dbsearch/record/@id' passing xml_val returning content) as number(10) ) as ID from testTable
Id be grateful if someone can help, as Im pulling out whats left of my hair...
Many Thanks