.... anyone have any experience with PostGRE XML data types; particularly setting conditions based on specific tags within the XML? Looking to share. Stan
Printable View
.... anyone have any experience with PostGRE XML data types; particularly setting conditions based on specific tags within the XML? Looking to share. Stan
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.
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].Quote:
Originally Posted by Tommy
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
[vba]cSQL = "SELECT WO,xml::text FROM xmldata WHERE Date>='2009-10-31'"[/vba] 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
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:Quote:
Originally Posted by Tommy
[VBA]
cSQL = "SELECT WO,xml::text FROM xmldata WHERE xml::Completion_Time like '2009-10-31*'"
[/VBA]
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.