Re: [SQL] Working with XML.
Title: Working with XML.
Hi Theo,
I am not aware of any means of passing xml to
stored procedures, apart from writing your own function to parse the
xml.
In regard to your second question - how to
access the second record - try using a more explicit xpath query
incorporating a "where" component. For example, if you wanted to access
the second row based upon the value of cola, you could use
'//query/[EMAIL PROTECTED]'525887']/text()'. Or you could specify the position of
the record if you know its position: '//query/row[2]/text()'.
Microsoft has a very good reference on xpath
expressions: http://msdn.microsoft.com/library/default.asp?url="">.
Regards,
George
- Original Message -
From:
Theo Galanakis
To: pgsql-sql@postgresql.org
Sent: Sunday, February 13, 2005 11:48
PM
Subject: [SQL] Working with XML.
Hi Folks,
Is there a way to pass in an xml string into a
stored proc and thenplace this into a temp table?
I use to be able to do this in sql server, it was
quite handy as I could call one stored proc to update multiple records, here
is a sample in sql server:
select CoverTypeID, ItemSQ, SituationID,
ItemDescription, CoverAmount From
OpenXML ( @XmlHandle, '/cover/covertype/item',1 ) With
( CoverTypeID int '../@id',
ItemSQ int '@id',
SituationID int '@situationID',
ItemDescription varchar(100) '@description',
CoverAmount money '@amount' )
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( '284122789648{ts
''2005-02-14
16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65525887493253{ts
''2005-02-14
16:13:18''}6uydk442uz247ga45kpys7htkxznkn8La31qhn942wu2cu2pdr25mv2nup2zh3vcbh3c4vdauak3p3w093cvtkeyga692b455cr3'
,'//query/row/cola/text()','','')
__This
email, including attachments, is intended only for the addresseeand
may be confidential, privileged and subject to copyright. If youhave
received this email in error, please advise the sender and deleteit.
If you are not the intended recipient of this email, you must
notuse, 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.
Re: [SQL] copy rows
I have not been too deep into 8.0, but AFAIK from the 7.x releases, the only way to do things like you describe, is do it the hard way: use SELECT INTO to get the field values you want into variables modify the necessary field values do the INSERT via Executing A Dynamic Command Inserting a record type variable has been brought up before, but IIRC this is not possible. You may like to search the archives. HTH Regards, Christoph [EMAIL PROTECTED] wrote: > > Hi, > > What is the simplest solution in plpgsql to copy some rows in a table? > > I would like to do something like: > > select some rows > do for each row >skip serial field \ >modify one field |-- what is the simplest way for this? >insert as new row / > > Is it possible to insert a record type variable? If yes, how to skip a > field? > > Daniel > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] triggers
update trigger working on same table??? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] triggers
On Mon, Feb 14, 2005 at 05:42:27PM +0200, ALÝ ÇELÝK wrote: > > update trigger working on same table??? Could you be more specific, please? What are you trying to do? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] triggers
update trigger working on same table??? If an UPDATE trigger does an update on its own table, it can trigger itself and explode... ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Working with XML.
Title: Message
Thanks
George.
How do you get an attributes value the following returns
the attribute tag. i.e. rows="100", all i want is the 100.
select pgxml_xpath('284122789648{ts
''2005-02-14
16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts
''2005-02-14
16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts
''2005-02-14
16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r','//query/@rows','','')
Theo
-Original Message-From: George Weaver
[mailto:[EMAIL PROTECTED] Sent: Tuesday, 15 February 2005 12:39
AMTo: Theo Galanakis; pgsql-sql@postgresql.orgSubject:
Re: [SQL] Working with XML.
Hi Theo,
I am not aware of any means of passing xml to
stored procedures, apart from writing your own function to parse the
xml.
In regard to your second question - how to
access the second record - try using a more explicit xpath query
incorporating a "where" component. For example, if you wanted to access
the second row based upon the value of cola, you could use
'//query/[EMAIL PROTECTED]'525887']/text()'. Or you could specify the position
of the record if you know its position: '//query/row[2]/text()'.
Microsoft has a very good reference on xpath
expressions: http://msdn.microsoft.com/library/default.asp?url="">.
Regards,
George
- Original Message -
From:
Theo Galanakis
To: pgsql-sql@postgresql.org
Sent: Sunday, February 13, 2005 11:48
PM
Subject: [SQL] Working with XML.
Hi Folks,
Is there a way to pass in an xml string into a
stored proc and thenplace this into a temp table?
I use to be able to do this in sql server, it was
quite handy as I could call one stored proc to update multiple records, here
is a sample in sql server:
select CoverTypeID, ItemSQ, SituationID,
ItemDescription, CoverAmount From
OpenXML ( @XmlHandle, '/cover/covertype/item',1 )
With ( CoverTypeID int '../@id',
ItemSQ int '@id',
SituationID int '@situationID',
ItemDescription varchar(100) '@description',
CoverAmount money '@amount' )
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( '284122789648{ts
''2005-02-14
16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65525887493253{ts
''2005-02-14
16:13:18''}6uydk442uz247ga45kpys7htkxznkn8La31qhn942wu2cu2pdr25mv2nup2zh3vcbh3c4vdauak3p3w093cvtkeyga692b455cr3'
,'//query/row/cola/text()','','')
__This
email, including attachments, is intended only for the
addresseeand may be confidential, privileged and subject to
copyright. If youhave received this email in error, please advise
the sender and deleteit. If you are not the intended recipient of
this email, you must notuse, 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.
