[SPAM] Betr: [DB2-L] XML challenge

Daniel Luksetich

[SPAM] Betr: [DB2-L] XML challenge
Specifically the problem I am having, both with my attempts and your
suggestion, is that my tags are getting modified. For example...

<name>Bob</name>

becomes...

<name%gt;Bob</name>

Look for a way to preserve the tag as is.

Thanks,
Dan


On Wed, 8 Dec 2010 16:26:20 +0100, Marcel Harleman
<[login to unmask email]> wrote:
> Maybe
>
> SELECT XMLSERIALIZE (
> XMLDOCUMENT(
> XMLCONCAT (
> XMLELEMENT ( NAME "blabla", colname )
> )
> )
> AS CLOB
> INCLUDING XMLDECLARATION
> )
> FROM creator.tablename
>
>
> does work?
>
>
> Regards,
>
> Marcel.
>
>
> ------------------------------------------------------------------------
> De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
> vertrouwelijke informatie met de burger of voor de bekendmaking
> van beslissingen. De Belastingdienst hanteert conventies voor het
> gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl.
>
>
> _____________________________________________________________________
> * IDUG North America * Anaheim, California * May 2-6 2011 *
> http://IDUG.ORG/NA *
> * If you are going to attend only one conference this year, this is
it!
> *
> ** The best DB2 technical sessions in the world
> ** Independent, not-for-profit, User Run - the IDUG difference!
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is
> the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Joe Geller

Re: XML challenge
(in response to David Simpson)
Dan,
Try XMLPARSE to convert a string to an XML value.

Joe



Folks,
I have some XML in a string stored in a varchar field of 16,000 bytes
EBCDIC. I am interested in an SQL statement that will convert the string
into an XML data type such that various XML functions can then be applied
to the XML result. Anyone done this with success? I'm currently playing
with the XMLCAST and XMLSERIALIZE functions. I'm not getting the expected
results.

Thanks,
Dan Luksetich

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Daniel Luksetich

Re: Betr: [DB2-L] XML challenge
(in response to Daniel Luksetich)
got it! XMLPARSE did the job!

Thanks for the advice.

Dan


On Wed, 8 Dec 2010 16:26:20 +0100, Marcel Harleman
<[login to unmask email]> wrote:
> Maybe
>
> SELECT XMLSERIALIZE (
> XMLDOCUMENT(
> XMLCONCAT (
> XMLELEMENT ( NAME "blabla", colname )
> )
> )
> AS CLOB
> INCLUDING XMLDECLARATION
> )
> FROM creator.tablename
>
>
> does work?
>
>
> Regards,
>
> Marcel.
>
>
> ------------------------------------------------------------------------
> De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
> vertrouwelijke informatie met de burger of voor de bekendmaking
> van beslissingen. De Belastingdienst hanteert conventies voor het
> gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl.
>
>
> _____________________________________________________________________
> * IDUG North America * Anaheim, California * May 2-6 2011 *
> http://IDUG.ORG/NA *
> * If you are going to attend only one conference this year, this is
it!
> *
> ** The best DB2 technical sessions in the world
> ** Independent, not-for-profit, User Run - the IDUG difference!
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is
> the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

David Simpson

Re: XML challenge
(in response to Daniel Luksetich)
I've seen some clients do this with a global temp table that has an XML column. This is particularly effective if you need to query the xml column multiple times.

So... insert the xml into the GTT and then use your functions.

______________________________________________________________________________
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email]
For more information about Themis, visit www.themisinc.com



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dan Luksetich
Sent: Wednesday, December 08, 2010 9:19 AM
To: [login to unmask email]
Subject: [DB2-L] XML challenge

Folks,
I have some XML in a string stored in a varchar field of 16,000 bytes
EBCDIC. I am interested in an SQL statement that will convert the string
into an XML data type such that various XML functions can then be applied
to the XML result. Anyone done this with success? I'm currently playing
with the XMLCAST and XMLSERIALIZE functions. I'm not getting the expected
results.

Thanks,
Dan Luksetich

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Daniel Luksetich

Re: XML challenge
(in response to Joe Geller)
Yeah, that's what I ultimately tried and it worked like a charm! Then I
got to code all those wonderful XPath expressions against the document.
Super way cool! If I use a common table expression then I can put
predicates against the table in the CTE and do any manipulation in the
final query to make the SQL a little easier to read. Maybe even put the
conversion from string to XML in a view to make the application believe it
is reading an XML data type. Again, way cool.

Thanks,
Dan

On Wed, 8 Dec 2010 10:47:39 -0500, Joe Geller <[login to unmask email]>
wrote:
> Dan,
> Try XMLPARSE to convert a string to an XML value.
>
> Joe
>
>
>
> Folks,
> I have some XML in a string stored in a varchar field of 16,000 bytes
> EBCDIC. I am interested in an SQL statement that will convert the string
> into an XML data type such that various XML functions can then be
applied
> to the XML result. Anyone done this with success? I'm currently playing
> with the XMLCAST and XMLSERIALIZE functions. I'm not getting the
expected
> results.
>
> Thanks,
> Dan Luksetich
>
> _____________________________________________________________________
> * IDUG North America * Anaheim, California * May 2-6 2011 *
> http://IDUG.ORG/NA *
> * If you are going to attend only one conference this year, this is
it!
> *
> ** The best DB2 technical sessions in the world
> ** Independent, not-for-profit, User Run - the IDUG difference!
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is
> the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv