Thread: Working with XML.

<p><font face="Arial" size="2">Hi Folks,</font><p><font face="Arial" size="2">Is there a way to pass in an xml string
intoa stored proc and thenplace this into a temp table? </font><p><font face="Arial" size="2">I use to be able to do
thisin sql server, it was quite handy as I could call one stored proc to update multiple records, here is a sample in
sqlserver:</font><p><font face="Arial" size="2">select CoverTypeID, ItemSQ, SituationID, ItemDescription, CoverAmount
</font><br/><font face="Arial" size="2">From  OpenXML ( @XmlHandle, '/cover/covertype/item',1 ) </font><br /><font
face="Arial"size="2">            With ( CoverTypeID int '../@id', </font><br /><font face="Arial"
size="2">                     ItemSQ int '@id', </font><br />                <font face="Arial" size="2">     
SituationIDint '@situationID',</font><br /><font face="Arial" size="2">                      ItemDescription
varchar(100)'@description',</font><br />                <font face="Arial" size="2">      CoverAmount money '@amount'
)</font><p><fontface="Arial" size="2">I have managed to get get pgxml_xpath working, however Im not sure how to access
specificrows in an xml document. E.g below there are two records, how do I access the second record, the following
returnsboth ,'//query/row/cola values being (284122,525887):</font><p><font face="Arial" size="2">select </font><br
/><fontface="Arial" size="2">pgxml_xpath(</font><br /><font face="Arial" size="2">'<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>'</font><p><font
face="Arial"size="2">,'//query/row/cola/text()','','')</font><table><tr><td bgcolor="#ffffff"><font
color="#000000">______________________________________________________________________<br/> This email, including
attachments,is intended only for the addressee<br /> and may be confidential, privileged and subject to copyright. If
you<br/> have received this email in error, please advise the sender and delete<br /> it. If you are not the intended
recipientof this email, you must not<br /> use, copy or disclose its content to anyone. You must not copy or <br />
communicateto others content that is confidential or subject to <br /> copyright, unless you have the consent of the
contentowner.<br /></font></td></tr></table> 

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

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

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

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

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

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>

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

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

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.