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:
C# Program SQL Query XML Test
The XML file that it created looks like this:
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.
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.
Generated XML Test 2
Very clean and simple. This is data that will be simple to work with when displaying to the user.