List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:March 31 2011 4:35pm
Subject:Re: Parse text field from query
View as plain text  
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
	dnelson@stripped

Thread
Parse text field from queryCarlos Fern├índez Iglesias31 Mar
  • Re: Parse text field from queryDan Nelson31 Mar