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 B3EAF8BA03C for ; Mon, 14 Feb 2005 13:39:01 +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 97737-01 for ; Mon, 14 Feb 2005 13:38:58 +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 A215C8B9B2C for ; Mon, 14 Feb 2005 13:38:55 +0000 (GMT) Received: from pd8mr2no.prod.shaw.ca (pd8mr2no-qfe2.prod.shaw.ca [10.0.144.159]) by l-daemon (Sun ONE Messaging Server 6.0 HotFix 1.01 (built Mar 15 2004)) with ESMTP id <0IBW00KU1LWSBB60@l-daemon> for pgsql-sql@postgresql.org; Mon, 14 Feb 2005 06:38:52 -0700 (MST) Received: from pn7ml4no.prod.shaw.ca ([10.0.149.113]) by pd8mr2no.prod.shaw.ca (Sun ONE Messaging Server 6.0 HotFix 1.01 (built Mar 15 2004)) with ESMTP id <0IBW00AEPLWSELH0@pd8mr2no.prod.shaw.ca> for pgsql-sql@postgresql.org; Mon, 14 Feb 2005 06:38:52 -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 <0IBW0055ELWM1N@l-daemon> for pgsql-sql@postgresql.org; Mon, 14 Feb 2005 06:38:52 -0700 (MST) Date: Mon, 14 Feb 2005 07:38:46 -0600 From: George Weaver Subject: Re: Working with XML. To: Theo Galanakis , pgsql-sql@postgresql.org Message-id: <000f01c5129a$8490c1b0$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_000C_01C51268.37514960" 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/141 X-Sequence-Number: 20252 This is a multi-part message in MIME format. ------=_NextPart_000_000C_01C51268.37514960 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 = copyright, unless you have the consent of the content owner. =20 ------=_NextPart_000_000C_01C51268.37514960 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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=20 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 = access=20 the second row based upon the value of cola, you could use=20 '//query/row[@cola=3D'525887']/text()'.  Or you could specify the = position of=20 the record if you know its position: = '//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 was=20 quite handy as I could call one stored proc to update multiple = records, here=20 is a sample in sql server:

select CoverTypeID, ItemSQ, = SituationID,=20 ItemDescription, CoverAmount
From =20 OpenXML ( @XmlHandle, '/cover/covertype/item',1 )
          &nbs= p; With=20 ( CoverTypeID int '../@id',
          &nbs= p;          =20 ItemSQ int '@id', =
       =20               SituationID int = '@situationID',=20
          &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 below=20 there are two records, how do I access the second record, the = following=20 returns both ,'//query/row/cola values being = (284122,525887):

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>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 = addressee
and=20 may be confidential, privileged and subject to copyright. If = you
have=20 received this email in error, please advise the sender and = delete
it.=20 If you are not the intended recipient of this email, you must=20 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=20 = owner.
------=_NextPart_000_000C_01C51268.37514960--