CREATE VIEW test_view (machine,owner,diskcount) AS SELECT P.MACHINE,U.OWNER_ID,P.DISKCOUNT FROM XMLS AS U, XMLTABLE( '$content/systems' PASSING U.XML_CONTENT AS "content" COLUMNS "MACHINE" CHAR(60) PATH 'system/@machine', "DISKCOUNT" SMALLINT PATH '//disk[@inactive="false"]/count()' ) AS P;
Note the use of /count() that I'd like. It doesn't work.
Imagine I have on that xml many:
<disk id="sda" inactive="false"/>
<disk id="sdb" inactive="true"/>
<disk id="sdc" inactive="false"/>
So I'd like to have the number (thus the SMALLINT instead of XML) of active disks returned instead of the XML tags of each "@inactive='false'". I'd be very happy with anything (whatever workaround) that would output what I want, e.g.:
Thank you in advance!