Step by step guide to create XML based report

Posted by Anantha Narayanan on Thursday, February 7, 2008 / Labels: ,

I have never tried this kind of reporting before. Mail from a friend prompted me that I should start creating this report and also publish an article which shows a step-by-step process of creating one.

 

So here is the list that we are going to achieve:

 

  1. Create a DTD file
  2. Create an XML file
  3. Create a report based on the two files created.

 

First step is to create a DTD file.

 

Our XML file contains details of all database environments in use by a team.

 

The following is our DTD file. Save the following contents as c:\database.dtd:

 

<?xml version="1.0" encoding="UTF-8"?>

<!ELEMENT DatabaseInventory (DatabaseName+)>

<!ELEMENT DatabaseName (   GlobalDatabaseName

                         , OracleSID

                         , DatabaseDomain

                         , Administrator+

                         , DatabaseAttributes

                         , Comments)

> 

<!ELEMENT GlobalDatabaseName (#PCDATA)>

<!ELEMENT OracleSID          (#PCDATA)>

<!ELEMENT DatabaseDomain     (#PCDATA)>

<!ELEMENT Administrator      (#PCDATA)>

<!ELEMENT DatabaseAttributes EMPTY>

<!ELEMENT Comments           (#PCDATA)>

 

<!ATTLIST Administrator       EmailAlias CDATA #REQUIRED>

<!ATTLIST Administrator       Extension  CDATA #IMPLIED>

<!ATTLIST DatabaseAttributes  Type       (Production|Development|Testing) #REQUIRED>

<!ATTLIST DatabaseAttributes  Version    (7|8|8i|9i) "9i">

 

<!ENTITY AUTHOR "Anantha Narayanan">

<!ENTITY WEB    "http://askanantha.blogspot.com">

 

The contents of the structure is as follows:

  1. DatabaseInventory (Row of type DatabaseName)
  2. DatabaseName is a type of fields (GlobalDatabaseName, OracleSID, DatabaseDomain, Administrator+, DatabaseAttributes, Comments)
  3. GlobalDatabaseName is a field of type PCDATA
  4. OracleSID is a field of type PCDATA
  5. DatabaseDomain is a field of type PCDATA
  6. Administrator is a field of type PCDATA
  7. DatabaseAttributes is EMPTY by default
  8. Comments is a field of type PCDATA

 

In the ATTLIST tag, we are defining the properties of the fields of type PCDATA.

 

Now the second step is to create XML file as below. Save the following contents as database.xml:

<?xml version="1.0"?>

<!DOCTYPE DatabaseInventory SYSTEM "db.dtd">

 

<DatabaseInventory>

 

  <DatabaseName>

    <GlobalDatabaseName>mydb.myspace.com</GlobalDatabaseName>

    <OracleSID>production</OracleSID>

    <DatabaseDomain>myspace.com</DatabaseDomain>

    <Administrator EmailAlias="tlananthu" Extension="2354">Anantha</Administrator>

    <DatabaseAttributes Type="Production" Version="10g"/>

    <Comments>

      This is production database.

    </Comments>

  </DatabaseName>

 

  <DatabaseName>

    <GlobalDatabaseName>prod.myspace.com</GlobalDatabaseName>

    <OracleSID>development</OracleSID>

    <DatabaseDomain>myspace.com</DatabaseDomain>

    <Administrator EmailAlias="tlananthu" Extension="2354">Anantha</Administrator>

    <DatabaseAttributes Type="Production" Version="10g"/>

    <Comments>

      This is development database.

    </Comments>

  </DatabaseName>

 

  <DatabaseName>

    <GlobalDatabaseName>test.myspace.com</GlobalDatabaseName>

    <OracleSID>testing</OracleSID>

    <DatabaseDomain>myspace.com</DatabaseDomain>

    <Administrator EmailAlias="tlananthu" Extension="2354">Anantha</Administrator>

    <DatabaseAttributes Type="Production" Version="10g"/>

    <Comments>

      This is testing database.

    </Comments>

  </DatabaseName>

 

</DatabaseInventory>

 

Now start Report Builder.

 

  1. Create a New report using the wizard
  2. Click next on Welcome wizard
  3. Select Create both Web and Paper Layout option and click Next
  4. Choose a Tabular report
  5. Choose XML Query
  6. In the Query Source definition choose Query Definition button
  7. In the Define XML Query choose the database.dtd and choose select columns button
    1. Choose all columns
  8. In the Data source field choose the database.xml file and click OK
  9. Click next
  10. Choose all fields and click next
  11. Click next
  12. Edit the labels if necessary.
  13. Click next
  14. Choose a template if necessary
  15. Click Finish.

 

 

Congratulations, you have created a report based on an XML query successfully.