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

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
    ACCEPTED ANSWER

    Re: Functions on XML

    ‏2012-02-13T12:29:33Z  in response to eduardok
    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
      ACCEPTED ANSWER

      Re: Functions on XML

      ‏2012-02-13T17:45:11Z  in response to eduardok
      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/