FW: 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/
<http://developer.postgresql.org/docs/pgsql/contrib/> , it there ftp access
or cvs access to contrib?

Theo

-----Original Message-----
From: George Weaver [mailto:gweaver(at)shaw(dot)ca]
Sent: Wednesday, 16 February 2005 12:37 AM
To: Theo Galanakis
Cc: pgsql-sql(at)postgresql(dot)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><ro
w><cola>24
3151</cola><colb>750292</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>2d4mat2Lp
tmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrv
yu4k7ng4oo
uihx0</cold></row><row><cola>764929</cola><colb>641215</colb><colc>{ts
''2005-02-14 16
:13:18''}</colc><cold>1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp
9p0s7obzr2
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><ro
w><cola>24
3151</cola><colb>750292</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>2d4mat2Lp
tmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrv
yu4k7ng4oo
uihx0</cold></row><row><cola>764929</cola><colb>641215</colb><colc>{ts
''2005-02-14 16
:13:18''}</colc><cold>1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp
9p0s7obzr2
7txvknn92m0ps4riqqx7puqoLn2wpmm2z50r</cold></row></query>'
jan28-05(# ,'//row[cola=284122]/colb') as colb;

colb
--------
789648
(1 row)

HTH,

George

----- Original Message -----
From: Theo Galanakis <mailto:Theo(dot)Galanakis(at)lonelyplanet(dot)com(dot)au>
To: 'George Weaver' <mailto:gweaver(at)shaw(dot)ca>
Cc: 'pgsql-sql(at)postgresql(dot)org' <mailto:'pgsql-sql(at)postgresql(dot)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>unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5w
ya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</cold></row><row><cola>2431
51</cola><colb>750292</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3L
q05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0</cold></row><row><cola>7649
29</cola><colb>641215</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLs
vp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r</cold></row></query>'
,'//query/@rows','','')

Theo

-----Original Message-----
From: George Weaver [mailto:gweaver(at)shaw(dot)ca]
Sent: Tuesday, 15 February 2005 12:39 AM
To: Theo Galanakis; pgsql-sql(at)postgresql(dot)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[(at)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
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsdk/htm
l/xmrefxpathsyntax.asp> .

Regards,
George

----- Original Message -----
From: Theo Galanakis <mailto:Theo(dot)Galanakis(at)lonelyplanet(dot)com(dot)au>
To: pgsql-sql(at)postgresql(dot)org <mailto:pgsql-sql(at)postgresql(dot)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>unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5w
ya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</cold></row<row><cola>52588
7</cola><colb>493253</colb><colc>{ts ''2005-02-14
16:13:18''}</colc><cold>6uydk442uz247ga45kpys7htkxznkn8La31qhn942wu2cu2pdr25
mv2nup2zh3vcbh3c4vdauak3p3w093cvtkeyga692b455cr3</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.