Hi,

As the title states, I have a query that I use to concatenate all the rows into a single string and I use the XMLTransform method to accomplish that but I still need to add a new line separator within the XML string and so far I didn't find any escape character to work. I tried the '\n', CHR(10), ASCII_CHR(10)||ASCII_CHR(13), <br/> ...

Here is the code snippet:

SELECT
        XMLTransform(SYS_XMLAgg(SYS_XMLGen(LocationTable.LOCATION)), XMLType('<?xml version="1.0"?><xsl:stylesheet version="1.0"
        xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
          <xsl:template match="/">
            <xsl:for-each select="/ROWSET/LOCATION">
              <xsl:value-of select="text()"/>
              ---NEW LINE SEPARATOR NEEDED HERE---
            </xsl:for-each>
          </xsl:template>
        </xsl:stylesheet>')).getstringval()
        FROM LocationTable

Regards,
Alin

I found the answer:

SELECT
        XMLTransform(SYS_XMLAgg(SYS_XMLGen(LocationTable.LOCATION)), XMLType('<?xml version="1.0"?><xsl:stylesheet version="1.0"
        xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
          <xsl:variable name="new_line" select="''&#xA;''" />
          <xsl:template match="/">
            <xsl:for-each select="/ROWSET/LOCATION">
              <xsl:value-of select="text()"/>
              <xsl:value-of select="$new_line" />
            </xsl:for-each>
          </xsl:template>
        </xsl:stylesheet>')).getstringval()
        FROM LocationTable
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.