RE: XML Question

From: Ian Cary (C) <Ian.Cary_at_ordnancesurvey.co.uk>
Date: Tue, 20 Sep 2005 15:29:54 +0100
Message-ID: <4AA808D68343824E8891632BD448AE6B1BEB9A@OSMAIL.ordsvy.gov.uk>

Would this help?

  1 declare
  2 cName constant varchar2(4) := 'Name';   3 l_xml xmltype;
  4 begin

  5     execute immediate 'select xmlelement('||cname||',''Larry'') from dual'
  6     into l_xml;
  7     dbms_output.put_line(l_xml.getCLOBVal());

8* end;
MP401SH2> /
<NAME>Larry</NAME>

PL/SQL procedure successfully completed.

Cheers,

Ian

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Davey, Alan Sent: 20 September 2005 14:59
To: oracle-l_at_freelists.org
Subject: XML Question

Hi,

I have a pl/sql routine that is generating XML data. When calling function xmlelement(), I want to supply a variable as the first parameter. However, Oracle (v10.1.0.4 on windows) takes the parameter name (not its value) and uses it as the element name as the following example shows:

Wrote file afiedt.buf

  1 declare
  2 cName constant varchar2(4) := 'Name';   3 l_xml xmltype;
  4 begin
  5 select xmlelement(cName,'Larry')   6 into l_xml
  7 from dual;
  8 dbms_output.put_line(l_xml.getCLOBVal());   9* end;
adavey_at_EASDEV> /
<CNAME>Larry</CNAME>

PL/SQL procedure successfully completed.

If you replace cName with xyz (no quotes), Oracle runs the pl/sql and uses <xyz> as the element tag name. Every example that I see in the FM is using a literal string for the element name. Is it possible to use a variable name for this parameter? If not, is this just an oversight on the part of Oracle?

Thanks,

Alan Davey
Senior Programmer/Analyst, Advertising Solutions Oracle 9i OCA; 3/4 OCP
Harris Corporation
w) 212-295-3458

This email and any attachments have been virus checked upon receipt at Ordnance Survey and are free of all known viruses.

This email is only intended for the person to whom it is addressed and may contain confidential information. If you have received this email in error, please notify the sender and delete this email which must not be copied, distributed or disclosed to any other person. Unless stated otherwise, the contents of this email are personal to the writer and do not represent the official view of Ordnance Survey. Nor can any contract be formed on Ordnance Survey's behalf via email. We reserve the right to monitor emails and attachments without prior notice.

Thank you for your cooperation.

Ordnance Survey
Romsey Road
Southampton SO16 4GU
Tel: 023 8079 2000
http://www.ordnancesurvey.co.uk

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Sep 20 2005 - 09:33:16 CDT