Working with XML. - Mailing list pgsql-sql

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