| Author |
Topic |
|
Trojka
Starting Member
10 Posts |
 Posted - 2006-12-08 : 14:22:44
|
Hi, I'm new here...don't throw rocks at me.  I'm also new to SQL-T - we just migrated all of our software/servers from an Oracle/Unix environment to an all Microsoft environment: Microsoft SQL2005/.net. All the stuff I've learned in the past in Oracle is slightly different than it works in SQL-T. So, I feel like an idiot asking this but here we go:Our database contains a table with a text field, filled with XML data (also new to me). In this XML field I would like to 1. query a particular string and 2. display that particular string (for instance Contract="40489483"). With my newly acquainted VBScript knowledge, my first idea is to try it with a regular expression: query with the regexp 'Contract="\w+"' and I'm done.Unfortunately I haven't found a way to do that in SQL-T. I can use the patindex and substring functions (as I've now done to quickly solve my problem), but unfortunately contract id differs in length so it's hard to pull it off with a substring. I've made a very ugly query with lots of replace, patindex and substring functions that actually works but it's horrible to manage (or to explain to even more unexperienced co workers).  So, is there an 'easier' way to do it? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-12-08 : 14:26:52
|
if you're working with sql server 2005 then you can use its xquery built in capabilities.SQL server has an XML datatype that provides this.Look into BOL = Books Online = SQL server help on how to use it.also this will be of help:http://www.15seconds.com/issue/050803.htmGo with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
 |
|
|
Trojka
Starting Member
10 Posts |
Posted - 2006-12-08 : 14:30:37
|
| I've seen them before, but I thought it wouldn't work on a text field. I assumed it would only work with xml fields...I'll look into it again! |
|
|
|
Trojka
Starting Member
10 Posts |
Posted - 2006-12-11 : 01:43:51
|
| Just what I thought:Msg 258, Level 15, State 1, Line 1Cannot call methods on text.Unfortunately the developers chose text instead of xml. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-11 : 01:52:22
|
| sp_xml_preparedocument maybe?Kristen |
|
|
|
Trojka
Starting Member
10 Posts |
Posted - 2006-12-11 : 02:05:36
|
| Or a view transforming the text to xml with cast? I'll investigate sp_xml_preparedocument...I'm learning every minute now. LOL |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-11 : 02:48:36
|
| What if you transform TEXT to VARCHAR(MAX)?Peter LarssonHelsingborg, Sweden |
|
|
|
Trojka
Starting Member
10 Posts |
Posted - 2006-12-11 : 03:33:26
|
| Made a view transforming the text like this: cast(publicatiexml as xml). That seems to work. Slowly getting there. SELECT p.publicatiexml.query('KoppelingAutomaatContractPublicatie/Current/KoppelingAutomaatContract/Automaat') from publicatie p where id = 1552Now the element Automaat has an attribute called Nummer, and I would like to show the value of that attribute. I just can't seem to figure it out...I can query with it (/[@Nummer="123"], but showing it seems impossible. |
|
|
|
Trojka
Starting Member
10 Posts |
Posted - 2006-12-11 : 03:49:09
|
| Aaaaaaaah, found it! I need to use the XValue option! Posting it for archiving purposes (maybe someone else struggling with this problem)SELECT p.publicatiexml.value('(KoppelingAutomaatContractPublicatie/Current/KoppelingAutomaatContract/Automaat/@Nummer)[1]','int') from publicatie p where id = 1552Or even more beautiful, without using the view, SELECT p.publicatiexml.value('(KoppelingAutomaatContractPublicatie/Current/KoppelingAutomaatContract/@AutomaatNummer)[1]','nvarchar(6)') from(select cast(publicatiexml as xml) publicatiexmlfrom cim_historie.dbo.publicatiewhere id = 1552and verwerkt =1 ) p |
|
|
|
|
Regular expressions?