pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWorking with XML.
13+ messages / 3 participants
[nested] [flat]
* Working with XML.
@ 2005-02-14 05:48 Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2005-02-14 13:38 ` Re: Working with XML. George Weaver <gweaver@shaw.ca>
0 siblings, 1 reply; 13+ messages in thread
From: Theo Galanakis @ 2005-02-14 05:48 UTC (permalink / raw)
To: pgsql-sql
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' )
I have managed to get get pgxml_xpath working, however Im not sure how to
access specific rows in an xml document. E.g below there are two records,
how do I access the second record, the following returns both
,'//query/row/cola values being (284122,525887):
select
pgxml_xpath(
'<query columns="4"
rows="100"><row><cola>284122</cola><colb>789648</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5w
ya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</cold></row<row><cola>52588
7</cola><colb>493253</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>6uydk442uz247ga45kpys7htkxznkn8La31qhn942wu2cu2pdr25
mv2nup2zh3vcbh3c4vdauak3p3w093cvtkeyga692b455cr3</cold></row></query>'
,'//query/row/cola/text()','','')
______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Working with XML.
2005-02-14 05:48 Working with XML. Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
@ 2005-02-14 13:38 ` George Weaver <gweaver@shaw.ca>
0 siblings, 0 replies; 13+ messages in thread
From: George Weaver @ 2005-02-14 13:38 UTC (permalink / raw)
To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; pgsql-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/row[@cola='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=/library/en-us/xmlsdk/html/xmrefxpathsyntax.asp.
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' )
I have managed to get get pgxml_xpath working, however Im not sure how to access specific rows in an xml document. E.g below there are two records, how do I access the second record, the following returns both ,'//query/row/cola values being (284122,525887):
select
pgxml_xpath(
'<query columns="4" rows="100"><row><cola>284122</cola><colb>789648</colb><colc>{ts ''2005-02-14 16:13:18''}</colc><cold>unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</cold></row<row><cola>525887</cola><colb>493253</colb><colc>{ts ''2005-02-14 16:13:18''}</colc><cold>6uydk442uz247ga45kpys7htkxznkn8La31qhn942wu2cu2pdr25mv2nup2zh3vcbh3c4vdauak3p3w093cvtkeyga692b455cr3</cold></row></query>'
,'//query/row/cola/text()','','')
______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Working with XML.
@ 2005-02-15 03:46 Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2005-02-15 13:36 ` Re: Working with XML. George Weaver <gweaver@shaw.ca>
0 siblings, 1 reply; 13+ messages in thread
From: Theo Galanakis @ 2005-02-15 03:46 UTC (permalink / raw)
To: George Weaver <gweaver@shaw.ca>; +Cc: pgsql-sql
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(
'<query columns="4"
rows="100"><row><cola>284122</cola><colb>789648</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5w
ya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</cold></row><row><cola>2431
51</cola><colb>750292</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3L
q05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0</cold></row><row><cola>7649
29</cola><colb>641215</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLs
vp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r</cold></row></query>'
,'//query/@rows','','')
Theo
-----Original Message-----
From: George Weaver [mailto:gweaver@shaw.ca]
Sent: Tuesday, 15 February 2005 12:39 AM
To: Theo Galanakis; pgsql-sql@postgresql.org
Subject: 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/row[@cola='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=/library/en-us/xmlsdk/html
/xmrefxpathsyntax.asp
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsdk/htm
l/xmrefxpathsyntax.asp> .
Regards,
George
----- Original Message -----
From: Theo Galanakis <mailto:Theo.Galanakis@lonelyplanet.com.au>
To: pgsql-sql@postgresql.org <mailto: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' )
I have managed to get get pgxml_xpath working, however Im not sure how to
access specific rows in an xml document. E.g below there are two records,
how do I access the second record, the following returns both
,'//query/row/cola values being (284122,525887):
select
pgxml_xpath(
'<query columns="4"
rows="100"><row><cola>284122</cola><colb>789648</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5w
ya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</cold></row<row><cola>52588
7</cola><colb>493253</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>6uydk442uz247ga45kpys7htkxznkn8La31qhn942wu2cu2pdr25
mv2nup2zh3vcbh3c4vdauak3p3w093cvtkeyga692b455cr3</cold></row></query>'
,'//query/row/cola/text()','','')
______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Working with XML.
2005-02-15 03:46 Re: Working with XML. Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
@ 2005-02-15 13:36 ` George Weaver <gweaver@shaw.ca>
0 siblings, 0 replies; 13+ messages in thread
From: George Weaver @ 2005-02-15 13:36 UTC (permalink / raw)
To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; +Cc: pgsql-sql
MessageHi 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(# '<query columns="4" rows="100"><row><cola>284122</cola><colb>789648</colb><
colc>{ts ''2005-02-14 16:13:18''}</colc><cold>unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1
d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</cold></row><row><cola>24
3151</cola><colb>750292</colb><colc>{ts ''2005-02-14 16:13:18''}</colc><cold>2d4mat2Lp
tmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oo
uihx0</cold></row><row><cola>764929</cola><colb>641215</colb><colc>{ts ''2005-02-14 16
:13:18''}</colc><cold>1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr2
7txvknn92m0ps4riqqx7puqoLn2wpmm2z50r</cold></row></query>'
jan28-05(# ,'//query/@rows') as rows;
rows
------
100
(1 row)
Another example:
jan28-05=# select xpath_string(
jan28-05(# '<query columns="4" rows="100"><row><cola>284122</cola><colb>789648</colb><
colc>{ts ''2005-02-14 16:13:18''}</colc><cold>unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1
d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</cold></row><row><cola>24
3151</cola><colb>750292</colb><colc>{ts ''2005-02-14 16:13:18''}</colc><cold>2d4mat2Lp
tmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oo
uihx0</cold></row><row><cola>764929</cola><colb>641215</colb><colc>{ts ''2005-02-14 16
:13:18''}</colc><cold>1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr2
7txvknn92m0ps4riqqx7puqoLn2wpmm2z50r</cold></row></query>'
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(
'<query columns="4" rows="100"><row><cola>284122</cola><colb>789648</colb><colc>{ts ''2005-02-14 16:13:18''}</colc><cold>unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</cold></row><row><cola>243151</cola><colb>750292</colb><colc>{ts ''2005-02-14 16:13:18''}</colc><cold>2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0</cold></row><row><cola>764929</cola><colb>641215</colb><colc>{ts ''2005-02-14 16:13:18''}</colc><cold>1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r</cold></row></query>'
,'//query/@rows','','')
Theo
-----Original Message-----
From: George Weaver [mailto:gweaver@shaw.ca]
Sent: Tuesday, 15 February 2005 12:39 AM
To: Theo Galanakis; pgsql-sql@postgresql.org
Subject: 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/row[@cola='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=/library/en-us/xmlsdk/html/xmrefxpathsyntax.asp.
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' )
I have managed to get get pgxml_xpath working, however Im not sure how to access specific rows in an xml document. E.g below there are two records, how do I access the second record, the following returns both ,'//query/row/cola values being (284122,525887):
select
pgxml_xpath(
'<query columns="4" rows="100"><row><cola>284122</cola><colb>789648</colb><colc>{ts ''2005-02-14 16:13:18''}</colc><cold>unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</cold></row<row><cola>525887</cola><colb>493253</colb><colc>{ts ''2005-02-14 16:13:18''}</colc><cold>6uydk442uz247ga45kpys7htkxznkn8La31qhn942wu2cu2pdr25mv2nup2zh3vcbh3c4vdauak3p3w093cvtkeyga692b455cr3</cold></row></query>'
,'//query/row/cola/text()','','')
______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Working with XML.
@ 2005-02-16 01:32 George Weaver <gweaver@shaw.ca>
0 siblings, 0 replies; 13+ messages in thread
From: George Weaver @ 2005-02-16 01:32 UTC (permalink / raw)
To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; +Cc: pgsql-sql
MessageHi Theo,
You can find the source code for xml2 it in the 8 source tree http://www.postgresql.org/download/.
If you're working with an earlier version of PostgreSQL than 8, you may have to make some modifications to the contrib code to get it to compile and link properly - I should be able to help you with that. I don't think you can use it on any version earlier than 7.2.
Regards,
George
----- Original Message -----
From: Theo Galanakis
To: 'George Weaver'
Sent: Tuesday, February 15, 2005 4:30 PM
Subject: RE: [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:gweaver@shaw.ca]
Sent: Wednesday, 16 February 2005 12:37 AM
To: Theo Galanakis
Cc: pgsql-sql@postgresql.org
Subject: 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(# '<query columns="4" rows="100"><row><cola>284122</cola><colb>789648</colb><
colc>{ts ''2005-02-14 16:13:18''}</colc><cold>unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1
d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</cold></row><row><cola>24
3151</cola><colb>750292</colb><colc>{ts ''2005-02-14 16:13:18''}</colc><cold>2d4mat2Lp
tmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oo
uihx0</cold></row><row><cola>764929</cola><colb>641215</colb><colc>{ts ''2005-02-14 16
:13:18''}</colc><cold>1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr2
7txvknn92m0ps4riqqx7puqoLn2wpmm2z50r</cold></row></query>'
jan28-05(# ,'//query/@rows') as rows;
rows
------
100
(1 row)
Another example:
jan28-05=# select xpath_string(
jan28-05(# '<query columns="4" rows="100"><row><cola>284122</cola><colb>789648</colb><
colc>{ts ''2005-02-14 16:13:18''}</colc><cold>unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1
d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</cold></row><row><cola>24
3151</cola><colb>750292</colb><colc>{ts ''2005-02-14 16:13:18''}</colc><cold>2d4mat2Lp
tmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oo
uihx0</cold></row><row><cola>764929</cola><colb>641215</colb><colc>{ts ''2005-02-14 16
:13:18''}</colc><cold>1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr2
7txvknn92m0ps4riqqx7puqoLn2wpmm2z50r</cold></row></query>'
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(
'<query columns="4" rows="100"><row><cola>284122</cola><colb>789648</colb><colc>{ts ''2005-02-14 16:13:18''}</colc><cold>unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</cold></row><row><cola>243151</cola><colb>750292</colb><colc>{ts ''2005-02-14 16:13:18''}</colc><cold>2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0</cold></row><row><cola>764929</cola><colb>641215</colb><colc>{ts ''2005-02-14 16:13:18''}</colc><cold>1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r</cold></row></query>'
,'//query/@rows','','')
Theo
-----Original Message-----
From: George Weaver [mailto:gweaver@shaw.ca]
Sent: Tuesday, 15 February 2005 12:39 AM
To: Theo Galanakis; pgsql-sql@postgresql.org
Subject: 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/row[@cola='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=/library/en-us/xmlsdk/html/xmrefxpathsyntax.asp.
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' )
I have managed to get get pgxml_xpath working, however Im not sure how to access specific rows in an xml document. E.g below there are two records, how do I access the second record, the following returns both ,'//query/row/cola values being (284122,525887):
select
pgxml_xpath(
'<query columns="4" rows="100"><row><cola>284122</cola><colb>789648</colb><colc>{ts ''2005-02-14 16:13:18''}</colc><cold>unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</cold></row<row><cola>525887</cola><colb>493253</colb><colc>{ts ''2005-02-14 16:13:18''}</colc><cold>6uydk442uz247ga45kpys7htkxznkn8La31qhn942wu2cu2pdr25mv2nup2zh3vcbh3c4vdauak3p3w093cvtkeyga692b455cr3</cold></row></query>'
,'//query/row/cola/text()','','')
______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
^ permalink raw reply [nested|flat] 13+ messages in thread
* FW: Working with XML.
@ 2005-02-21 00:52 Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
0 siblings, 0 replies; 13+ messages in thread
From: Theo Galanakis @ 2005-02-21 00:52 UTC (permalink / raw)
To: pgsql-sql
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/
<http://developer.postgresql.org/docs/pgsql/contrib/; , it there ftp access
or cvs access to contrib?
Theo
-----Original Message-----
From: George Weaver [mailto:gweaver@shaw.ca]
Sent: Wednesday, 16 February 2005 12:37 AM
To: Theo Galanakis
Cc: pgsql-sql@postgresql.org
Subject: 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(# '<query columns="4"
rows="100"><row><cola>284122</cola><colb>789648</colb><
colc>{ts ''2005-02-14
16:13:18''}</colc><cold>unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1
d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</cold></row><ro
w><cola>24
3151</cola><colb>750292</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>2d4mat2Lp
tmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrv
yu4k7ng4oo
uihx0</cold></row><row><cola>764929</cola><colb>641215</colb><colc>{ts
''2005-02-14 16
:13:18''}</colc><cold>1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp
9p0s7obzr2
7txvknn92m0ps4riqqx7puqoLn2wpmm2z50r</cold></row></query>'
jan28-05(# ,'//query/@rows') as rows;
rows
------
100
(1 row)
Another example:
jan28-05=# select xpath_string(
jan28-05(# '<query columns="4"
rows="100"><row><cola>284122</cola><colb>789648</colb><
colc>{ts ''2005-02-14
16:13:18''}</colc><cold>unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1
d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</cold></row><ro
w><cola>24
3151</cola><colb>750292</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>2d4mat2Lp
tmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrv
yu4k7ng4oo
uihx0</cold></row><row><cola>764929</cola><colb>641215</colb><colc>{ts
''2005-02-14 16
:13:18''}</colc><cold>1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp
9p0s7obzr2
7txvknn92m0ps4riqqx7puqoLn2wpmm2z50r</cold></row></query>'
jan28-05(# ,'//row[cola=284122]/colb') as colb;
colb
--------
789648
(1 row)
HTH,
George
----- Original Message -----
From: Theo Galanakis <mailto:Theo.Galanakis@lonelyplanet.com.au>
To: 'George Weaver' <mailto:gweaver@shaw.ca>
Cc: 'pgsql-sql@postgresql.org' <mailto:'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(
'<query columns="4"
rows="100"><row><cola>284122</cola><colb>789648</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5w
ya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</cold></row><row><cola>2431
51</cola><colb>750292</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3L
q05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0</cold></row><row><cola>7649
29</cola><colb>641215</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLs
vp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r</cold></row></query>'
,'//query/@rows','','')
Theo
-----Original Message-----
From: George Weaver [mailto:gweaver@shaw.ca]
Sent: Tuesday, 15 February 2005 12:39 AM
To: Theo Galanakis; pgsql-sql@postgresql.org
Subject: 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/row[@cola='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=/library/en-us/xmlsdk/html
/xmrefxpathsyntax.asp
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsdk/htm
l/xmrefxpathsyntax.asp> .
Regards,
George
----- Original Message -----
From: Theo Galanakis <mailto:Theo.Galanakis@lonelyplanet.com.au>
To: pgsql-sql@postgresql.org <mailto: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' )
I have managed to get get pgxml_xpath working, however Im not sure how to
access specific rows in an xml document. E.g below there are two records,
how do I access the second record, the following returns both
,'//query/row/cola values being (284122,525887):
select
pgxml_xpath(
'<query columns="4"
rows="100"><row><cola>284122</cola><colb>789648</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5w
ya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</cold></row<row><cola>52588
7</cola><colb>493253</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>6uydk442uz247ga45kpys7htkxznkn8La31qhn942wu2cu2pdr25
mv2nup2zh3vcbh3c4vdauak3p3w093cvtkeyga692b455cr3</cold></row></query>'
,'//query/row/cola/text()','','')
______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
^ permalink raw reply [nested|flat] 13+ messages in thread
* FW: Working with XML.
@ 2005-02-21 05:28 Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2005-02-21 14:55 ` Re: Working with XML. George Weaver <gweaver@shaw.ca>
2005-02-21 20:36 ` Re: FW: Working with XML. Mirko Zeibig <mirko-postgresql@zeibig.net>
0 siblings, 2 replies; 13+ messages in thread
From: Theo Galanakis @ 2005-02-21 05:28 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have copied all the files manually from http
<http://developer.postgresql.org/docs/pgsql/contrib/;
://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.c
xpath.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 once
xpath.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 AM
To:
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/
<http://developer.postgresql.org/docs/pgsql/contrib/; , it there ftp access
or cvs access to contrib?
Theo
-----Original Message-----
From: George Weaver [mailto:gweaver@shaw.ca]
Sent: Wednesday, 16 February 2005 12:37 AM
To: Theo Galanakis
Cc: pgsql-sql@postgresql.org
Subject: 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(# '<query columns="4"
rows="100"><row><cola>284122</cola><colb>789648</colb><
colc>{ts ''2005-02-14
16:13:18''}</colc><cold>unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1
d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</cold></row><ro
w><cola>24
3151</cola><colb>750292</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>2d4mat2Lp
tmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrv
yu4k7ng4oo
uihx0</cold></row><row><cola>764929</cola><colb>641215</colb><colc>{ts
''2005-02-14 16
:13:18''}</colc><cold>1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp
9p0s7obzr2
7txvknn92m0ps4riqqx7puqoLn2wpmm2z50r</cold></row></query>'
jan28-05(# ,'//query/@rows') as rows;
rows
------
100
(1 row)
Another example:
jan28-05=# select xpath_string(
jan28-05(# '<query columns="4"
rows="100"><row><cola>284122</cola><colb>789648</colb><
colc>{ts ''2005-02-14
16:13:18''}</colc><cold>unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1
d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</cold></row><ro
w><cola>24
3151</cola><colb>750292</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>2d4mat2Lp
tmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrv
yu4k7ng4oo
uihx0</cold></row><row><cola>764929</cola><colb>641215</colb><colc>{ts
''2005-02-14 16
:13:18''}</colc><cold>1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp
9p0s7obzr2
7txvknn92m0ps4riqqx7puqoLn2wpmm2z50r</cold></row></query>'
jan28-05(# ,'//row[cola=284122]/colb') as colb;
colb
--------
789648
(1 row)
HTH,
George
----- Original Message -----
From: Theo Galanakis <mailto:Theo.Galanakis@lonelyplanet.com.au>
To: 'George Weaver' <mailto:gweaver@shaw.ca>
Cc: 'pgsql-sql@postgresql.org' <mailto:'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(
'<query columns="4"
rows="100"><row><cola>284122</cola><colb>789648</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5w
ya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</cold></row><row><cola>2431
51</cola><colb>750292</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3L
q05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0</cold></row><row><cola>7649
29</cola><colb>641215</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLs
vp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r</cold></row></query>'
,'//query/@rows','','')
Theo
-----Original Message-----
From: George Weaver [mailto:gweaver@shaw.ca]
Sent: Tuesday, 15 February 2005 12:39 AM
To: Theo Galanakis; pgsql-sql@postgresql.org
Subject: 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/row[@cola='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=/library/en-us/xmlsdk/html
/xmrefxpathsyntax.asp
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsdk/htm
l/xmrefxpathsyntax.asp> .
Regards,
George
----- Original Message -----
From: Theo Galanakis <mailto:Theo.Galanakis@lonelyplanet.com.au>
To: pgsql-sql@postgresql.org <mailto: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' )
I have managed to get get pgxml_xpath working, however Im not sure how to
access specific rows in an xml document. E.g below there are two records,
how do I access the second record, the following returns both
,'//query/row/cola values being (284122,525887):
select
pgxml_xpath(
'<query columns="4"
rows="100"><row><cola>284122</cola><colb>789648</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5w
ya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</cold></row<row><cola>52588
7</cola><colb>493253</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>6uydk442uz247ga45kpys7htkxznkn8La31qhn942wu2cu2pdr25
mv2nup2zh3vcbh3c4vdauak3p3w093cvtkeyga692b455cr3</cold></row></query>'
,'//query/row/cola/text()','','')
______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Working with XML.
2005-02-21 05:28 FW: Working with XML. Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
@ 2005-02-21 14:55 ` George Weaver <gweaver@shaw.ca>
1 sibling, 0 replies; 13+ messages in thread
From: George Weaver @ 2005-02-21 14:55 UTC (permalink / raw)
To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; pgsql-sql
MessageHi Theo,
There have been 2 major changes between the 7 and 8 versions that affect the coding in xml2. You need to edit the source code in order for it to compile properly on 7.
First, work_mem has to be changed to SortMem (line 666). I.e.
tupstore = tuplestore_begin_heap(true, false, work_mem);
should be changed to:
tupstore = tuplestore_begin_heap(true, SortMem);
Second, the error reporting framework has changed.
To fix this, you need to change all the ereport coding to use elog. I.e.
ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
errmsg("xpath_table must be called as a table function")));
should be changed to:
elog(ERROR,"xpath_table must be called as a table function");
I have attached an edited copy of xpath.c with these changes, if you would like to work with it.
Regards,
George
----- Original Message -----
From: Theo Galanakis
To: 'pgsql-sql@postgresql.org'
Sent: Sunday, February 20, 2005 11:28 PM
Subject: FW: [SQL] Working with XML.
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.c
xpath.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 once
xpath.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
Attachments:
xpath.c (text/plain, 20.8K) download
/* Parser interface for DOM-based parser (libxml) rather than
stream-based SAX-type parser */
/*#include "errcodes.h"*/
#include "postgres.h"
#include "fmgr.h"
#include "executor/spi.h"
#include "funcapi.h"
#include "miscadmin.h"
#include "lib/stringinfo.h"
/* libxml includes */
#include <libxml/xpath.h>
#include <libxml/tree.h>
#include <libxml/xmlmemory.h>
#include <libxml/xmlerror.h>
#include <libxml/parserInternals.h>
/* declarations */
static void *pgxml_palloc(size_t size);
static void *pgxml_repalloc(void *ptr, size_t size);
static void pgxml_pfree(void *ptr);
static char *pgxml_pstrdup(const char *string);
static void pgxml_errorHandler(void *ctxt, const char *msg,...);
void elog_error(int level, char *explain, int force);
void pgxml_parser_init(void);
static xmlChar *pgxmlNodeSetToText(xmlNodeSetPtr nodeset,
xmlChar * toptagname, xmlChar * septagname,
xmlChar * plainsep);
text *pgxml_result_to_text(xmlXPathObjectPtr res, xmlChar * toptag,
xmlChar * septag, xmlChar * plainsep);
xmlChar *pgxml_texttoxmlchar(text *textstring);
static xmlXPathObjectPtr pgxml_xpath(text *document, xmlChar * xpath);
Datum xml_valid(PG_FUNCTION_ARGS);
Datum xpath_nodeset(PG_FUNCTION_ARGS);
Datum xpath_string(PG_FUNCTION_ARGS);
Datum xpath_number(PG_FUNCTION_ARGS);
Datum xpath_bool(PG_FUNCTION_ARGS);
Datum xpath_list(PG_FUNCTION_ARGS);
Datum xpath_table(PG_FUNCTION_ARGS);
/* Global variables */
char *errbuf; /* per line error buffer */
char *pgxml_errorMsg = NULL; /* overall error message */
/* Convenience macros */
#define GET_TEXT(cstrp) DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(cstrp)))
#define GET_STR(textp) DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(textp)))
#define ERRBUF_SIZE 200
/* memory handling passthrough functions (e.g. palloc, pstrdup are
currently macros, and the others might become so...) */
static void *
pgxml_palloc(size_t size)
{
/* elog(DEBUG1,"Alloc %d in CMC %x",size,CurrentMemoryContext); */
return palloc(size);
}
static void *
pgxml_repalloc(void *ptr, size_t size)
{
/* elog(DEBUG1,"ReAlloc in CMC %x",CurrentMemoryContext);*/
return repalloc(ptr, size);
}
static void
pgxml_pfree(void *ptr)
{
/* elog(DEBUG1,"Free in CMC %x",CurrentMemoryContext); */
return pfree(ptr);
}
static char *
pgxml_pstrdup(const char *string)
{
return pstrdup(string);
}
/* The error handling function. This formats an error message and sets
* a flag - an ereport will be issued prior to return
*/
static void
pgxml_errorHandler(void *ctxt, const char *msg,...)
{
va_list args;
va_start(args, msg);
vsnprintf(errbuf, ERRBUF_SIZE, msg, args);
va_end(args);
/* Now copy the argument across */
if (pgxml_errorMsg == NULL)
pgxml_errorMsg = pstrdup(errbuf);
else
{
int32 xsize = strlen(pgxml_errorMsg);
pgxml_errorMsg = repalloc(pgxml_errorMsg,
(size_t) (xsize + strlen(errbuf) + 1));
strncpy(&pgxml_errorMsg[xsize - 1], errbuf, strlen(errbuf));
pgxml_errorMsg[xsize + strlen(errbuf) - 1] = '\0';
}
memset(errbuf, 0, ERRBUF_SIZE);
}
/* This function reports the current message at the level specified */
void
elog_error(int level, char *explain, int force)
{
if (force || (pgxml_errorMsg != NULL))
{
if (pgxml_errorMsg == NULL)
{
/*
ereport(level, (errcode(ERRCODE_EXTERNAL_ROUTINE_EXCEPTION),
errmsg(explain)));
*/
elog(ERROR,explain);
}
else
{
/*
ereport(level, (errcode(ERRCODE_EXTERNAL_ROUTINE_EXCEPTION),
errmsg("%s:%s", explain, pgxml_errorMsg)));
pfree(pgxml_errorMsg);
*/
elog(ERROR,"%s:%s", explain, pgxml_errorMsg);
}
}
}
void
pgxml_parser_init()
{
/*
* This code could also set parser settings from user-supplied info.
* Quite how these settings are made is another matter :)
*/
xmlMemSetup(pgxml_pfree, pgxml_palloc, pgxml_repalloc, pgxml_pstrdup);
xmlInitParser();
xmlSetGenericErrorFunc(NULL, pgxml_errorHandler);
xmlSubstituteEntitiesDefault(1);
xmlLoadExtDtdDefaultValue = 1;
pgxml_errorMsg = NULL;
errbuf = palloc(200);
memset(errbuf, 0, 200);
}
/* Returns true if document is well-formed */
PG_FUNCTION_INFO_V1(xml_valid);
Datum
xml_valid(PG_FUNCTION_ARGS)
{
/* called as xml_valid(document) */
xmlDocPtr doctree;
text *t = PG_GETARG_TEXT_P(0); /* document buffer */
int32 docsize = VARSIZE(t) - VARHDRSZ;
pgxml_parser_init();
doctree = xmlParseMemory((char *) VARDATA(t), docsize);
if (doctree == NULL)
{
xmlCleanupParser();
PG_RETURN_BOOL(false); /* i.e. not well-formed */
}
xmlCleanupParser();
xmlFreeDoc(doctree);
PG_RETURN_BOOL(true);
}
static xmlChar
*
pgxmlNodeSetToText(xmlNodeSetPtr nodeset,
xmlChar * toptagname,
xmlChar * septagname,
xmlChar * plainsep)
{
/* Function translates a nodeset into a text representation */
/*
* iterates over each node in the set and calls xmlNodeDump to write
* it to an xmlBuffer -from which an xmlChar * string is returned.
*/
/* each representation is surrounded by <tagname> ... </tagname> */
/*
* plainsep is an ordinary (not tag) seperator - if used, then nodes
* are cast to string as output method
*/
xmlBufferPtr buf;
xmlChar *result;
int i;
buf = xmlBufferCreate();
if ((toptagname != NULL) && (xmlStrlen(toptagname) > 0))
{
xmlBufferWriteChar(buf, "<");
xmlBufferWriteCHAR(buf, toptagname);
xmlBufferWriteChar(buf, ">");
}
if (nodeset != NULL)
{
for (i = 0; i < nodeset->nodeNr; i++)
{
if (plainsep != NULL)
{
xmlBufferWriteCHAR(buf,
xmlXPathCastNodeToString(nodeset->nodeTab[i]));
/* If this isn't the last entry, write the plain sep. */
if (i < (nodeset->nodeNr) - 1)
xmlBufferWriteChar(buf, plainsep);
}
else
{
if ((septagname != NULL) && (xmlStrlen(septagname) > 0))
{
xmlBufferWriteChar(buf, "<");
xmlBufferWriteCHAR(buf, septagname);
xmlBufferWriteChar(buf, ">");
}
xmlNodeDump(buf,
nodeset->nodeTab[i]->doc,
nodeset->nodeTab[i],
1, 0);
if ((septagname != NULL) && (xmlStrlen(septagname) > 0))
{
xmlBufferWriteChar(buf, "</");
xmlBufferWriteCHAR(buf, septagname);
xmlBufferWriteChar(buf, ">");
}
}
}
}
if ((toptagname != NULL) && (xmlStrlen(toptagname) > 0))
{
xmlBufferWriteChar(buf, "</");
xmlBufferWriteCHAR(buf, toptagname);
xmlBufferWriteChar(buf, ">");
}
result = xmlStrdup(buf->content);
xmlBufferFree(buf);
return result;
}
/* Translate a PostgreSQL "varlena" -i.e. a variable length parameter
* into the libxml2 representation
*/
xmlChar *
pgxml_texttoxmlchar(text *textstring)
{
xmlChar *res;
int32 txsize;
txsize = VARSIZE(textstring) - VARHDRSZ;
res = (xmlChar *) palloc(txsize + 1);
memcpy((char *) res, VARDATA(textstring), txsize);
res[txsize] = '\0';
return res;
}
/* Public visible XPath functions */
/* This is a "raw" xpath function. Check that it returns child elements
* properly
*/
PG_FUNCTION_INFO_V1(xpath_nodeset);
Datum
xpath_nodeset(PG_FUNCTION_ARGS)
{
xmlChar *xpath,
*toptag,
*septag;
int32 pathsize;
text
*xpathsupp,
*xpres;
/* PG_GETARG_TEXT_P(0) is document buffer */
xpathsupp = PG_GETARG_TEXT_P(1); /* XPath expression */
toptag = pgxml_texttoxmlchar(PG_GETARG_TEXT_P(2));
septag = pgxml_texttoxmlchar(PG_GETARG_TEXT_P(3));
pathsize = VARSIZE(xpathsupp) - VARHDRSZ;
xpath = pgxml_texttoxmlchar(xpathsupp);
xpres = pgxml_result_to_text(
pgxml_xpath(PG_GETARG_TEXT_P(0), xpath),
toptag, septag, NULL);
/* xmlCleanupParser(); done by result_to_text routine */
pfree((void *) xpath);
if (xpres == NULL)
PG_RETURN_NULL();
PG_RETURN_TEXT_P(xpres);
}
/* The following function is almost identical, but returns the elements in */
/* a list. */
PG_FUNCTION_INFO_V1(xpath_list);
Datum
xpath_list(PG_FUNCTION_ARGS)
{
xmlChar *xpath,
*plainsep;
int32 pathsize;
text
*xpathsupp,
*xpres;
/* PG_GETARG_TEXT_P(0) is document buffer */
xpathsupp = PG_GETARG_TEXT_P(1); /* XPath expression */
plainsep = pgxml_texttoxmlchar(PG_GETARG_TEXT_P(2));
pathsize = VARSIZE(xpathsupp) - VARHDRSZ;
xpath = pgxml_texttoxmlchar(xpathsupp);
xpres = pgxml_result_to_text(
pgxml_xpath(PG_GETARG_TEXT_P(0), xpath),
NULL, NULL, plainsep);
/* xmlCleanupParser(); done by result_to_text routine */
pfree((void *) xpath);
if (xpres == NULL)
PG_RETURN_NULL();
PG_RETURN_TEXT_P(xpres);
}
PG_FUNCTION_INFO_V1(xpath_string);
Datum
xpath_string(PG_FUNCTION_ARGS)
{
xmlChar *xpath;
int32 pathsize;
text
*xpathsupp,
*xpres;
/* PG_GETARG_TEXT_P(0) is document buffer */
xpathsupp = PG_GETARG_TEXT_P(1); /* XPath expression */
pathsize = VARSIZE(xpathsupp) - VARHDRSZ;
/*
* We encapsulate the supplied path with "string()" = 8 chars + 1 for
* NUL at end
*/
/* We could try casting to string using the libxml function? */
xpath = (xmlChar *) palloc(pathsize + 9);
memcpy((char *) (xpath + 7), VARDATA(xpathsupp), pathsize);
strncpy((char *) xpath, "string(", 7);
xpath[pathsize + 7] = ')';
xpath[pathsize + 8] = '\0';
xpres = pgxml_result_to_text(
pgxml_xpath(PG_GETARG_TEXT_P(0), xpath),
NULL, NULL, NULL);
xmlCleanupParser();
pfree((void *) xpath);
if (xpres == NULL)
PG_RETURN_NULL();
PG_RETURN_TEXT_P(xpres);
}
PG_FUNCTION_INFO_V1(xpath_number);
Datum
xpath_number(PG_FUNCTION_ARGS)
{
xmlChar *xpath;
int32 pathsize;
text
*xpathsupp;
float4 fRes;
xmlXPathObjectPtr res;
/* PG_GETARG_TEXT_P(0) is document buffer */
xpathsupp = PG_GETARG_TEXT_P(1); /* XPath expression */
pathsize = VARSIZE(xpathsupp) - VARHDRSZ;
xpath = pgxml_texttoxmlchar(xpathsupp);
res = pgxml_xpath(PG_GETARG_TEXT_P(0), xpath);
pfree((void *) xpath);
if (res == NULL)
{
xmlCleanupParser();
PG_RETURN_NULL();
}
fRes = xmlXPathCastToNumber(res);
xmlCleanupParser();
if (xmlXPathIsNaN(fRes))
PG_RETURN_NULL();
PG_RETURN_FLOAT4(fRes);
}
PG_FUNCTION_INFO_V1(xpath_bool);
Datum
xpath_bool(PG_FUNCTION_ARGS)
{
xmlChar *xpath;
int32 pathsize;
text
*xpathsupp;
int bRes;
xmlXPathObjectPtr res;
/* PG_GETARG_TEXT_P(0) is document buffer */
xpathsupp = PG_GETARG_TEXT_P(1); /* XPath expression */
pathsize = VARSIZE(xpathsupp) - VARHDRSZ;
xpath = pgxml_texttoxmlchar(xpathsupp);
res = pgxml_xpath(PG_GETARG_TEXT_P(0), xpath);
pfree((void *) xpath);
if (res == NULL)
{
xmlCleanupParser();
PG_RETURN_BOOL(false);
}
bRes = xmlXPathCastToBoolean(res);
xmlCleanupParser();
PG_RETURN_BOOL(bRes);
}
/* Core function to evaluate XPath query */
xmlXPathObjectPtr
pgxml_xpath(text *document, xmlChar * xpath)
{
xmlDocPtr doctree;
xmlXPathContextPtr ctxt;
xmlXPathObjectPtr res;
xmlXPathCompExprPtr comppath;
int32 docsize;
docsize = VARSIZE(document) - VARHDRSZ;
pgxml_parser_init();
doctree = xmlParseMemory((char *) VARDATA(document), docsize);
if (doctree == NULL)
{ /* not well-formed */
return NULL;
}
ctxt = xmlXPathNewContext(doctree);
ctxt->node = xmlDocGetRootElement(doctree);
/* compile the path */
comppath = xmlXPathCompile(xpath);
if (comppath == NULL)
{
xmlCleanupParser();
xmlFreeDoc(doctree);
elog_error(ERROR, "XPath Syntax Error", 1);
return NULL;
}
/* Now evaluate the path expression. */
res = xmlXPathCompiledEval(comppath, ctxt);
xmlXPathFreeCompExpr(comppath);
if (res == NULL)
{
xmlXPathFreeContext(ctxt);
/* xmlCleanupParser(); */
xmlFreeDoc(doctree);
return NULL;
}
/* xmlFreeDoc(doctree); */
return res;
}
text
*
pgxml_result_to_text(xmlXPathObjectPtr res,
xmlChar * toptag,
xmlChar * septag,
xmlChar * plainsep)
{
xmlChar *xpresstr;
int32 ressize;
text *xpres;
if (res == NULL)
return NULL;
switch (res->type)
{
case XPATH_NODESET:
xpresstr = pgxmlNodeSetToText(res->nodesetval,
toptag,
septag, plainsep);
break;
case XPATH_STRING:
xpresstr = xmlStrdup(res->stringval);
break;
default:
elog(NOTICE, "Unsupported XQuery result: %d", res->type);
xpresstr = xmlStrdup("<unsupported/>");
}
/* Now convert this result back to text */
ressize = strlen(xpresstr);
xpres = (text *) palloc(ressize + VARHDRSZ);
memcpy(VARDATA(xpres), xpresstr, ressize);
VARATT_SIZEP(xpres) = ressize + VARHDRSZ;
/* Free various storage */
xmlCleanupParser();
/* xmlFreeDoc(doctree); -- will die at end of tuple anyway */
xmlFree(xpresstr);
elog_error(ERROR, "XPath error", 0);
return xpres;
}
/* xpath_table is a table function. It needs some tidying (as do the
* other functions here!
*/
PG_FUNCTION_INFO_V1(xpath_table);
Datum
xpath_table(PG_FUNCTION_ARGS)
{
/* SPI (input tuple) support */
SPITupleTable *tuptable;
HeapTuple spi_tuple;
TupleDesc spi_tupdesc;
/* Output tuple (tuplestore) support */
Tuplestorestate *tupstore = NULL;
TupleDesc ret_tupdesc;
HeapTuple ret_tuple;
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
AttInMetadata *attinmeta;
MemoryContext per_query_ctx;
MemoryContext oldcontext;
/* Function parameters */
char *pkeyfield = GET_STR(PG_GETARG_TEXT_P(0));
char *xmlfield = GET_STR(PG_GETARG_TEXT_P(1));
char *relname = GET_STR(PG_GETARG_TEXT_P(2));
char *xpathset = GET_STR(PG_GETARG_TEXT_P(3));
char *condition = GET_STR(PG_GETARG_TEXT_P(4));
char **values;
xmlChar **xpaths;
xmlChar *pos;
xmlChar *pathsep = "|";
int numpaths;
int ret;
int proc;
int i;
int j;
int rownr; /* For issuing multiple rows from one
* original document */
int had_values; /* To determine end of nodeset results */
StringInfo querysql;
/* We only have a valid tuple description in table function mode */
if (rsinfo->expectedDesc == NULL)
{
/*ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
errmsg("xpath_table must be called as a table function")));
*/
elog(ERROR,"xpath_table must be called as a table function");
}
/* The tuplestore must exist in a higher context than
* this function call (per_query_ctx is used) */
per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
oldcontext = MemoryContextSwitchTo(per_query_ctx);
/* Create the tuplestore - work_mem is the max in-memory size before a
* file is created on disk to hold it.
* NOTE - Changed work_mem to sortmem - Nov 22 - 04 - GW
*/
/*tupstore = tuplestore_begin_heap(true, false, work_mem);*/
tupstore = tuplestore_begin_heap(true, SortMem);
MemoryContextSwitchTo(oldcontext);
/* get the requested return tuple description */
ret_tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc);
/*
* At the moment we assume that the returned attributes make sense for
* the XPath specififed (i.e. we trust the caller). It's not fatal if
* they get it wrong - the input function for the column type will
* raise an error if the path result can't be converted into the
* correct binary representation.
*/
attinmeta = TupleDescGetAttInMetadata(ret_tupdesc);
/*
* We want to materialise because it means that we don't have to carry
* libxml2 parser state between invocations of this function
*/
/* check to see if caller supports us returning a tuplestore */
if (!rsinfo || !(rsinfo->allowedModes & SFRM_Materialize))
/*ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
errmsg("xpath_table requires Materialize mode, but it is not "
"allowed in this context")));
*/
elog(ERROR,"xpath_table requires Materialize mode, but it is not "
"allowed in this context");
/* Set return mode and allocate value space. */
rsinfo->returnMode = SFRM_Materialize;
rsinfo->setDesc = ret_tupdesc;
values = (char **) palloc(ret_tupdesc->natts * sizeof(char *));
xpaths = (xmlChar **) palloc(ret_tupdesc->natts * sizeof(xmlChar *));
/* Split XPaths. xpathset is a writable CString. */
/* Note that we stop splitting once we've done all needed for tupdesc */
numpaths = 0;
pos = xpathset;
do
{
xpaths[numpaths] = pos;
pos = strstr(pos, pathsep);
if (pos != NULL)
{
*pos = '\0';
pos++;
}
numpaths++;
} while ((pos != NULL) && (numpaths < (ret_tupdesc->natts - 1)));
/* Now build query */
querysql = makeStringInfo();
/* Build initial sql statement */
appendStringInfo(querysql, "SELECT %s, %s FROM %s WHERE %s",
pkeyfield,
xmlfield,
relname,
condition
);
if ((ret = SPI_connect()) < 0)
elog(ERROR, "xpath_table: SPI_connect returned %d", ret);
if ((ret = SPI_exec(querysql->data, 0)) != SPI_OK_SELECT)
elog(ERROR, "xpath_table: SPI execution failed for query %s", querysql->data);
proc = SPI_processed;
/* elog(DEBUG1,"xpath_table: SPI returned %d rows",proc); */
tuptable = SPI_tuptable;
spi_tupdesc = tuptable->tupdesc;
/* Switch out of SPI context */
MemoryContextSwitchTo(oldcontext);
/* Check that SPI returned correct result. If you put a comma into one of
* the function parameters, this will catch it when the SPI query returns
* e.g. 3 columns.
*/
if (spi_tupdesc->natts != 2)
{
/*ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("Expression returning multiple columns is not valid in parameter list"),
errdetail("Expected two columns in SPI result, got %d", spi_tupdesc->natts)));
*/
elog(ERROR,"Expression returning multiple columns is not valid in parameter list");
}
/* Setup the parser. Beware that this must happen in the same context as the
* cleanup - which means that any error from here on must do cleanup to
* ensure that the entity table doesn't get freed by being out of context.
*/
pgxml_parser_init();
/* For each row i.e. document returned from SPI */
for (i = 0; i < proc; i++)
{
char *pkey;
char *xmldoc;
xmlDocPtr doctree;
xmlXPathContextPtr ctxt;
xmlXPathObjectPtr res;
xmlChar *resstr;
xmlXPathCompExprPtr comppath;
/* Extract the row data as C Strings */
spi_tuple = tuptable->vals[i];
pkey = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
xmldoc = SPI_getvalue(spi_tuple, spi_tupdesc, 2);
/*
* Clear the values array, so that not-well-formed documents
* return NULL in all columns.
*/
/* Note that this also means that spare columns will be NULL. */
for (j = 0; j < ret_tupdesc->natts; j++)
values[j] = NULL;
/* Insert primary key */
values[0] = pkey;
/* Parse the document */
doctree = xmlParseMemory(xmldoc, strlen(xmldoc));
if (doctree == NULL)
{ /* not well-formed, so output all-NULL
* tuple */
ret_tuple = BuildTupleFromCStrings(attinmeta, values);
oldcontext = MemoryContextSwitchTo(per_query_ctx);
tuplestore_puttuple(tupstore, ret_tuple);
MemoryContextSwitchTo(oldcontext);
heap_freetuple(ret_tuple);
}
else
{
/* New loop here - we have to deal with nodeset results */
rownr = 0;
do
{
/* Now evaluate the set of xpaths. */
had_values = 0;
for (j = 0; j < numpaths; j++)
{
ctxt = xmlXPathNewContext(doctree);
ctxt->node = xmlDocGetRootElement(doctree);
xmlSetGenericErrorFunc(ctxt, pgxml_errorHandler);
/* compile the path */
comppath = xmlXPathCompile(xpaths[j]);
if (comppath == NULL)
{
xmlCleanupParser();
xmlFreeDoc(doctree);
elog_error(ERROR, "XPath Syntax Error", 1);
PG_RETURN_NULL(); /* Keep compiler happy */
}
/* Now evaluate the path expression. */
res = xmlXPathCompiledEval(comppath, ctxt);
xmlXPathFreeCompExpr(comppath);
if (res != NULL)
{
switch (res->type)
{
case XPATH_NODESET:
/* We see if this nodeset has enough nodes */
if ((res->nodesetval != NULL) && (rownr < res->nodesetval->nodeNr))
{
resstr =
xmlXPathCastNodeToString(res->nodesetval->nodeTab[rownr]);
had_values = 1;
}
else
resstr = NULL;
break;
case XPATH_STRING:
resstr = xmlStrdup(res->stringval);
break;
default:
elog(NOTICE, "Unsupported XQuery result: %d", res->type);
resstr = xmlStrdup("<unsupported/>");
}
/*
* Insert this into the appropriate column in the
* result tuple.
*/
values[j + 1] = resstr;
}
xmlXPathFreeContext(ctxt);
}
/* Now add the tuple to the output, if there is one. */
if (had_values)
{
ret_tuple = BuildTupleFromCStrings(attinmeta, values);
oldcontext = MemoryContextSwitchTo(per_query_ctx);
tuplestore_puttuple(tupstore, ret_tuple);
MemoryContextSwitchTo(oldcontext);
heap_freetuple(ret_tuple);
}
rownr++;
} while (had_values);
}
xmlFreeDoc(doctree);
pfree(pkey);
pfree(xmldoc);
}
xmlCleanupParser();
/* Needed to flag completeness in 7.3.1. 7.4 defines it as a no-op. */
tuplestore_donestoring(tupstore);
SPI_finish();
rsinfo->setResult = tupstore;
/*
* SFRM_Materialize mode expects us to return a NULL Datum. The actual
* tuples are in our tuplestore and passed back through
* rsinfo->setResult. rsinfo->setDesc is set to the tuple description
* that we actually used to build our tuples with, so the caller can
* verify we did what it was expecting.
*/
return (Datum) 0;
}
=
* Re: FW: Working with XML.
2005-02-21 05:28 FW: Working with XML. Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
@ 2005-02-21 20:36 ` Mirko Zeibig <mirko-postgresql@zeibig.net>
1 sibling, 0 replies; 13+ messages in thread
From: Mirko Zeibig @ 2005-02-21 20:36 UTC (permalink / raw)
To: pgsql-sql; +Cc: Theo.Galanakis@lonelyplanet.com.au
Theo Galanakis wrote:
> Hi,
> I have copied all the files manually from http
> <http://developer.postgresql.org/docs/pgsql/contrib/;
> ://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.c
> xpath.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 once
> xpath.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.
Hello Theo,
this has nothing to do with libxml2. `work_mem` is a variable declared
in one of the PostgreSQL include files. The `-I../../src/include`
directive in the compile statement above should point to the include-dir
of your PostgreSQL installation.
Regards
Mirko
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Working with XML.
@ 2005-02-22 13:45 George Weaver <gweaver@shaw.ca>
0 siblings, 0 replies; 13+ messages in thread
From: George Weaver @ 2005-02-22 13:45 UTC (permalink / raw)
To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; +Cc: pgsql-sql
MessageHi Theo,
I am not able to duplicate the problem you experienced. I tried the query you provided below using pgAdmin and psql on a 7.3.2 and an 8.0 database with success. I then copied all the rows and pasted them to the end so that I would have 100 rows, and the query worked as expected.
Perhaps the client you're using is causing the problem. Can you run the query in pgAdmin?
Regards,
George
----- Original Message -----
From: Theo Galanakis
To: 'George Weaver'
Cc: 'pgsql-sql@postgresql.org'
Sent: Monday, February 21, 2005 11:54 PM
Subject: RE: [SQL] Working with XML.
Thankyou George,
XML2 compiled ok... next stummbling block.. when I pass a very long XML string to xpath_string() it crashes the postgres server. Client receives a message like:
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
Below is the function call, a smaller xml string with say 40 records works fine, anything bigger crashes the server.
select xpath_string('<?xml version="1.0" encoding="UTF-8"?> <query columns="4" rows="50"><row><cola>774187</cola><colb>222663</colb><colc>2005-02-22 <snip>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Working with XML.
@ 2005-02-22 22:13 Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2005-02-23 14:59 ` Re: Working with XML. George Weaver <gweaver@shaw.ca>
0 siblings, 1 reply; 13+ messages in thread
From: Theo Galanakis @ 2005-02-22 22:13 UTC (permalink / raw)
To: George Weaver <gweaver@shaw.ca>; +Cc: pgsql-sql
George,
I have run this SP in Cold Fusion, PgAdmin and EMS PostgreSQL Manager 2,
with all the same results. We are currently using Redhat ES3 and Postgres
7.4.5.
Theo
-----Original Message-----
From: George Weaver [mailto:gweaver@shaw.ca]
Sent: Wednesday, 23 February 2005 12:46 AM
To: Theo Galanakis
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Working with XML.
Hi Theo,
I am not able to duplicate the problem you experienced. I tried the query
you provided below using pgAdmin and psql on a 7.3.2 and an 8.0 database
with success. I then copied all the rows and pasted them to the end so that
I would have 100 rows, and the query worked as expected.
Perhaps the client you're using is causing the problem. Can you run the
query in pgAdmin?
Regards,
George
----- Original Message -----
From: Theo Galanakis <mailto:Theo.Galanakis@lonelyplanet.com.au>
To: 'George Weaver' <mailto:gweaver@shaw.ca>
Cc: 'pgsql-sql@postgresql.org' <mailto:'pgsql-sql@postgresql.org'>
Sent: Monday, February 21, 2005 11:54 PM
Subject: RE: [SQL] Working with XML.
Thankyou George,
XML2 compiled ok... next stummbling block.. when I pass a very long XML
string to xpath_string() it crashes the postgres server. Client receives a
message like:
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
Below is the function call, a smaller xml string with say 40 records works
fine, anything bigger crashes the server.
select xpath_string('<?xml version="1.0" encoding="UTF-8"?> <query
columns="4"
rows="50"><row><cola>774187</cola><colb>222663</colb><colc>2005-02-22 <snip>
______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Working with XML.
2005-02-22 22:13 Re: Working with XML. Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
@ 2005-02-23 14:59 ` George Weaver <gweaver@shaw.ca>
0 siblings, 0 replies; 13+ messages in thread
From: George Weaver @ 2005-02-23 14:59 UTC (permalink / raw)
To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; +Cc: pgsql-sql
MessageHi Theo,
Hmm. Well we're getting into territory that's over my head. I've simply been a user of xml2 and do not know much about its inner workings. Just out of curiosity, what is the size of Sort_Mem in your postgresql.conf?
Regards,
George
----- Original Message -----
From: Theo Galanakis
To: 'George Weaver'
Cc: 'pgsql-sql@postgresql.org'
Sent: Tuesday, February 22, 2005 4:13 PM
Subject: Re: [SQL] Working with XML.
George,
I have run this SP in Cold Fusion, PgAdmin and EMS PostgreSQL Manager 2, with all the same results. We are currently using Redhat ES3 and Postgres 7.4.5.
Theo
-----Original Message-----
From: George Weaver [mailto:gweaver@shaw.ca]
Sent: Wednesday, 23 February 2005 12:46 AM
To: Theo Galanakis
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Working with XML.
Hi Theo,
I am not able to duplicate the problem you experienced. I tried the query you provided below using pgAdmin and psql on a 7.3.2 and an 8.0 database with success. I then copied all the rows and pasted them to the end so that I would have 100 rows, and the query worked as expected.
Perhaps the client you're using is causing the problem. Can you run the query in pgAdmin?
Regards,
George
----- Original Message -----
From: Theo Galanakis
To: 'George Weaver'
Cc: 'pgsql-sql@postgresql.org'
Sent: Monday, February 21, 2005 11:54 PM
Subject: RE: [SQL] Working with XML.
Thankyou George,
XML2 compiled ok... next stummbling block.. when I pass a very long XML string to xpath_string() it crashes the postgres server. Client receives a message like:
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
Below is the function call, a smaller xml string with say 40 records works fine, anything bigger crashes the server.
select xpath_string('<?xml version="1.0" encoding="UTF-8"?> <query columns="4" rows="50"><row><cola>774187</cola><colb>222663</colb><colc>2005-02-22 <snip>
______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Working with XML.
@ 2005-02-23 22:42 Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
0 siblings, 0 replies; 13+ messages in thread
From: Theo Galanakis @ 2005-02-23 22:42 UTC (permalink / raw)
To: George Weaver <gweaver@shaw.ca>; +Cc: pgsql-sql
Hi George,
I sent out a message shortly after this that didn't get through. However
it's good news, I rebooted the server and it appears to work fine now.
Thankyou for all your help, appreciated!!
Theo
-----Original Message-----
From: George Weaver [mailto:gweaver@shaw.ca]
Sent: Thursday, 24 February 2005 2:00 AM
To: Theo Galanakis
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Working with XML.
Hi Theo,
Hmm. Well we're getting into territory that's over my head. I've simply
been a user of xml2 and do not know much about its inner workings. Just out
of curiosity, what is the size of Sort_Mem in your postgresql.conf?
Regards,
George
----- Original Message -----
From: Theo Galanakis <mailto:Theo.Galanakis@lonelyplanet.com.au>
To: 'George Weaver' <mailto:gweaver@shaw.ca>
Cc: 'pgsql-sql@postgresql.org' <mailto:'pgsql-sql@postgresql.org'>
Sent: Tuesday, February 22, 2005 4:13 PM
Subject: Re: [SQL] Working with XML.
George,
I have run this SP in Cold Fusion, PgAdmin and EMS PostgreSQL Manager 2,
with all the same results. We are currently using Redhat ES3 and Postgres
7.4.5.
Theo
-----Original Message-----
From: George Weaver [mailto:gweaver@shaw.ca]
Sent: Wednesday, 23 February 2005 12:46 AM
To: Theo Galanakis
Cc: pgsql-sql@postgresql.org <mailto:pgsql-sql@postgresql.org>
Subject: Re: [SQL] Working with XML.
Hi Theo,
I am not able to duplicate the problem you experienced. I tried the query
you provided below using pgAdmin and psql on a 7.3.2 and an 8.0 database
with success. I then copied all the rows and pasted them to the end so that
I would have 100 rows, and the query worked as expected.
Perhaps the client you're using is causing the problem. Can you run the
query in pgAdmin?
Regards,
George
----- Original Message -----
From: Theo Galanakis <mailto:Theo.Galanakis@lonelyplanet.com.au>
To: 'George Weaver' <mailto:gweaver@shaw.ca>
Cc: 'pgsql-sql@postgresql.org' <mailto:'pgsql-sql@postgresql.org'>
Sent: Monday, February 21, 2005 11:54 PM
Subject: RE: [SQL] Working with XML.
Thankyou George,
XML2 compiled ok... next stummbling block.. when I pass a very long XML
string to xpath_string() it crashes the postgres server. Client receives a
message like:
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
Below is the function call, a smaller xml string with say 40 records works
fine, anything bigger crashes the server.
select xpath_string('<?xml version="1.0" encoding="UTF-8"?> <query
columns="4"
rows="50"><row><cola>774187</cola><colb>222663</colb><colc>2005-02-22 <snip>
______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2005-02-23 22:42 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-14 05:48 Working with XML. Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2005-02-14 13:38 ` George Weaver <gweaver@shaw.ca>
2005-02-15 03:46 Re: Working with XML. Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2005-02-15 13:36 ` George Weaver <gweaver@shaw.ca>
2005-02-16 01:32 Re: Working with XML. George Weaver <gweaver@shaw.ca>
2005-02-21 00:52 FW: Working with XML. Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2005-02-21 05:28 FW: Working with XML. Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2005-02-21 14:55 ` George Weaver <gweaver@shaw.ca>
2005-02-21 20:36 ` Mirko Zeibig <mirko-postgresql@zeibig.net>
2005-02-22 13:45 Re: Working with XML. George Weaver <gweaver@shaw.ca>
2005-02-22 22:13 Re: Working with XML. Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2005-02-23 14:59 ` George Weaver <gweaver@shaw.ca>
2005-02-23 22:42 Re: Working with XML. Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox