Topic
  • 2 replies
  • Latest Post - ‏2012-02-13T17:45:11Z by MatthiasNicola
eduardok
eduardok
7 Posts

Pinned topic Functions on XML

‏2012-02-13T12:13:44Z |

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.:

machine1,owner_bla,2
machine53,owner_bleh,1
machine321,owner_heh,0

Thank you in advance!
Updated on 2012-02-13T17:45:11Z at 2012-02-13T17:45:11Z by MatthiasNicola
  • eduardok
    eduardok
    7 Posts

    Re: Functions on XML

    ‏2012-02-13T12:29:33Z  
    The "Producing one row per phone number" example seems to be the best I can find: http://www.ibm.com/developerworks/data/library/techarticle/dm-0708nicola/

    I wonder if I could somehow embed the XMLQUERY seen here (posted by Matthias) http://www.ibm.com/developerworks/forums/thread.jspa?threadID=417074&tstart=0 to get the count out on one sql.
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: Functions on XML

    ‏2012-02-13T17:45:11Z  
    • eduardok
    • ‏2012-02-13T12:29:33Z
    The "Producing one row per phone number" example seems to be the best I can find: http://www.ibm.com/developerworks/data/library/techarticle/dm-0708nicola/

    I wonder if I could somehow embed the XMLQUERY seen here (posted by Matthias) http://www.ibm.com/developerworks/forums/thread.jspa?threadID=417074&tstart=0 to get the count out on one sql.
    How about the following - does that help?

    
    create table eduard(doc XML);   insert into eduard values(
    ' <systems> <system machine=
    "M1"> <disk id=
    "sda" inactive=
    "false"/> <disk id=
    "sdb" inactive=
    "true"/> <disk id=
    "sdc" inactive=
    "false"/> </system> <system machine=
    "M2"> <disk id=
    "sda" inactive=
    "false"/> <disk id=
    "sdb" inactive=
    "false"/> <disk id=
    "sdc" inactive=
    "false"/> </system> </systems>
    ');   SELECT P.MACHINE, DISKCOUNT FROM eduard, XMLTABLE(
    '$DOC/systems/system' COLUMNS MACHINE   CHAR(60) PATH 
    '@machine', DISKCOUNT SMALLINT PATH 
    'count(disk[@inactive="false"])' ) AS P;     MACHINE                                                      DISKCOUNT ------------------------------------------------------------ --------- M1                                                                   2 M2                                                                   3   2 record(s) selected.
    



    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/