Search code examples
flowgear

Flowgear SQL Query XML Result

I have a SQL query with a simple SELECT statement, FOR XML AUTO, ELEMENTS.

This is a sample text result in SQL Management Studio:

> XML_F52E2B61-18A1-11d1-B105-00805F49916B
> -------------------------------------------------------------------------------- <row><CompanyID>0</CompanyID><Company>0  
> </Company><GlCode>00-1010</GlCode><GlYear>2009</GlYear><BeginYearBalance>0.00</BeginYearBalance><ClosingBalPer1>50251640.00</ClosingBalPer1><ClosingBalPer2>45141985.00</ClosingBalPer2><ClosingBalPer3>43603485.00</C
> lPer7><ClosingBalPer8>0.00</ClosingBalPer8><ClosingBalPer9>0.00</ClosingBalPer9><ClosingBalPer10>0.00</ClosingBalPer10><ClosingBalPer11>0.00</ClosingBalPer11><ClosingBalPer12>0.00</ClosingBalPer12><ClosingBalPer13>0.00</ClosingBalPer13><ClosingBalPer14>0.0
> BalPer1><ClosingBalPer2>20000.00</ClosingBalPer2><ClosingBalPer3>0.00</ClosingBalPer3><ClosingBalPer4>0.00</ClosingBalPer4><ClosingBalPer5>0.00</ClosingBalPer5><ClosingBalPer6>0.00</ClosingBalPer6><ClosingBalPer7>0.00</ClosingBalPer7><ClosingBalPer8>0.00</
> alPer14>

It creates the familiar structure of <row> element for each record, with an element for each column.

However, this is what comes back when I run the node in Flowgear:

<Result>
  <Table>
    <XML_F52E2B61-18A1-11d1-B105-00805F49916B>&lt;row&gt;&lt;CompanyID&gt;0&lt;/CompanyID&gt;&lt;Company&gt;0   &lt;/Company&gt;&lt;GlCode&gt;00-1010&lt;/GlCode&gt;&lt;GlYear&gt;2009&lt;/GlYear&gt;&lt;BeginYearBalance&gt;0.00&lt;/BeginYearBalance&gt;&lt;ClosingBalPer1&gt;50251640.00&lt;/ClosingBalPer1&gt;&lt;ClosingBalPer2&gt;45141985.00&lt;/ClosingBalPer2&gt;&lt;ClosingBalPer3&gt;43603485.00&lt;/ClosingBalPer3&gt;&lt;ClosingBalPer4&gt;37602854.06&lt;/ClosingBalPer4&gt;&lt;ClosingBalPer5&gt;30477670.97&lt;/ClosingBalPer5&gt;&lt;ClosingBalPer6&gt;27131030.91&lt;/ClosingBalPer6&gt;&lt;ClosingBalPer7&gt;26489572.00&lt;/ClosingBalPer7&gt;&lt;ClosingBalPer8&gt;24508174.38&lt;/ClosingBalPer8&gt;&lt;ClosingBalPer9&gt;25107606.36&lt;/ClosingBalPer9&gt;&lt;ClosingBalPer10&gt;24838327.11&lt;/ClosingBalPer10&gt;&lt;ClosingBalPer11&gt;28456828.16&lt;/ClosingBalPer11&gt;&lt;ClosingBalPer12&gt;3205488.90&lt;/ClosingBalPer12&gt;&lt;ClosingBalPer13&gt;3205488.90&lt;/ClosingBalPer13&gt;&lt;ClosingBalPer14&gt;3205488.90&lt;/ClosingBalPer14&gt;&lt;ClosingBalPer15&gt;0.00&lt;/ClosingBalPer15&gt;&lt;/row&gt;&lt;row&gt;&lt;CompanyID&gt;0&lt;/CompanyID&gt;&lt;Company&gt;0   &lt;/Company&gt;&lt;GlCode&gt;00-1010&lt;/GlCode&gt;&lt;GlYear&gt;2010&lt;/GlYear&gt;&lt;BeginYearBalance&gt;3205488.90&lt;/BeginYearBalance&gt;&lt;ClosingBalPer1&gt;69904850.41&lt;/ClosingBalPer1&gt;&lt;ClosingBalPer2&gt;70035845.09&lt;/ClosingBalPer2&gt;&lt;ClosingBalPer3&gt;69751854.18&lt;/ClosingBalPer3&gt;&lt;ClosingBalPer4&gt;76699659.70&lt;/ClosingBalPer4&gt;&lt;ClosingBalPer5&gt;64868593.99&lt;/ClosingBalPer5&gt;&lt;ClosingBalPer6&gt;63343067.84&lt;/ClosingBalPer6&gt;&lt;ClosingBalPer7&gt;67160186.41&lt;/ClosingBalPer7&gt;&lt;ClosingBalPer8&gt;66030723.36&lt;/ClosingBalPer8&gt;&lt;ClosingBalPer9&gt;67336905.21&lt;/ClosingBalPer9&gt;&lt;ClosingBalPer10&gt;67363017.56&lt;/ClosingBalPer10&gt;&lt;ClosingBalPer11&gt;67275039.96&lt;/ClosingBalPer11&gt;&lt;ClosingBalPer12&gt;64310025.92&lt;/ClosingBalPer12&gt;&lt;ClosingBalPer13&gt;64310025.92&lt;/ClosingBalPer13&gt;&lt;ClosingBalPer14&gt;64310025.92&lt;/ClosingBalPer14&gt;&lt;ClosingBalPer15&gt;0.00&lt;/ClosingBalPer15&gt;&lt;/row&gt;&lt;row&gt;&lt;CompanyID&gt;0&lt;/CompanyID&gt;&lt;Company&gt;0   &lt;/Company&gt;&lt;GlCode&gt;00-1010&lt;/GlCode&gt;&lt;GlYear&gt;2011&lt;/GlYear&gt;&lt;BeginYearBalance&gt;64310025.92&lt;/BeginYearBalance&gt;&lt;ClosingBalPer1&gt;63333898.71&lt;/ClosingBalPer1&gt;&lt;ClosingBalPer2&gt;63334486.71&lt;/ClosingBalPer2&gt;&lt;ClosingBalPer3&gt;0.00&lt;/ClosingBalPer3&gt;&lt;ClosingBalPer4&gt;0.00&lt;/ClosingBalPer4&gt;&lt;ClosingBalPer5&gt;0.00&lt;/ClosingBalPer5&gt;&lt;ClosingBalPer6&gt;0.00&lt;/ClosingBalPer6&gt;&lt;ClosingBalPer7&gt;0.00&lt;/ClosingBa</XML_F52E2B61-18A1-11d1-B105-00805F49916B>
  </Table>
  <Table>
    <XML_F52E2B61-18A1-11d1-B105-00805F49916B>lPer7&gt;&lt;ClosingBalPer8&gt;0.00&lt;/ClosingBalPer8&gt;&lt;ClosingBalPer9&gt;0.00&lt;/ClosingBalPer9&gt;&lt;ClosingBalPer10&gt;0.00&lt;/ClosingBalPer10&gt;&lt;ClosingBalPer11&gt;0.00&lt;/ClosingBalPer11&gt;&lt;ClosingBalPer12&gt;0.00&lt;/ClosingBalPer12&gt;&lt;ClosingBalPer13&gt;0.00&lt;/ClosingBalPer13&gt;&lt;ClosingBalPer14&gt;0.00&lt;/ClosingBalPer14&gt;&lt;ClosingBalPer15&gt;0.00&lt;/ClosingBalPer15&gt;&lt;/row&gt;&lt;row&gt;&lt;CompanyID&gt;0&lt;/CompanyID&gt;&lt;Company&gt;0   &lt;/Company&gt;&lt;GlCode&gt;00-1020&lt;/GlCode&gt;&lt;GlYear&gt;2009&lt;/GlYear&gt;&lt;BeginYearBalance&gt;0.00&lt;/BeginYearBalance&gt;&lt;ClosingBalPer1&gt;20000.00&lt;/ClosingBalPer1&gt;&lt;ClosingBalPer2&gt;20000.00&lt;/ClosingBalPer2&gt;&lt;ClosingBalPer3&gt;20000.00&lt;/ClosingBalPer3&gt;&lt;ClosingBalPer4&gt;20000.00&lt;/ClosingBalPer4&gt;&lt;ClosingBalPer5&gt;20000.00&lt;/ClosingBalPer5&gt;&lt;ClosingBalPer6&gt;20000.00&lt;/ClosingBalPer6&gt;&lt;ClosingBalPer7&gt;20000.00&lt;/ClosingBalPer7&gt;&lt;ClosingBalPer8&gt;20000.00&lt;/ClosingBalPer8&gt;&lt;ClosingBalPer9&gt;20000.00&lt;/ClosingBalPer9&gt;&lt;ClosingBalPer10&gt;20000.00&lt;/ClosingBalPer10&gt;&lt;ClosingBalPer11&gt;20000.00&lt;/ClosingBalPer11&gt;&lt;ClosingBalPer12&gt;20000.00&lt;/ClosingBalPer12&gt;&lt;ClosingBalPer13&gt;20000.00&lt;/ClosingBalPer13&gt;&lt;ClosingBalPer14&gt;20000.00&lt;/ClosingBalPer14&gt;&lt;ClosingBalPer15&gt;0.00&lt;/ClosingBalPer15&gt;&lt;/row&gt;&lt;row&gt;&lt;CompanyID&gt;0&lt;/CompanyID&gt;&lt;Company&gt;0   &lt;/Company&gt;&lt;GlCode&gt;00-1020&lt;/GlCode&gt;&lt;GlYear&gt;2010&lt;/GlYear&gt;&lt;BeginYearBalance&gt;20000.00&lt;/BeginYearBalance&gt;&lt;ClosingBalPer1&gt;20000.00&lt;/ClosingBalPer1&gt;&lt;ClosingBalPer2&gt;20000.00&lt;/ClosingBalPer2&gt;&lt;ClosingBalPer3&gt;20000.00&lt;/ClosingBalPer3&gt;&lt;ClosingBalPer4&gt;20000.00&lt;/ClosingBalPer4&gt;&lt;ClosingBalPer5&gt;20000.00&lt;/ClosingBalPer5&gt;&lt;ClosingBalPer6&gt;20000.00&lt;/ClosingBalPer6&gt;&lt;ClosingBalPer7&gt;20000.00&lt;/ClosingBalPer7&gt;&lt;ClosingBalPer8&gt;20000.00&lt;/ClosingBalPer8&gt;&lt;ClosingBalPer9&gt;20000.00&lt;/ClosingBalPer9&gt;&lt;ClosingBalPer10&gt;20000.00&lt;/ClosingBalPer10&gt;&lt;ClosingBalPer11&gt;20000.00&lt;/ClosingBalPer11&gt;&lt;ClosingBalPer12&gt;20000.00&lt;/ClosingBalPer12&gt;&lt;ClosingBalPer13&gt;20000.00&lt;/ClosingBalPer13&gt;&lt;ClosingBalPer14&gt;20000.00&lt;/ClosingBalPer14&gt;&lt;ClosingBalPer15&gt;0.00&lt;/ClosingBalPer15&gt;&lt;/row&gt;&lt;row&gt;&lt;CompanyID&gt;0&lt;/CompanyID&gt;&lt;Company&gt;0   &lt;/Company&gt;&lt;GlCode&gt;00-1020&lt;/GlCode&gt;&lt;GlYear&gt;2011&lt;/GlYear&gt;&lt;BeginYearBalance&gt;20000.00&lt;/BeginYearBalance&gt;&lt;ClosingBalPer1&gt;20000.00&lt;/Closing</XML_F52E2B61-18A1-11d1-B105-00805F49916B>
  </Table>
  <Table>
    <XML_F52E2B61-18A1-11d1-B105-00805F49916B>BalPer1&gt;&lt;ClosingBalPer2&gt;20000.00&lt;/ClosingBalPer2&gt;&lt;ClosingBalPer3&gt;0.00&lt;/ClosingBalPer3&gt;&lt;ClosingBalPer4&gt;0.00&lt;/ClosingBalPer4&gt;&lt;ClosingBalPer5&gt;0.00&lt;/ClosingBalPer5&gt;&lt;ClosingBalPer6&gt;0.00&lt;/ClosingBalPer6&gt;&lt;ClosingBalPer7&gt;0.00&lt;/ClosingBalPer7&gt;&lt;ClosingBalPer8&gt;0.00&lt;/ClosingBalPer8&gt;&lt;ClosingBalPer9&gt;0.00&lt;/ClosingBalPer9&gt;&lt;ClosingBalPer10&gt;0.00&lt;/ClosingBalPer10&gt;&lt;ClosingBalPer11&gt;0.00&lt;/ClosingBalPer11&gt;&lt;ClosingBalPer12&gt;0.00&lt;/ClosingBalPer12&gt;&lt;ClosingBalPer13&gt;0.00&lt;/ClosingBalPer13&gt;&lt;ClosingBalPer14&gt;0.00&lt;/ClosingBalPer14&gt;&lt;ClosingBalPer15&gt;0.00&lt;/ClosingBalPer15&gt;&lt;/row&gt;&lt;row&gt;&lt;CompanyID&gt;0&lt;/CompanyID&gt;&lt;Company&gt;0   &lt;/Company&gt;&lt;GlCode&gt;00-1030&lt;/GlCode&gt;&lt;GlYear&gt;2009&lt;/GlYear&gt;&lt;BeginYearBalance&gt;0.00&lt;/BeginYearBalance&gt;&lt;ClosingBalPer1&gt;15000.00&lt;/ClosingBalPer1&gt;&lt;ClosingBalPer2&gt;30000.00&lt;/ClosingBalPer2&gt;&lt;ClosingBalPer3&gt;30000.00&lt;/ClosingBalPer3&gt;&lt;ClosingBalPer4&gt;30000.00&lt;/ClosingBalPer4&gt;&lt;ClosingBalPer5&gt;30000.00&lt;/ClosingBalPer5&gt;&lt;ClosingBalPer6&gt;30000.00&lt;/ClosingBalPer6&gt;&lt;ClosingBalPer7&gt;30000.00&lt;/ClosingBalPer7&gt;&lt;ClosingBalPer8&gt;30000.00&lt;/ClosingBalPer8&gt;&lt;ClosingBalPer9&gt;30000.00&lt;/ClosingBalPer9&gt;&lt;ClosingBalPer10&gt;30000.00&lt;/ClosingBalPer10&gt;&lt;ClosingBalPer11&gt;30000.00&lt;/ClosingBalPer11&gt;&lt;ClosingBalPer12&gt;30000.00&lt;/ClosingBalPer12&gt;&lt;ClosingBalPer13&gt;30000.00&lt;/ClosingBalPer13&gt;&lt;ClosingBalPer14&gt;30000.00&lt;/ClosingBalPer14&gt;&lt;ClosingBalPer15&gt;0.00&lt;/ClosingBalPer15&gt;&lt;/row&gt;&lt;row&gt;&lt;CompanyID&gt;0&lt;/CompanyID&gt;&lt;Company&gt;0   &lt;/Company&gt;&lt;GlCode&gt;00-1030&lt;/GlCode&gt;&lt;GlYear&gt;2010&lt;/GlYear&gt;&lt;BeginYearBalance&gt;30000.00&lt;/BeginYearBalance&gt;&lt;ClosingBalPer1&gt;30000.00&lt;/ClosingBalPer1&gt;&lt;ClosingBalPer2&gt;30000.00&lt;/ClosingBalPer2&gt;&lt;ClosingBalPer3&gt;30000.00&lt;/ClosingBalPer3&gt;&lt;ClosingBalPer4&gt;30000.00&lt;/ClosingBalPer4&gt;&lt;ClosingBalPer5&gt;30000.00&lt;/ClosingBalPer5&gt;&lt;ClosingBalPer6&gt;30000.00&lt;/ClosingBalPer6&gt;&lt;ClosingBalPer7&gt;30000.00&lt;/ClosingBalPer7&gt;&lt;ClosingBalPer8&gt;30000.00&lt;/ClosingBalPer8&gt;&lt;ClosingBalPer9&gt;30000.00&lt;/ClosingBalPer9&gt;&lt;ClosingBalPer10&gt;30000.00&lt;/ClosingBalPer10&gt;&lt;ClosingBalPer11&gt;30000.00&lt;/ClosingBalPer11&gt;&lt;ClosingBalPer12&gt;30000.00&lt;/ClosingBalPer12&gt;&lt;ClosingBalPer13&gt;30000.00&lt;/ClosingBalPer13&gt;&lt;ClosingBalPer14&gt;30000.00&lt;/ClosingB</XML_F52E2B61-18A1-11d1-B105-00805F49916B>
  </Table>

The Node is further splitting the result into what seems to be fixed-length <Table> elements. where the 2nd Table node contains the balance of text from the 1st Table element.

This results in VERY different XML, and I don't know how to deal with this, seemingly non-structured, XML in the next Flowgear Node (my XSLT transformation)

Please help...why is this happening and how can I make it stop?

Solution

  • When you are using an XML query, you need to tick the XmlQuery property on the Node.

    SQL returns a very different response for XML queries and Flowgear needs to be told to parse it accordingly.

    See http://developers.flowgear.net/kb/Node:SQL_Query for more details.