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 19CCD8BA105 for ; Tue, 15 Feb 2005 13:36:52 +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 48226-01 for ; Tue, 15 Feb 2005 13:36:48 +0000 (GMT) Received: from pd6mo2no.prod.shaw.ca (idcmail-mo2no.cg.shawcable.net [64.59.134.9]) by svr1.postgresql.org (Postfix) with ESMTP id 269828BA275 for ; Tue, 15 Feb 2005 13:36:47 +0000 (GMT) Received: from pd7mr6no.prod.shaw.ca (pd7mr6no-qfe2.prod.shaw.ca [10.0.144.223]) by l-daemon (Sun ONE Messaging Server 6.0 HotFix 1.01 (built Mar 15 2004)) with ESMTP id <0IBY0033IGH4EF50@l-daemon> for pgsql-sql@postgresql.org; Tue, 15 Feb 2005 06:36:40 -0700 (MST) Received: from pn7ml4no.prod.shaw.ca ([10.0.149.113]) by pd7mr6no.prod.shaw.ca (Sun ONE Messaging Server 6.0 HotFix 1.01 (built Mar 15 2004)) with ESMTP id <0IBY00CKHGH4FP10@pd7mr6no.prod.shaw.ca> for pgsql-sql@postgresql.org; Tue, 15 Feb 2005 06:36:40 -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 <0IBY00LD7GGYXA@l-daemon> for pgsql-sql@postgresql.org; Tue, 15 Feb 2005 06:36:40 -0700 (MST) Date: Tue, 15 Feb 2005 07:36:37 -0600 From: George Weaver Subject: Re: Working with XML. To: Theo Galanakis Cc: pgsql-sql@postgresql.org Message-id: <002301c51363$61db28d0$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_0020_01C51331.14B37E40" X-Priority: 3 X-MSMail-priority: Normal References: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.708 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, FORGED_RCVD_HELO, HTML_50_60, HTML_MESSAGE, HTML_TAG_EXIST_TBODY X-Spam-Level: * X-Archive-Number: 200502/153 X-Sequence-Number: 20264 This is a multi-part message in MIME format. ------=_NextPart_000_0020_01C51331.14B37E40 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable MessageHi 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', = 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_0020_01C51331.14B37E40 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message
Hi Theo,
 
I'm not sure if it can be done with the = xml contrib=20 module.  You may want to install and work with the xml2 contrib = module,=20 which is more recent, has more extensive capabilities, and is = easier to=20 work with.
 
It will give you the result you = 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 = XML.

Thanks George.
 
    How do you get an attributes value the = following returns=20 the attribute tag. i.e. rows=3D"100", all i want is the = 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 Weaver=20 [mailto:gweaver@shaw.ca]
Sent: Tuesday, 15 February 2005 = 12:39=20 AM
To: Theo Galanakis; = pgsql-sql@postgresql.org
Subject:=20 Re: [SQL] Working with XML.

Hi Theo,
 
I am not aware of any means of = passing xml to=20 stored procedures, apart from writing your own function to parse the = xml.
 
In regard to your second question = - how to=20 access the second record - try using a more explicit = xpath query=20 incorporating a "where" component.  For example, if you wanted = to=20 access the second row based upon the value of cola, you could use=20 '//query/row[@cola=3D'525887']/text()'.  Or you could specify = the=20 position of the record if you know its position:=20 '//query/row[2]/text()'.
 
Microsoft has a very good reference = on xpath=20 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
Sent: Sunday, February 13, = 2005 11:48=20 PM
Subject: [SQL] Working with = XML.

Hi Folks,

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

I use to be able to do this in sql = server, it=20 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,=20 ItemDescription, CoverAmount
From =20 OpenXML ( @XmlHandle, '/cover/covertype/item',1 )
          &nbs= p;=20 With ( CoverTypeID int '../@id',
          &nbs= p;          =20 ItemSQ int '@id', =
       =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 working,=20 however Im not sure how to access specific rows in an xml = document. E.g=20 below there are two records, how do I access the second record, = the=20 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()','','')

_________________________________________________________= _____________
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 its=20 content to anyone. You must not copy or
communicate to = others=20 content that is confidential or subject to
copyright, = unless you=20 have the consent of the content=20 = owner.
------=_NextPart_000_0020_01C51331.14B37E40--