|
Hi Theo,
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 -----
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
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 -----
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
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()'.
Regards,
George
----- Original Message -----
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.
|
|
