Consulting

Results 1 to 5 of 5

Thread: Friendly inquiry

  1. #1
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location

    Friendly inquiry

    .... anyone have any experience with PostGRE XML data types; particularly setting conditions based on specific tags within the XML? Looking to share. Stan

  2. #2
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    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.

  3. #3
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Quote Originally Posted by Tommy
    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

    [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.

  4. #4
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    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?

  5. #5
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Quote Originally Posted by Tommy
    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:

    [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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •