PDA

View Full Version : Friendly inquiry



stanl
12-03-2009, 01:34 PM
.... anyone have any experience with PostGRE XML data types; particularly setting conditions based on specific tags within the XML? Looking to share. Stan

Tommy
12-03-2009, 02:19 PM
I have some experence with postgresql, I also "helped" with the ODBC driver. No experence with xml data type though. What little work I did with it was in 1995-2008, I used Linux Redhat 5.1-7.2. So I doubt if I could help but I might be able to.

stanl
12-05-2009, 04:18 AM
I have some experence with postgresql

We would be moving from PostGRE 8.3 -> 8.4 and my understanding is they will be decrementing several xml functions, but my issue is more basic. Assume a table xmldata, with three columns both referring to workorders - Date [a timestamp showing the last time the workorder was updated] WO [the workorder number] and xml [xml representation of workorder queried via Web Services].

One of the tags within the xml field witll be Completion_Time, and this has no relation to the Date field in the table. So assume I needed to query rows where the complete time = 2009-11-01

I now would construct something like

cSQL = "SELECT WO,xml::text FROM xmldata WHERE Date>='2009-10-31'" then parse through the xml data for the tag <Completion_Time>2009-11-01</Completion_Time>

unlike SQL Server which permits indexing/searching on tags, I am unable to discover anything similar in PostGRE.

P.S. - I didn't design the PostGRE tables, and my suggestions to perhaps include a field for Completion were unheeded, even though that is mission critical information.:dunno

Tommy
12-06-2009, 10:21 AM
I stored the dates in the format 'mm/dd/yyyy' and this may be a regional setting but would you be confusing the database engine by using the 'yyyy-mm-dd' format and using the keyword Date?

stanl
12-07-2009, 04:12 AM
I stored the dates in the format 'mm/dd/yyyy' and this may be a regional setting but would you be confusing the database engine by using the 'yyyy-mm-dd' format and using the keyword Date?

That is not an issue, and using either date format still obtains the data. Point I was getting at was limiting the query to the actual tag value, something like:


cSQL = "SELECT WO,xml::text FROM xmldata WHERE xml::Completion_Time like '2009-10-31*'"


NOTE: Completion time is really a string value in notation
yyyy-mm-dd-m-s.ms

Which of course ends up with a hideous PostGRE error.