| Author |
Topic |
|
savvas1986
Starting Member
4 Posts |
 Posted - 2012-03-22 : 07:06:22
|
| Hello,I have db2 table with a text data type field. The data that this fields contain have the following format per row :" <entry><java.lang.String>NAME</java.lang.String><Vector> <java.lang.String>LALALALA</java.lang.String></Vector></entry> <entry><java.lang.String>PHONE</java.lang.String><Vector> <java.lang.String>111111</java.lang.String></Vector></entry>" etc... . What i need is a statment that will select only the phone (111111). The lenght is not standart so i cannot use substring. I would be greatefull if you could help me with this. Thank you |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-03-22 : 07:08:33
|
| This forum is for Microsoft SQL Server, so while there may be a few people who might be able to offer DB2 help, you are likely to get faster and better responses at a DB2 forum or more generalized database forums such as dbforums.com |
 |
|
|
savvas1986
Starting Member
4 Posts |
Posted - 2012-03-22 : 07:11:01
|
| Thank you for correcting me.But you can consider it as a MS SQL Server question. I think the solution will be almost the same. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-03-22 : 07:18:30
|
If you say so. I know nothing at all about DB2.  This works on SQL Server.DECLARE @x VARCHAR(MAX);SET @x = '<entry><java.lang.String>NAME</java.lang.String><Vector> <java.lang.String>LALALALA</java.lang.String></Vector></entry><entry><java.lang.String>PHONE</java.lang.String><Vector> <java.lang.String>111111</java.lang.String></Vector></entry>';SELECT cast(@x AS XML).query('data(/entry/java.lang.String[.="PHONE"]/../Vector/java.lang.String)') |
|
|
|
savvas1986
Starting Member
4 Posts |
Posted - 2012-03-22 : 07:32:36
|
| The data that this field contains is not standart so that i can set them to @x . If i set @x=Select myfield from mytable , and then cast it as xml . will this do the trick ? |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-03-22 : 08:03:57
|
You would need to cast the column to XML and then query against the colum. See this example:CREATE TABLE #tmp (id INT, x VARCHAR(MAX));INSERT INTO #tmp VALUES(1,' <entry><java.lang.String>NAME</java.lang.String><Vector> <java.lang.String>LALALALA</java.lang.String></Vector></entry><entry><java.lang.String>PHONE</java.lang.String><Vector> <java.lang.String>111111</java.lang.String></Vector></entry>');SELECT c.query('data(Vector/java.lang.String)')FROM( SELECT CAST(x AS XML) AS XMLCol FROM #tmp) sCROSS APPLY s.XMLCol.nodes('/entry') T(c)WHERE c.exist('java.lang.String[.="PHONE"]') = 1DROP TABLE #tmp; |
|
|
|
savvas1986
Starting Member
4 Posts |
Posted - 2012-03-22 : 08:12:21
|
| Could you explain me what this row does? CROSS APPLY s.XMLCol.nodes('/entry') T(c) |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-03-22 : 08:21:48
|
quote:
Originally posted by savvas1986
Could you explain me what this row does? CROSS APPLY s.XMLCol.nodes('/entry') T(c)
Cross apply is similar to an inner join; the right side is evaluated for each row of the left side where there is a match. http://technet.microsoft.com/en-us/library/ms175156.aspx |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
DB2 text field