Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2014-04-08 : 17:21:52
|
| Hi,I need to find a way to query a value stored inside a table column. I have a piece of sample script that looks like this:select convert(xml,[VALUE]).value('(/ROOT/app/@S_YEAR)[1]','numeric(4,0)') as S_Year from myTble where [KEY] = 'StringA'The myTble has 2 columns, Key, Value, and the Value is of data type text. The data stored in there is a large xml node or doc. The table has only three rows for StringA, StringB, and StringC.I feel bad that I cannot post the xml here because it contains a lot of sensitive data. It will be very hard for me to replace those sensitive data without breaking the syntax integrity.I used to know XPath, but couldn't make out above Select.Anyway, above select returns 2013. Thanks! |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-04-08 : 17:35:45
|
quote:
Originally posted by Hommer
Hi,I need to find a way to query a value stored inside a table column. I have a piece of sample script that looks like this:select convert(xml,[VALUE]).value('(/ROOT/app/@S_YEAR)[1]','numeric(4,0)') as S_Year from myTble where [KEY] = 'StringA'The myTble has 2 columns, Key, Value, and the Value is of data type text. The data stored in there is a large xml node or doc. The table has only three rows for StringA, StringB, and StringC.I feel bad that I cannot post the xml here because it contains a lot of sensitive data. It will be very hard for me to replace those sensitive data without breaking the syntax integrity.I used to know XPath, but couldn't make out above Select.Anyway, above select returns 2013. Thanks!
Based on what you described, this query looks fine. It is trying to get the value of attribute S_YEAR from the table for the row for which key = 'StringA'.Is it not working correctly? What is the question you are asking?If it is not giving you the values you expect, first thing to make sure is that you are using the correct upper/lower case in your query. XML is case-sensitive (even if your server/database/table is set to case-insensitive collation). |
 |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2014-04-09 : 09:03:44
|
| James,Thanks!This one is fine. I want to copy and modify it for other use. I guess I need to find more examples of how VALUE() and XML work. |
|
|
|
|
|
|
|
|
get to xml value inside a table column