Home > HowTo > How To : Read and Write Office 2003 Excel XML Files

How To : Read and Write Office 2003 Excel XML Files

February 12, 2012 Leave a comment Go to comments

Office Open XML (OOX) has become the default format with the release of Office 2007, but back in the 2003’s days, Microsoft had already developed a format to store Excel workbooks as XML.
A comprehensive overview is available here :
Dive into SpreadsheetML (Part 1 of 2)
Dive into SpreadsheetML (Part 2 of 2)

Contrary to OOX where data and metadata are stored in a multipart archive, an Excel workbook file in SpreadsheetML 2003 format consists in a single XML instance, and therefore easily managed using built-in Oracle XML functions and XML DB features.
In this article, I’ll focus on how to create and read such files with the help of SQL/XML functions, XSLT and XQuery.

 

1. Writing a file

The minimum valid structure for an instance looks like this :

<?xml version="1.0" encoding="UTF-8"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
 <Worksheet ss:Name="Sheet1">
  <Table>
   <Row>
    <Cell>
     <Data ss:Type="String">Employee No</Data>
    </Cell>
    <Cell>
     <Data ss:Type="String">Employee Name</Data>
    </Cell>
    <Cell>
     <Data ss:Type="String">Job</Data>
    </Cell>
   </Row>
   <Row>
    <Cell>
     <Data ss:Type="Number">7839</Data>
    </Cell>
    <Cell>
     <Data ss:Type="String">KING</Data>
    </Cell>
    <Cell>
     <Data ss:Type="String">PRESIDENT</Data>
    </Cell>
   </Row>
   <!-- More rows -->
  </Table>
 </Worksheet>
</Workbook>

It can be generated this way, with SQL/XML functions :

SELECT XMLConcat(
         XMLPi("mso-application", 'progid="Excel.Sheet"')
       , XMLElement("Workbook",
           XMLAttributes(
             'urn:schemas-microsoft-com:office:spreadsheet' as "xmlns"
           , 'urn:schemas-microsoft-com:office:spreadsheet' as "xmlns:ss"
           )
         , XMLElement("Worksheet",
             XMLAttributes('Sheet1' as "ss:Name")
           , XMLElement("Table",
               XMLElement("Row",
                 XMLForest(
                   XMLElement("Data", XMLAttributes('String' as "ss:Type"), 'Employee No') as "Cell"
                 , XMLElement("Data", XMLAttributes('String' as "ss:Type"), 'Employee Name') as "Cell"
                 , XMLElement("Data", XMLAttributes('String' as "ss:Type"), 'Job') as "Cell"
                 )
               )
             , XMLAgg(
                 XMLElement("Row",
                   XMLForest(
                     XMLElement("Data", XMLAttributes('Number' as "ss:Type"), e.empno) as "Cell"
                   , XMLElement("Data", XMLAttributes('String' as "ss:Type"), e.ename) as "Cell"
                   , XMLElement("Data", XMLAttributes('String' as "ss:Type"), e.job) as "Cell"
                   )
                 )
                 order by e.empno
               )
             )
           )
         )
       )
FROM scott.emp e
;

Although this query is relatively simple and efficient, we can imagine how cumbersome it could get to write queries for more complex requirements.
So this is where XSLT comes into play. By creating a stylesheet working on a canonical XML input, we can hide the transformation logic and separate the data layer from the presentation layer.

Following is an example generating a multisheet workbook and some additional Excel-specific formattings (frozen headers and tab color set to red for total salaries higher than 10,000).
Here, I first stored the XSLT stylesheet in the XML DB repository. That’s not mandatory, we can also declare the stylesheet inline in the PL/SQL block, but it’s a good practice to keep the stylesheets in the database (repository or XMLType column in a relational table) if we intend to use them with the internal XSLT processor.

The stylesheet (test.xsl) :

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns="urn:schemas-microsoft-com:office:spreadsheet"  
 xmlns:x="urn:schemas-microsoft-com:office:excel" 
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
 <xsl:output method="xml" encoding="UTF-8"/>
 <xsl:template match="/">
  <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>
  <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" 
            xmlns:x="urn:schemas-microsoft-com:office:excel" 
            xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
   <Styles>
    <Style ss:ID="h">
     <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
    </Style>
   </Styles>
   <xsl:apply-templates/>
  </Workbook>
 </xsl:template>
 <xsl:template match="ROWSET">
  <Worksheet ss:Name="{@name}">
   <Table>
    <Row>
     <xsl:for-each select="ROW[1]/*">
      <Cell ss:StyleID="h">
       <Data ss:Type="String">
        <xsl:value-of select="translate(local-name(), '_', ' ')"/>
       </Data>
      </Cell>
     </xsl:for-each>
    </Row>
    <xsl:apply-templates/>
   </Table>
   <x:WorksheetOptions>
    <x:FrozenNoSplit/>
    <x:SplitHorizontal>1</x:SplitHorizontal>
    <x:TopRowBottomPane>1</x:TopRowBottomPane>
    <x:ActivePane>2</x:ActivePane>
    <xsl:if test="@color">
     <x:TabColorIndex><xsl:value-of select="@color"/></x:TabColorIndex>
    </xsl:if>
   </x:WorksheetOptions>
  </Worksheet>
 </xsl:template>
 <xsl:template match="ROW">
  <Row>
   <xsl:apply-templates/>
  </Row>
 </xsl:template>
 <xsl:template match="ROW/*">
  <Cell>
   <Data ss:Type="String">
    <xsl:value-of select="."/>
   </Data>
  </Cell>
 </xsl:template>
</xsl:stylesheet>

 

The transformation code :

DECLARE
  
  xmldoc CLOB;
 
BEGIN
 
  select xmlserialize(document
           xmltransform(
             xmlelement("ROOT",
               xmlagg(
                 xmlelement("ROWSET",
                   xmlattributes(
                     d.dname as "name"
                   , case when sum(e.sal) > 10000 then '2' end as "color"
                   )
                 , xmlagg(
                     xmlelement("ROW",
                       xmlforest(
                         e.empno as "Employee_No"
                       , e.ename as "Employee_Name"
                       , e.job   as "Job"
                       , e.sal   as "Salary"
                       )
                     ) order by e.empno
                   )
                 ) order by d.deptno
               )
             )
           , xdburitype('/office/excel/stylesheets/out/test.xsl').getXML()
           )
           as clob
         )
  into xmldoc
  from scott.dept d
       join scott.emp e on e.deptno = d.deptno
  group by d.deptno
         , d.dname
  ;
  
  dbms_xslprocessor.clob2file(xmldoc, 'TEST_DIR', 'test.xml');
 
END;
/

 

The output file :

 

One of the most used Excel features is the Pivot Table generator. Creating such content is also possible directly from the database, using XSLT.
For instance, here’s some “raw” data :

SQL> select employee_id
  2       , first_name
  3       , last_name
  4       , extract(year from hire_date) as hire_year
  5       , job_id
  6  from hr.employees
  7  ;
 
EMPLOYEE_ID FIRST_NAME           LAST_NAME                  HIRE_YEAR JOB_ID
----------- -------------------- ------------------------- ---------- ----------
        198 Donald               OConnell                        2007 SH_CLERK
        199 Douglas              Grant                           2008 SH_CLERK
        200 Jennifer             Whalen                          2003 AD_ASST
        201 Michael              Hartstein                       2004 MK_MAN
        202 Pat                  Fay                             2005 MK_REP
        203 Susan                Mavris                          2002 HR_REP
        204 Hermann              Baer                            2002 PR_REP
        205 Shelley              Higgins                         2002 AC_MGR
        206 William              Gietz                           2002 AC_ACCOUNT
        100 Steven               King                            2003 AD_PRES
        101 Neena                Kochhar                         2005 AD_VP
        102 Lex                  De Haan                         2001 AD_VP
        103 Alexander            Hunold                          2006 IT_PROG

...

        195 Vance                Jones                           2007 SH_CLERK
        196 Alana                Walsh                           2006 SH_CLERK
        197 Kevin                Feeney                          2006 SH_CLERK
 
107 rows selected
 

and we want to display, for a given job, the number of employees hired per year.
In SQL, that’s called a dynamic pivot but it’s not possible – with conventional methods – to produce such a result set out of a single SELECT statement (because the number of columns has to be known at parse time).

The PIVOT XML operator (11g) provides a partial answer to the problem by generating an XMLType containing aggregated “columns” (actually XML “elements”). The same functionality can be simulated in 10g too with XMLAgg and a partitioned outer join.
But with that method, we still have to build the pivot in SQL, in the database.

What I describe below let Excel do the job for us, through its standard pivot table functionality. We just have to generate a tab containing the raw data (hereafter named “DataSource”), and a tab (“PivotTable”) containing the minimum pivot table definition, i.e. no data and no cache.

The stylesheet (pivot.xsl) :

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
 <xsl:output method="xml" encoding="UTF-8"/>
 <xsl:param name="filename"/>
 <xsl:template match="/">
  <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>
  <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
            xmlns:x="urn:schemas-microsoft-com:office:excel"
            xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
   <xsl:apply-templates/>
  </Workbook>
 </xsl:template>
 <xsl:template match="ROWSET">
  <Worksheet ss:Name="DataSource">
   <Table>
    <Row>
     <Cell><Data ss:Type="String">EMPLOYEE_ID</Data></Cell>
     <Cell><Data ss:Type="String">FIRST_NAME</Data></Cell>
     <Cell><Data ss:Type="String">LAST_NAME</Data></Cell>
     <Cell><Data ss:Type="String">HIRE_YEAR</Data></Cell>
     <Cell><Data ss:Type="String">JOB_ID</Data></Cell>
    </Row>
    <xsl:apply-templates/>
   </Table>
  </Worksheet>
  <Worksheet ss:Name="PivotTable">
   <Table/>
   <x:PivotTable>
    <x:Name>My Pivot Table</x:Name>
    <x:ImmediateItemsOnDrop/>
    <x:ShowPageMultipleItemLabel/>
    <x:GrandTotalString>Total</x:GrandTotalString>
    <x:Location>R3C1:R4C2</x:Location>
    <x:PivotField>
     <x:Name>EMPLOYEE_ID</x:Name>
     <x:DataType>Integer</x:DataType>
    </x:PivotField>
    <x:PivotField>
     <x:Name>FIRST_NAME</x:Name>
    </x:PivotField>
    <x:PivotField>
     <x:Name>LAST_NAME</x:Name>
    </x:PivotField>
    <x:PivotField>
     <x:Name>HIRE_YEAR</x:Name>
     <x:Orientation>Column</x:Orientation>
     <x:AutoSortOrder>Ascending</x:AutoSortOrder>
     <x:Position>1</x:Position>
     <x:DataType>Integer</x:DataType>
     <x:PivotItem>
      <x:Name/>
     </x:PivotItem>
    </x:PivotField>
    <x:PivotField>
     <x:Name>JOB_ID</x:Name>
     <x:Orientation>Row</x:Orientation>
     <x:AutoSortOrder>Ascending</x:AutoSortOrder>
     <x:Position>1</x:Position>
     <x:PivotItem>
      <x:Name/>
     </x:PivotItem>
    </x:PivotField>
    <x:PivotField>
     <x:DataField/>
     <x:Name>Data</x:Name>
     <x:Orientation>Row</x:Orientation>
     <x:Position>-1</x:Position>
    </x:PivotField>
    <x:PivotField>
     <x:Name>Number of Employees</x:Name>
     <x:ParentField>EMPLOYEE_ID</x:ParentField>
     <x:Orientation>Data</x:Orientation>
     <x:Function>Count</x:Function>
     <x:Position>1</x:Position>
    </x:PivotField>
    <x:PTLineItems>
     <x:PTLineItem>
      <x:Item>0</x:Item>
     </x:PTLineItem>
    </x:PTLineItems>
    <x:PTLineItems>
     <x:Orientation>Column</x:Orientation>
     <x:PTLineItem>
      <x:Item>0</x:Item>
     </x:PTLineItem>
    </x:PTLineItems>
    <x:PTSource>
     <x:RefreshOnFileOpen/>
     <x:ConsolidationReference>
      <x:FileName>[<xsl:value-of select="$filename"/>]DataSource</x:FileName>
      <x:Reference>R1C1:R<xsl:value-of select="count(ROW)+1"/>C5</x:Reference>
     </x:ConsolidationReference>
    </x:PTSource>
   </x:PivotTable>
  </Worksheet>
 </xsl:template>
 <xsl:template match="ROW">
  <Row>
   <Cell><Data ss:Type="Number"><xsl:value-of select="EMPLOYEE_ID"/></Data></Cell>
   <Cell><Data ss:Type="String"><xsl:value-of select="FIRST_NAME"/></Data></Cell>
   <Cell><Data ss:Type="String"><xsl:value-of select="LAST_NAME"/></Data></Cell>
   <Cell><Data ss:Type="Number"><xsl:value-of select="HIRE_YEAR"/></Data></Cell>
   <Cell><Data ss:Type="String"><xsl:value-of select="JOB_ID"/></Data></Cell>
  </Row>
 </xsl:template>
 <xsl:template name="PivotTable">
 </xsl:template>
</xsl:stylesheet>

 

The transformation code :

DECLARE

  res clob;
  v_filename varchar2(260) := 'test_pivot.xml';

BEGIN

  select xmlserialize(document
           xmltransform(
             xmlelement("ROWSET",
               xmlagg(
                 xmlelement("ROW",
                   xmlforest(
                     employee_id
                   , first_name
                   , last_name
                   , extract(year from hire_date) as hire_year
                   , job_id
                   )
                 )
               )
             )
           , xdburitype('/office/excel/stylesheets/out/pivot.xsl').getXML()
           , 'filename="'''||v_filename||'''"'
           )
         )
  into res
  from hr.employees
  ;

  dbms_xslprocessor.clob2file(res, 'TEST_DIR', v_filename);

END;
/

 

The output file :

 

2. Reading a file

I’ll divide this section in two parts : querying and optimizing.

a) “One-shot” queries

Let’s say we want to read this document (saved as XML 2003 format) as if it were a relational table :

As usual, we’ll use an XMLType table to store the original file and then query from it.
Examples in the present article were tested on 11g XE (11.2.0.2) so storage is Binary XML by default :

create table tmp_xml of xmltype;

insert into tmp_xml values(
  xmltype(
    bfilename('XML_DIR','test.xml')
  , nls_charset_id('AL32UTF8')
  )
);

The query involves two XMLTable() functions, the first one to break the document into separate worksheets, and the second to extract each row from them :

SQL> select x1.sheetname
  2       , x2.id
  3       , x2.comments
  4       , x2.dt
  5  from tmp_xml t
  6     , xmltable(
  7         xmlnamespaces( default 'urn:schemas-microsoft-com:office:spreadsheet'
  8                      , 'urn:schemas-microsoft-com:office:spreadsheet' as "ss" )
  9       , '/Workbook/Worksheet'
 10         passing t.object_value
 11         columns sheetname varchar2(31) path '@ss:Name'
 12               , rowset    xmltype      path 'Table/Row'
 13       ) x1
 14     , xmltable(
 15         xmlnamespaces(default 'urn:schemas-microsoft-com:office:spreadsheet')
 16       , '/Row[position()>1]'
 17         passing x1.rowset
 18         columns id        number         path 'Cell[1]/Data'
 19               , comments  varchar2(2000) path 'Cell[2]/Data'
 20               , dt        timestamp      path 'substring-before(Cell[3]/Data,".")'
 21       ) x2
 22  where x1.sheetname = 'MyData-1'
 23  ;
 
SHEETNAME               ID COMMENTS                                           DT
--------------- ---------- -------------------------------------------------- -------------------------
MyData-1                 1 This is a comment for line #1                      09/02/12 12:09:37,000000
MyData-1                 2 This is a comment for line #2                      10/02/12 12:09:36,000000
MyData-1                 3 This is a comment for line #3                      11/02/12 12:09:36,000000
MyData-1                 4 This is a comment for line #4                      12/02/12 12:09:36,000000
MyData-1                 5 This is a comment for line #5                      13/02/12 12:09:36,000000
MyData-1                 6 This is a comment for line #6                      14/02/12 12:09:36,000000
MyData-1                 7 This is a comment for line #7                      15/02/12 12:09:36,000000
MyData-1                 8 This is a comment for line #8                      16/02/12 12:09:36,000000
MyData-1                 9 This is a comment for line #9                      17/02/12 12:09:36,000000
MyData-1                10 This is a comment for line #10                     18/02/12 12:09:36,000000
MyData-1                11 This is a comment for line #11                     19/02/12 12:09:36,000000
MyData-1                12 This is a comment for line #12                     20/02/12 12:09:36,000000
MyData-1                13 This is a comment for line #13                     21/02/12 12:09:36,000000
MyData-1                14 This is a comment for line #14                     22/02/12 12:09:36,000000
MyData-1                15 This is a comment for line #15                     23/02/12 12:09:36,000000
MyData-1                16 This is a comment for line #16                     24/02/12 12:09:36,000000
 
16 rows selected
 

 

b) Optimized access of the document

If loading these documents in the database is a recurring task then, provided the structure doesn’t change, queries on the data can be optimized by creating a structured XML index on the XMLType table.
With such an index in place, and depending on the size of the document, there could be a significant overhead at insert time, but it’s a trade-off : subsequent queries will be considerably faster.

Here’s a small test case based on the following document (a 50,000-row worksheet, no header) :

Document properties (I’ll define a virtual column to hold the title property) :


 

Set up and query plan :

-- Table creation : 
create table ext_smldata of xmltype
xmltype store as binary xml
virtual columns (
  title as (
    XMLCast(
      XMLQuery(
      'declare default element namespace "urn:schemas-microsoft-com:office:spreadsheet"; (::)
       declare namespace o = "urn:schemas-microsoft-com:office:office"; (::)
       /Workbook/o:DocumentProperties/o:Title'
      passing object_value returning content
      )
      as varchar2(200)
    )
  )
);

-- Index on the "TITLE" virtual column : 
create index ext_smldata_title_idx on ext_smldata (title);

-- Structured XML index on the table : 
create index ext_smldata_sxi on ext_smldata (object_value)
indextype is xdb.xmlindex
parameters (q'#
 XMLTable ext_smldata_xtb
   XMLNamespaces (default 'urn:schemas-microsoft-com:office:spreadsheet')
 , '/Workbook/Worksheet/Table/Row'
   COLUMNS rec_id      NUMBER       PATH 'Cell[1]/Data/text()'
         , description VARCHAR2(80) PATH 'Cell[2]/Data/text()'
         , rec_value   VARCHAR2(30) PATH 'Cell[3]/Data/text()'
#');

-- Insert : 
insert into ext_smldata values(
  xmltype(
    bfilename('XML_DIR','smldata.xml')
  , nls_charset_id('AL32UTF8')
  )
);

 

SQL> set timing on
SQL> set autotrace traceonly
SQL> SELECT x.*
  2  FROM ext_smldata t
  3     , XMLTable(
  4         XMLNamespaces (default 'urn:schemas-microsoft-com:office:spreadsheet')
  5       , '/Workbook/Worksheet/Table/Row'
  6         PASSING t.object_value
  7         COLUMNS rec_id      NUMBER       PATH 'Cell[1]/Data/text()'
  8               , description VARCHAR2(80) PATH 'Cell[2]/Data/text()'
  9               , rec_value   VARCHAR2(30) PATH 'Cell[3]/Data/text()'
 10       ) x
 11  WHERE t.title = 'SampleData1'
 12  ;

50000 rows selected.

Elapsed: 00:00:01.69

Execution Plan
----------------------------------------------------------
Plan hash value: 3987672269

------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       | 59520 |  5405K|   174   (2)| 00:00:03 |
|*  1 |  HASH JOIN                   |                       | 59520 |  5405K|   174   (2)| 00:00:03 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EXT_SMLDATA           |     1 |    29 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EXT_SMLDATA_TITLE_IDX |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL          | EXT_SMLDATA_XTB       | 59520 |  3720K|   171   (1)| 00:00:03 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."SYS_NC_OID$"="SYS_SXI_0"."OID")
   3 - access("T"."TITLE"='SampleData1')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3962  consistent gets
         77  physical reads
       4796  redo size
    2823321  bytes sent via SQL*Net to client
      37083  bytes received via SQL*Net from client
       3335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      50000  rows processed

The explain plan shows that the underlying relational table supporting the XML index is used to retrieve the data.

Advertisements
Categories: HowTo Tags: , , ,
  1. Babs
    March 5, 2012 at 14:36

    Hi odie
    I have an xml file which is stored in a table as xmltype from which i need an output like :-
    R_NO REQ STATUS_CODE
    123 525 JOB
    123 560 CNCLD

    123
    525
    JOB

    123
    560
    CNCLD


    My problem here the column names i cannot hardcode for example PASSING XML_VALUE
    columns R_NO VARCHAR2(20) PATH ‘R_NO’
    is not the solution i need to get my code has to traverse the xml and each node has to transform into a column , i cannot hardcode column name R_NO as above. runtime it has to identify, any idea?

    • March 5, 2012 at 20:20

      Hi,

      Sorry I’m not sure I understand.
      If you have an unknown number of resulting columns then you can’t do it with a single SELECT.
      I’d be glad to help though, but need more details. Could you post your issue in the XML DB forum?
      Make sure you provide :

      • your database version
      • a sample XML
      • the required output

      Thanks.

  2. Michael Reitsma
    June 28, 2012 at 16:31

    Hi Odie,

    How do you specifiy the index parameter when this index spec is longer than 1000 chars ?
    That would be the index ext_smldata_sxi in your example.
    In my case the spec is longer than 1000 chars and thus can not be created with parameters construct of your example.

    Hope you can help.

    Mike

    • June 28, 2012 at 17:55

      Hi Mike,

      You’ll have to do it in two steps : registering the parameter (we can use CLOB there), then creating the index by referencing the parameter.

      begin
        dbms_xmlindex.registerparameter(
          'ext_smldata_xtb_param'
        , 'XMLTable ext_smldata_xtb ... big text content here ... '
        );
      end;
      /
      
      create index ext_smldata_sxi on ext_smldata (object_value)
      indextype is xdb.xmlindex parameters ('PARAM ext_smldata_xtb_param')
      ;
      
  3. Jon D.
    July 28, 2012 at 19:48

    Odie,

    This artcile was soooo incredibly helpful to me. You have provided me with a fantastic solution to a major issue that has been hanging over my head for months. A million thanks!

    One question: Given than the spreadhsheet column-names are in the top row of the sheet (in the X1 table?), would it somehow be possible to read in the column-names rather than needing to list them (hard-coded) under the COLUMNS clause under X2?

    –Jon

    • July 30, 2012 at 21:33

      Hi Jon,

      No, it’s not possible with a static query, the SQL projection must be known at parse time.
      It can be done with dynamic SQL though, in two steps :
      1- Read the top row to find column names
      2- Build an SQL string using these names and access the data via a REF CURSOR (or DBMS_SQL package)

  4. Naveen
    December 7, 2012 at 05:44

    How to display multiple row within a single cell.
    For eg: In Cell “A1” i need to display
    1-2
    3-4
    (One below the other).
    I had tried with ,, CDATA, &#10,&#A,&#a. That is not working for me.
    Can anyone please help me in this issue ???

    • December 11, 2012 at 23:18

      This one is tricky.
      You have to replace LF characters with the corresponding entity, e.g.
      XMLElement("ITEM", replace(my_col, chr(10), '&#10;'))

      Then in the stylesheet, use disable-output-escaping attribute so that &#10; is preserved in the output :
      <xsl:value-of select="ITEM" disable-output-escaping="yes"/>

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s