Erick's Blog
  • Home
  • Blog
  • Apps
    • Mortgage Calculator
  • PowerShell
    • Blog & Examples
    • Excel Reference
  • Windchill
    • Document Exporter
    • Document Worker
    • E.P.L.E.S.
    • Export Released
    • Property Checker
    • Windchill Business Analytics >
      • Part 01 - Program Logic
      • Part 02 - Getting Data with SQL Queries
      • Part 03 - Automating SQL Queries
      • Part 04 - Converting SQL Results to XML
      • Part 05 - Data Processing and Manipulation
      • Part 06 - Displaying XML in HTML
      • Part 07 - Auto Updating Displayed Data
      • Part 08 - Hosting Webpage with an Existing Apache Installation
      • Part 09 - Running Multiple Queries In Sequence
      • Part 10 - Calculating Data Trends
      • Part 11 - Making It Modular
    • Windchill Quick View
  • Reviews
  • Music
  • Contact

Windchill Business Analytics: Programming Journal


Part 04: Converting SQL Results to XML

Last Updated: August 14, 2016
I have data and I want to write that data to a file that is both human readable (for debugging purposes) and JavaScript readable.  XML is an old and familiar file format for me that is supported just about everywhere.  Let's begin by updating the code to create an XML file from the above results.

    
I am assuming that an XML file does not currently exists and I need to generate one from scratch.  I create my root node and then append the data to that.  You can see that I also decided to use the label text as a status check.  This will be useful in letting me know when the XML is finished being created.  Running the program again gives me:
Picture
C# Program SQL Query XML Test
The XML file that it created looks like this:
Picture
Generated XML Test 1
This is exactly the kind of format I was looking for.  Now it is just a matter of extending the code to include the monthly data from the initial query.

    
Take a look at a few things that needed to be changed for the query to work.
1.  I had to replace all " with "".  Otherwise, you get a error from Oracle.  Do NOT replace " with '.  That will not work.
2.  I had to remove the space between Object Type.  I chose to replace it with _ to make it easy to read.
​3.  In the while loop, I used the colNames variable to get the custom column names I created so I can make nodes from them.  Thanks to the stackoverflow people for the simple example.
​4.  I moved the root node creation out of the while loop as I am now working with more than one row in my results and did not want it to overwrite any data.

Running the code to include all months and all three object types.  I now get the following XML document.
Picture
Generated XML Test 2
Very clean and simple.  This is data that will be simple to work with when displaying to the user.

Previous: Automating SQL Queries
W.B.A. â€‹Home
Next: Data Processing and Manipulation
Copyright © 2018 Erick Johnson
  • Home
  • Blog
  • Apps
    • Mortgage Calculator
  • PowerShell
    • Blog & Examples
    • Excel Reference
  • Windchill
    • Document Exporter
    • Document Worker
    • E.P.L.E.S.
    • Export Released
    • Property Checker
    • Windchill Business Analytics >
      • Part 01 - Program Logic
      • Part 02 - Getting Data with SQL Queries
      • Part 03 - Automating SQL Queries
      • Part 04 - Converting SQL Results to XML
      • Part 05 - Data Processing and Manipulation
      • Part 06 - Displaying XML in HTML
      • Part 07 - Auto Updating Displayed Data
      • Part 08 - Hosting Webpage with an Existing Apache Installation
      • Part 09 - Running Multiple Queries In Sequence
      • Part 10 - Calculating Data Trends
      • Part 11 - Making It Modular
    • Windchill Quick View
  • Reviews
  • Music
  • Contact