RE: XML Question

From: Davey, Alan <ddavey_at_harris.com>
Date: Tue, 20 Sep 2005 10:42:56 -0400
Message-ID: <A223847B255BF54FBE6AFEEF58F0C16FF50FCC@mlbe2k6.cs.myharris.net>

Thanks for the replies Brian and Ian.

Brian, that certainly does the trick, but leaves me feeling dirty, as my pl/sql routines are going to be called fairly often.

The XML document that I am producing will be fairly large, so various pl/sql routines are building different parts that then get concatenated together in the end. My hope was to use constant variables for the element name in case there was a minor change in the xml schema down the road. I suppose the argument could be made to just use literals and update them when the schema changes, since the receiver of the xml document should be able to use the latest schema as well.

Since some of my xml data is strictly header information, I was hoping to avoid hitting dual at all and doing something like: l_xml := xmlelement(...);

But it seems that the xmlelement function only works as part of a select statement.

Regards,

Alan Davey

From: Ian Cary (C) [mailto:Ian.Cary_at_ordnancesurvey.co.uk] Sent: Tuesday, September 20, 2005 10:30 AM To: Davey, Alan; oracle-l_at_freelists.org
Subject: RE: XML Question

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

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

Received on Tue Sep 20 2005 - 09:45:21 CDT