In the last episode (Mar 31), Carlos Fernndez Iglesias said: > I have this field in a table from my database that contains a lot of > information and I would like to extract only a little bit of it. I have > to parse it but need to do it directly in the sql query, do you know what > kind of function I have to use, or how?
Since your data is well-formed XML, you can use mysql's ExtractValue() function: mysql> select extractvalue(f,"/TEMPLATE/CPU") from t; +---------------------------------+ | extractvalue(f,"/TEMPLATE/CPU") | +---------------------------------+ | 1 | +---------------------------------+ 1 row in set (0.01 sec) See http://dev.mysql.com/doc/refman/5.5/en/xml-functions.html for more info. > This is an example of the field (i need the 1 in cpu data): > > | > <TEMPLATE><CONTEXT><FILES><![CDATA[/srv/cloud/images/carlos_iglesias/cluster_benchmark_kvm/cluster_head_context/init.sh > > /srv/cloud/cluster_deploys/benchmak-kvm_clusterfiles/hosts > /srv/cloud/cluster_deploys/benchmak-kvm_clusterfiles/exports > /srv/cloud/cluster_deploys/benchmak-kvm_clusterfiles/fstab_head_extra > /srv/cloud/images/carlos_iglesias/cluster_benchmark_kvm/cluster_head_context/sge_conf.sh > > /srv/cloud/images/carlos_iglesias/cluster_benchmark_kvm/cluster_head_context/ssh_config_root > > /srv/cloud/cluster_deploys/benchmak-kvm_clusterfiles/root_id_rsa > /srv/cloud/cluster_deploys/benchmak-kvm_clusterfiles/root_id_rsa.pub > /srv/cloud/images/carlos_iglesias/cluster_benchmark_kvm/cluster_head_context/ssh_config_user > > /srv/cloud/cluster_deploys/benchmak-kvm_clusterfiles/user_id_rsa > /srv/cloud/cluster_deploys/benchmak-kvm_clusterfiles/user_id_rsa.pub > /srv/cloud/cluster_deploys/benchmak-kvm_clusterfiles/user_ext.pub]]></FILES><GATEWAY><![CDATA[84.21.173.254]]></GATEWAY><HOSTNAME><![CDATA[benchmak-kvm_head]]></HOSTNAME><IP_PRIVATE><![CDATA[192.168.194.1]]></IP_PRIVATE><IP_PUBLIC><![CDATA[84.21.173.194]]></IP_PUBLIC><NODE_CORES><![CDATA[1]]></NODE_CORES><ROOT_PASSWD><![CDATA[rootpass194]]></ROOT_PASSWD><TARGET><![CDATA[hdd]]></TARGET><USER_GID><![CDATA[1000]]></USER_GID><USER_ID><![CDATA[1000]]></USER_ID><USER_NAME><![CDATA[eimrt]]></USER_NAME><USER_PASSWD><![CDATA[eimrtpass]]></USER_PASSWD></CONTEXT><CPU><![CDATA[1]]></CPU><DISK><BUS><![CDATA[ide]]></BUS><CLONE><![CDATA[YES]]></CLONE><DISK_ID><![CDATA[0]]></DISK_ID><IMAGE><![CDATA[cluster_head-30.8.10-2GB-grub]]></IMAGE><IMAGE_ID><![CDATA[0]]></IMAGE_ID><READONLY><![CDATA[NO]]></READONLY><SAVE><![CDATA[NO]]></SAVE><SOURCE><![CDATA[/srv/cloud/one/var//images/167afbfb852ba6fee3ebe34a48d7709545b1eb37]]></SOURCE><TARGET><![CDATA[hda]]></TARGET><TYPE><![CDATA[DISK]]></TYPE></! DISK><DISK><DISK_ID><![CDATA[1]]></DISK_ID><FORMAT><![CDATA[ext3]]></FORMAT><MODEL><![CDATA[virtio]]></MODEL><READONLY><![CDATA[no]]></READONLY><SIZE><![CDATA[20480]]></SIZE><TARGET><![CDATA[hdb]]></TARGET><TYPE><![CDATA[fs]]></TYPE></DISK><GRAPHICS><KEYMAP><![CDATA[es]]></KEYMAP><LISTEN><![CDATA[127.0.0.1]]></LISTEN><PORT><![CDATA[6613]]></PORT><TYPE><![CDATA[vnc]]></TYPE></GRAPHICS><MEMORY><![CDATA[1024]]></MEMORY><NAME><![CDATA[benchmak-kvm_head]]></NAME><NIC><BRIDGE><![CDATA[br1]]></BRIDGE><IP><![CDATA[84.21.173.194]]></IP><MAC><![CDATA[02:fe:54:15:ad:c2]]></MAC><MODEL><![CDATA[virtio]]></MODEL><NETWORK><![CDATA[Public > LAN > kvm]]></NETWORK><NETWORK_ID><![CDATA[1]]></NETWORK_ID></NIC><NIC><BRIDGE><![CDATA[br1]]></BRIDGE><IP><![CDATA[192.168.194.1]]></IP><MAC><![CDATA[02:fe:c0:a8:c2:01]]></MAC><MODEL><![CDATA[virtio]]></MODEL><NETWORK><![CDATA[benchmak-kvm_localnet]]></NETWORK><NETWORK_ID><![CDATA[92]]></NETWORK_ID></NIC><RANK><![CDATA[- > > RUNNING_VMS]]></RANK><REQUIREMENTS><![CDATA[CLUSTER = > "kvm"]]></REQUIREMENTS><VCPU><![CDATA[1]]></VCPU><VMID><![CDATA[713]]></VMID></TEMPLATE> > > | -- Dan Nelson [email protected] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[email protected]
