Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2010
    Posts
    7

    Unanswered: Preserve XML in VARCHAR column

    Hi
    I am using zOS DB2 9 and have a table which contains a varchar field which contains xml data.
    I want to pull this column, and another couple of integer columns to create a complete xml document.
    However, when I extract the xml from the varchar, the xml querying thinks it is text and convert the less than / greater than to the "<" html values which isn't what I want.
    I almost need an XMLPRESERVE type function.
    Here is an example of a query to demonstrate (it's in quotes in case any conversion happens)

    "SELECT XML2CLOB(XMLCONCAT(
    XMLELEMENT(NAME "Value", 1)
    ,XMLELEMENT(NAME "Test", '<TagOne>1</TagOne>')
    )) FROM SYSIBM.SYSDUMMY1"
    Note: The <TagOne>1</TagOne> value would come from my varchar column in the real query.

    This outputs:
    <Value>1</Value><Test>&lt;TagOne&gt;1&lt;/TagOne&gt;</Test>

    The output I actually want is:
    <Value>1</Value><Test><TagOne>1</TagOne></Test>

    Anyone with any ideas?
    Thanks in advance
    Simon

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    A string is not an XML document. It is a value inside an XML doc. You need do parse the string to create an XML document. Then you can combine this document with other XML elements. Have a look at the XMLPARSE() function.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jan 2010
    Posts
    7
    wow ... thanks knut
    that works a treat :-)

    SELECT XML2CLOB(XMLELEMENT(NAME "Test", XMLPARSE('<TagOne>1</TagOne>'))
    ) FROM SYSIBM.SYSDUMMY1

    You star

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •