From: George Weaver <gweaver@shaw.ca>
To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
To: pgsql-sql@postgresql.org
Subject: Re: Working with XML.
Date: Mon, 14 Feb 2005 07:38:46 -0600
Message-ID: <000f01c5129a$8490c1b0$6400a8c0@Dell4500> (raw)
References: <D1444817B78AB546BF2896C2B70E7F04371EE0@ganesh.au.lpint.net>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gweaver@shaw.ca, Theo.Galanakis@lonelyplanet.com.au
Subject: Re: Working with XML.
In-Reply-To: <000f01c5129a$8490c1b0$6400a8c0@Dell4500>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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