FW: [SQL] Working with XML.
Title: Message Hi George, Thanks for your help once again. However I cant seem to find XML2, the contrib package for RedHat ES3 I downloaded only has xml. Where can I find the compiled or source code. The only link I have is http://developer.postgresql.org/docs/pgsql/contrib/ , it there ftp access or cvs access to contrib? Theo -Original Message-From: George Weaver [mailto:[EMAIL PROTECTED] Sent: Wednesday, 16 February 2005 12:37 AMTo: Theo GalanakisCc: pgsql-sql@postgresql.orgSubject: Re: [SQL] Working with XML. Hi Theo, I'm not sure if it can be done with the xml contrib module. You may want to install and work with the xml2 contrib module, which is more recent, has more extensive capabilities, and is easier to work with. It will give you the result you want: jan28-05=# select xpath_string(jan28-05(# '284122789648{ts ''2005-02-14 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts ''2005-02-14 16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts ''2005-02-14 16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r'jan28-05(# ,'//query/@rows') as rows; rows-- 100(1 row) Another example: jan28-05=# select xpath_string(jan28-05(# '284122789648 {ts ''2005-02-14 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts ''2005-02-14 16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts ''2005-02-14 16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r'jan28-05(# ,'//row[cola=284122]/colb') as colb; colb 789648(1 row) HTH, George - Original Message - From: Theo Galanakis To: 'George Weaver' Cc: 'pgsql-sql@postgresql.org' Sent: Monday, February 14, 2005 9:46 PM Subject: RE: [SQL] Working with XML. Thanks George. How do you get an attributes value the following returns the attribute tag. i.e. rows="100", all i want is the 100. select pgxml_xpath('284122789648{ts ''2005-02-14 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts ''2005-02-14 16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts ''2005-02-14 16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r','//query/@rows','','') Theo -Original Message-From: George Weaver [mailto:[EMAIL PROTECTED] Sent: Tuesday, 15 February 2005 12:39 AMTo: Theo Galanakis; pgsql-sql@postgresql.orgSubject: Re: [SQL] Working with XML. Hi Theo, I am not aware of any means of passing xml to stored procedures, apart from writing your own function to parse the xml. In regard to your second question - how to access the second record - try using a more explicit xpath query incorporating a "where" component. For example, if you wanted to access the second row based upon the value of cola, you could use '//query/[EMAIL PROTECTED]'525887']/text()'. Or you could specify the position of the record if you know its position: '//query/row[2]/text()'. Microsoft has a very good reference on xpath expressions: http://msdn.microsoft.com/library/default.asp?url="">. Regards, George - Original Message - From: Theo Galanakis To: pgsql-sql@postgresql.org Sent: Sunday, February 13, 2005 11:48 PM Subject: [SQL] Working with XML. Hi Folks, Is there a way to pass in an xml string into a stored proc and thenplace this into a temp table? I use to be able to do this in sql server, it was quite handy as I could call one stored proc to update multiple records, here is a sample in sql server: select CoverTypeID, ItemSQ, SituationID, ItemDescription, CoverAmount From OpenXML ( @XmlHandle, '/cover/covertype/item',1 ) With ( CoverTypeID int '../@id', ItemSQ int '@id', SituationID int '@situationID', ItemDescription varchar(100) '@description', CoverAmount money '@amount'
FW: [SQL] Working with XML.
Title: Message Hi, I have copied all the files manually from http://developer.postgresql.org/docs/pgsql/contrib/ for the xml2 contribution. However I have the following issue when I attempt to compile with gmake: gcc -I/usr/include/libxml2 -fpic -I. -I../../src/include -D_GNU_SOURCE -c -o xpath.o xpath.cxpath.c: In function `xpath_table':xpath.c:689: `work_mem' undeclared (first use in this function)xpath.c:689: (Each undeclared identifier is reported only oncexpath.c:689: for each function it appears in.)gmake: *** [xpath.o] Error 1 I have installed : libxml2-devel-2.5.10-1.rpm What am I doing wrong, or can someone point me to the direction of a binary for XML2 on RedHat ES3, Postgres 7.4.5. Cheers, Theo -Original Message-From: Theo Galanakis Sent: Monday, 21 February 2005 11:52 AMTo: Subject: FW: [SQL] Working with XML. Hi George, Thanks for your help once again. However I cant seem to find XML2, the contrib package for RedHat ES3 I downloaded only has xml. Where can I find the compiled or source code. The only link I have is http://developer.postgresql.org/docs/pgsql/contrib/ , it there ftp access or cvs access to contrib? Theo -Original Message-From: George Weaver [mailto:[EMAIL PROTECTED] Sent: Wednesday, 16 February 2005 12:37 AMTo: Theo GalanakisCc: pgsql-sql@postgresql.orgSubject: Re: [SQL] Working with XML. Hi Theo, I'm not sure if it can be done with the xml contrib module. You may want to install and work with the xml2 contrib module, which is more recent, has more extensive capabilities, and is easier to work with. It will give you the result you want: jan28-05=# select xpath_string(jan28-05(# '284122789648{ts ''2005-02-14 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts ''2005-02-14 16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts ''2005-02-14 16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r'jan28-05(# ,'//query/@rows') as rows; rows-- 100(1 row) Another example: jan28-05=# select xpath_string(jan28-05(# '284122789648 {ts ''2005-02-14 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts ''2005-02-14 16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts ''2005-02-14 16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r'jan28-05(# ,'//row[cola=284122]/colb') as colb; colb 789648(1 row) HTH, George - Original Message - From: Theo Galanakis To: 'George Weaver' Cc: 'pgsql-sql@postgresql.org' Sent: Monday, February 14, 2005 9:46 PM Subject: RE: [SQL] Working with XML. Thanks George. How do you get an attributes value the following returns the attribute tag. i.e. rows="100", all i want is the 100. select pgxml_xpath('284122789648{ts ''2005-02-14 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts ''2005-02-14 16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts ''2005-02-14 16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r','//query/@rows','','') Theo -Original Message-From: George Weaver [mailto:[EMAIL PROTECTED] Sent: Tuesday, 15 February 2005 12:39 AMTo: Theo Galanakis; pgsql-sql@postgresql.orgSubject: Re: [SQL] Working with XML. Hi Theo, I am not aware of any means of passing xml to stored procedures, apart from writing your own function to parse the xml. In regard to your second question - how to access the second record - try using a more explicit xpath query incorporating a "where" component. For example, if you wanted to access the second row based upon the value of cola, you could use '//query/[EMAIL PROTECTED]'525887']/text()'. Or you could specify the position of the record if you know its position: '//query/row[2]/text()'. Microsoft has a very good reference on xpath expressions: http://msdn.microsoft.com/library/default.asp?url="">. Regards, George - Original Message - From: Theo Galanakis To: pgsql-sql@postgresql.org Sent: Sunday, F
