X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 39DFF8B9B3E for ; Wed, 16 Feb 2005 01:33:10 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 57198-02 for ; Wed, 16 Feb 2005 01:32:32 +0000 (GMT) Received: from pd6mo1no.prod.shaw.ca (idcmail-mo2no.cg.shawcable.net [64.59.134.9]) by svr1.postgresql.org (Postfix) with ESMTP id 939B68B9D70 for ; Wed, 16 Feb 2005 01:32:29 +0000 (GMT) Received: from pd8mr7no.prod.shaw.ca (pd8mr7no-qfe2.prod.shaw.ca [10.0.144.228]) by l-daemon (Sun ONE Messaging Server 6.0 HotFix 1.01 (built Mar 15 2004)) with ESMTP id <0IBZ000T3DLV8OE0@l-daemon> for pgsql-sql@postgresql.org; Tue, 15 Feb 2005 18:32:19 -0700 (MST) Received: from pn7ml1no.prod.shaw.ca ([10.0.149.110]) by pd8mr7no.prod.shaw.ca (Sun ONE Messaging Server 6.0 HotFix 1.01 (built Mar 15 2004)) with ESMTP id <0IBZ00M0MDLVMV70@pd8mr7no.prod.shaw.ca> for pgsql-sql@postgresql.org; Tue, 15 Feb 2005 18:32:19 -0700 (MST) Received: from Dell4500 (S01060004236fc1f7.wp.shawcable.net [24.77.13.13]) by l-daemon (iPlanet Messaging Server 5.2 HotFix 1.18 (built Jul 28 2003)) with SMTP id <0IBZ0071EDLONL@l-daemon> for pgsql-sql@postgresql.org; Tue, 15 Feb 2005 18:32:19 -0700 (MST) Date: Tue, 15 Feb 2005 19:32:17 -0600 From: George Weaver Subject: Re: Working with XML. To: Theo Galanakis Cc: pgsql-sql@postgresql.org Message-id: <00b401c513c7$5ccc7510$6400a8c0@Dell4500> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Mailer: Microsoft Outlook Express 6.00.2900.2180 Content-type: multipart/alternative; boundary="----=_NextPart_000_00B1_01C51395.0F264BB0" X-Priority: 3 X-MSMail-priority: Normal References: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.832 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, HTML_50_60, HTML_MESSAGE, HTML_TAG_EXIST_TBODY X-Spam-Level: X-Archive-Number: 200502/155 X-Sequence-Number: 20266 This is a multi-part message in MIME format. ------=_NextPart_000_00B1_01C51395.0F264BB0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable MessageHi Theo, You can find the source code for xml2 it in the 8 source tree = http://www.postgresql.org/download/. 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 -----=20 From: Theo Galanakis=20 To: 'George Weaver'=20 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 -----Original Message----- From: George Weaver [mailto:gweaver@shaw.ca]=20 Sent: Wednesday, 16 February 2005 12:37 AM To: Theo Galanakis Cc: pgsql-sql@postgresql.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=3D# select xpath_string( jan28-05(# '284122789648< colc>{ts ''2005-02-14 = 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1 = d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65= 24 3151750292{ts ''2005-02-14 = 16:13:18''}2d4mat2Lp = tmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5= xrvyu4k7ng4oo = uihx0764929641215{ts = ''2005-02-14 16 = :13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkL= svp9p0s7obzr2 7txvknn92m0ps4riqqx7puqoLn2wpmm2z50r' jan28-05(# ,'//query/@rows') as rows; rows ------ 100 (1 row) Another example: jan28-05=3D# select xpath_string( jan28-05(# '284122789648< colc>{ts ''2005-02-14 = 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1 = d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65= 24 3151750292{ts ''2005-02-14 = 16:13:18''}2d4mat2Lp = tmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5= xrvyu4k7ng4oo = uihx0764929641215{ts = ''2005-02-14 16 = :13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkL= svp9p0s7obzr2 7txvknn92m0ps4riqqx7puqoLn2wpmm2z50r' jan28-05(# ,'//row[cola=3D284122]/colb') as colb; colb -------- 789648 (1 row) HTH, George ----- Original Message -----=20 From: Theo Galanakis=20 To: 'George Weaver'=20 Cc: 'pgsql-sql@postgresql.org'=20 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=3D"100", all i want is the 100. select=20 pgxml_xpath( '284122789648{ts = ''2005-02-14 = 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8= h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts ''2005-02-14 = 16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25= b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts ''2005-02-14 = 16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nk= kLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r'= ,'//query/@rows','','') Theo -----Original Message----- From: George Weaver [mailto:gweaver@shaw.ca]=20 Sent: Tuesday, 15 February 2005 12:39 AM To: Theo Galanakis; pgsql-sql@postgresql.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[@cola=3D'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=3D/library/en-us/xmlsdk= /html/xmrefxpathsyntax.asp. Regards, George ----- Original Message -----=20 From: Theo Galanakis=20 To: pgsql-sql@postgresql.org=20 Sent: Sunday, February 13, 2005 11:48 PM Subject: [SQL] Working with XML. Hi Folks,=20 Is there a way to pass in an xml string into a stored proc and = thenplace this into a temp table?=20 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=20 From OpenXML ( @XmlHandle, '/cover/covertype/item',1 )=20 With ( CoverTypeID int '../@id',=20 ItemSQ int '@id',=20 SituationID int '@situationID',=20 ItemDescription varchar(100) = '@description',=20 CoverAmount money '@amount' )=20 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=20 pgxml_xpath(=20 '284122789648{ts = ''2005-02-14 = 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8= h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65525887493253{ts ''2005-02-14 = 16:13:18''}6uydk442uz247ga45kpys7htkxznkn8La31qhn942wu2cu2pd= r25mv2nup2zh3vcbh3c4vdauak3p3w093cvtkeyga692b455cr3'= ,'//query/row/cola/text()','','')=20 = ______________________________________________________________________ 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=20 communicate to others content that is confidential or = subject to=20 copyright, unless you have the consent of the content = owner. =20 ------=_NextPart_000_00B1_01C51395.0F264BB0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message
Hi Theo,
 
You can find the source code for xml2 = it in the 8=20 source tree http://www.postgresql.org/do= wnload/.
 
If you're working with an earlier = version of=20 PostgreSQL than 8, you may have to make some modifications to the = contrib code=20 to get it to compile and link properly - I should be able to help you = with=20 that.  I don't think you can use it on any version earlier than=20 7.2.
 
Regards,
George
----- Original Message -----
From:=20 Theo Galanakis =
Sent: Tuesday, February 15, = 2005 4:30=20 PM
Subject: RE: [SQL] Working with = XML.

Hi=20 George,
 
    Thanks for your help once = again.
 
    However I cant seem to find XML2, the contrib = package for=20 RedHat ES3 I downloaded only has xml. Where can I find the = compiled or=20 source code. The only link I have is http://developer= .postgresql.org/docs/pgsql/contrib/ ,=20 it there ftp access or cvs access to contrib?
 
Theo
-----Original Message-----
From: = George Weaver=20 [mailto:gweaver@shaw.ca]
Sent: Wednesday, 16 February = 2005 12:37=20 AM
To: Theo Galanakis
Cc:=20 pgsql-sql@postgresql.org
Subject: Re: [SQL] Working with=20 XML.

Hi Theo,
 
I'm not sure if it can be done with = the xml=20 contrib module.  You may want to install and work with the xml2 = contrib=20 module, which is more recent, has more extensive = capabilities, and is=20 easier to work with.
 
It will give you the result you=20 want:
 
jan28-05=3D# select = xpath_string(
jan28-05(#=20 '<query columns=3D"4"=20 = rows=3D"100"><row><cola>284122</cola><colb>789= 648</colb><
colc>{ts=20 ''2005-02-14=20 = 16:13:18''}</colc><cold>unbnda8m5946z55sgi1xco34h1q9tLonoys3n= yk1
d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</c= old></row><row><cola>24
3151</cola><colb= >750292</colb><colc>{ts=20 ''2005-02-14=20 = 16:13:18''}</colc><cold>2d4mat2Lp
tmdydtgqnc5vnuzxgqxiykqn= 1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oo
uihx0&l= t;/cold></row><row><cola>764929</cola><colb= >641215</colb><colc>{ts=20 ''2005-02-14=20 = 16
:13:18''}</colc><cold>1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4e= t41podr7izcro4nkkLsvp9p0s7obzr2
7txvknn92m0ps4riqqx7puqoLn2wpmm2z50r&l= t;/cold></row></query>'
jan28-05(#=20 ,'//query/@rows') as rows;

 rows
------
 100
(1 row)
 
Another example:
 
jan28-05=3D# select xpath_string(
jan28-05(# '<query = columns=3D"4"=20 = rows=3D"100"><row><cola>284122</cola><colb>789= 648</colb><
colc>{ts=20 ''2005-02-14=20 = 16:13:18''}</colc><cold>unbnda8m5946z55sgi1xco34h1q9tLonoys3n= yk1
d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</c= old></row><row><cola>24
3151</cola><colb= >750292</colb><colc>{ts=20 ''2005-02-14=20 = 16:13:18''}</colc><cold>2d4mat2Lp
tmdydtgqnc5vnuzxgqxiykqn= 1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oo
uihx0&l= t;/cold></row><row><cola>764929</cola><colb= >641215</colb><colc>{ts=20 ''2005-02-14=20 = 16
:13:18''}</colc><cold>1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4e= t41podr7izcro4nkkLsvp9p0s7obzr2
7txvknn92m0ps4riqqx7puqoLn2wpmm2z50r&l= t;/cold></row></query>'
jan28-05(#=20 ,'//row[cola=3D284122]/colb') as colb;

  colb
--------
 789648
(1 row)
 
HTH,
 
George
----- Original Message ----- =
From:=20 Theo = Galanakis
Sent: Monday, February 14, = 2005 9:46=20 PM
Subject: RE: [SQL] Working = with=20 XML.

Thanks George.
 
    How do you get an attributes value the = following=20 returns the attribute tag. i.e. rows=3D"100", all i want is the=20 100.
   select
pgxml_xpath(
'<query = columns=3D"4"=20 = rows=3D"100"><row><cola>284122</cola><colb>789= 648</colb><colc>{ts=20 ''2005-02-14=20 = 16:13:18''}</colc><cold>unbnda8m5946z55sgi1xco34h1q9tLonoys3n= yk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</cold&= gt;</row><row><cola>243151</cola><colb>7502= 92</colb><colc>{ts=20 ''2005-02-14=20 = 16:13:18''}</colc><cold>2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4p= r0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0</cold&= gt;</row><row><cola>764929</cola><colb>6412= 15</colb><colc>{ts=20 ''2005-02-14=20 = 16:13:18''}</colc><cold>1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41p= odr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r</cold&= gt;</row></query>'
,'//query/@rows','','')
Theo
 
-----Original = Message-----
From: George=20 Weaver [mailto:gweaver@shaw.ca]
Sent: Tuesday, 15 = February=20 2005 12:39 AM
To: Theo Galanakis;=20 pgsql-sql@postgresql.org
Subject: Re: [SQL] Working = with=20 XML.

Hi Theo,
 
I am not aware of any means of = passing xml=20 to stored procedures, apart from writing your own function to = parse the=20 xml.
 
In regard to your second = question=20 - how to access the second record - try using a more = explicit=20 xpath query incorporating a "where" component.  For = example,=20 if you wanted to access the second row based upon the value of = cola, you=20 could use '//query/row[@cola=3D'525887']/text()'.  Or you = could=20 specify the position of the record if you know its position:=20 '//query/row[2]/text()'.
 
Microsoft has a very good = reference on=20 xpath expressions: http://msdn.microsoft.com/library/def= ault.asp?url=3D/library/en-us/xmlsdk/html/xmrefxpathsyntax.asp.
 
Regards,
George
----- Original Message ----- =
From:=20 Theo = Galanakis=20
To: pgsql-sql@postgresql.org=20
Sent: Sunday, February = 13, 2005=20 11:48 PM
Subject: [SQL] Working = with=20 XML.

Hi Folks,

Is there a way to pass in an = xml string=20 into a stored proc and thenplace this into a temp table? =

I use to be able to do this in = sql server,=20 it was quite handy as I could call one stored proc to update = multiple=20 records, here is a sample in sql server:

select CoverTypeID, ItemSQ, = SituationID,=20 ItemDescription, CoverAmount
From  OpenXML ( @XmlHandle, = '/cover/covertype/item',1 )=20
          &nbs= p;=20 With ( CoverTypeID int '../@id',
          &nbs= p;          =20 ItemSQ int '@id',=20
       =20               SituationID int=20 '@situationID',
          &nbs= p;          =20 ItemDescription varchar(100) '@description',=20
       =20               CoverAmount money = '@amount'=20 )

I have managed to get get = pgxml_xpath=20 working, however Im not sure how to access specific rows in an = xml=20 document. E.g below there are two records, how do I access the = second=20 record, the following returns both ,'//query/row/cola values = being=20 (284122,525887):

select
pgxml_xpath(
'<query=20 columns=3D"4"=20 = rows=3D"100"><row><cola>284122</cola><colb>789= 648</colb><colc>{ts=20 ''2005-02-14=20 = 16:13:18''}</colc><cold>unbnda8m5946z55sgi1xco34h1q9tLonoys3n= yk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65</cold&= gt;</row<row><cola>525887</cola><colb>493253&l= t;/colb><colc>{ts=20 ''2005-02-14=20 = 16:13:18''}</colc><cold>6uydk442uz247ga45kpys7htkxznkn8La31qh= n942wu2cu2pdr25mv2nup2zh3vcbh3c4vdauak3p3w093cvtkeyga692b455cr3</cold&= gt;</row></query>'

,'//query/row/cola/text()','','')=20

_________________________________________________________= _____________
This=20 email, including attachments, is intended only for the=20 addressee
and may be confidential, privileged and = subject to=20 copyright. If you
have received this email in error, = please=20 advise the sender and delete
it. If you are not the = intended=20 recipient of this email, you must not
use, copy or = disclose=20 its content to anyone. You must not copy or =
communicate to=20 others content that is confidential or subject to =
copyright,=20 unless you have the consent of the content=20 = owner.
------=_NextPart_000_00B1_01C51395.0F264BB0--