title: MYSQL UDF function to return XML
body: <h1>Situation:</h1>
  
  <p>I want to create a mysql function named XMLify, that takes in a string and an expression that will return a set</p>
  
  <p><code>XMLify(string, expr)</code></p>
  
  <p>The function should wrap each returned field of each returned row in the set, into its own XML tag. The name of the tag should be the field name.</p>
  
  <p>Small example:</p>
  
  <pre><code>select XMLify('foo', (SELECT 1 as `a`, 2 as `b` UNION SELECT 3 as `a`, 4 as `b`));
  </code></pre>
  
  <p>should return:</p>
  
  <pre><code>&lt;foo&gt;&lt;a&gt;1&lt;/a&gt;&lt;b&gt;2&lt;/b&gt;&lt;/foo&gt;&lt;foo&gt;&lt;a&gt;3&lt;/a&gt;&lt;b&gt;4&lt;/b&gt;&lt;/foo&gt;
  </code></pre>
  
  <p>I want to have this, because it will enable me to run a complex query with many joins and/or dependant subqueries, without having to return redundant data to the client.</p>
  
  <p>I already have a work-around without the function I want to build. But this involves writing difficult queries that are not easily maintained.
  See my examples below.</p>
  
  <p>Making sure the field names are legal XML node name is for a later worry. Once the function stands, I will think of some algorithm that will take the field name and turn it into some legal XML node name.</p>
  
  <p>Also escaping the XML data is for a later worry. This will be done with a different function named <code>CDATAify</code>, that will simply wrap all data into <code>&lt;![CDATA[</code> and <code>]]&gt;</code>, and will escape any prior occurance of <code>]]&gt;</code> in the data into <code>]]]]&gt;&lt;![CDATA[&gt;</code>.</p>
  
  <p>I haven't been able to accomplish this using stored functions in MySQL, because these do not take in resultsets. Also, even if you were to pass in SQL as a string, and then prepare statement and execute it, you cannot access the fields if you don't already know the field names.</p>
  
  <p>So now I am wondering if the trick can be done with user defined functions (UDF). This is something I haven't yet worked with, and I would like your advise here before enbarqueing.</p>
  
  <h1>QUESTIONS:</h1>
  
  <p>So my questions now are:</p>
  
  <ul>
  <li>To recap, I would like to have a MySQL function that I can pass an expression or result set, and where I can also use the field names of the result set.</li>
  <li>Am I assuming correct that this will not be possible in stored functions?</li>
  <li>Will UDF take in expessions / their result set as an argument?</li>
  <li>Will UDF allow me to to access field names of the result set, so I can use them as the XML tag names</li>
  <li>Will it work on Windows as well? I read that UDF have some limitations</li>
  <li>Is there a better way I haven't thought of yet?</li>
  <li>Will I be able to have a UDF .dll that I can create on my own development computer and then copy the .dll file to my server and use it there?</li>
  <li>How do I get this show on a roll? Please be thorough and take into account that I have MySQL 5.5 64-bit on a Windows computer.</li>
  </ul>
  
  <h1>EXAMPLE:</h1>
  
  <p>Imagine having the following 3 tables:</p>
  
  <pre><code>users:         grades:             toys:
  +----+------+  +--------+-------+  +--------+--------------+
  | id | name |  | userid | grade |  | userid | toy          |
  +----+------+  +--------+-------+  +--------+--------------+
  |  1 | Bart |  |      1 |     E |  |      1 | slingshot    |
  |  2 | Lisa |  |      1 |     E |  |      1 | Krusty       |
  | .. | ...  |  |      2 |     A |  |      2 | Malibu Stacy |  
  | .. | ...  |  |      2 |     B |  |      2 | calculator   |
  +----+------+  +--------+-------+  +--------+--------------+
  </code></pre>
  
  <p>My desired result would be, limited to Bart and Lisa:</p>
  
  <pre><code>&lt;users&gt;
      &lt;user&gt;
          &lt;id&gt;&lt;![CDATA[1]]&gt;&lt;/id&gt;
          &lt;name&gt;&lt;![CDATA[Bart]]&gt;&lt;/name&gt;
          &lt;grades&gt;
              &lt;grade&gt;&lt;![CDATA[E]]&gt;&lt;/grade&gt;
              &lt;grade&gt;&lt;![CDATA[E]]&gt;&lt;/grade&gt;
          &lt;/grades&gt;
          &lt;toys&gt;
              &lt;toy&gt;&lt;![CDATA[slingshot]]&gt;&lt;/toy&gt;
              &lt;toy&gt;&lt;![CDATA[Krusty]]&gt;&lt;/toy&gt;
          &lt;/toys&gt;
      &lt;/user&gt;
      &lt;user&gt;
          &lt;id&gt;&lt;![CDATA[1]]&gt;&lt;/id&gt;
          &lt;name&gt;&lt;![CDATA[Lisa]]&gt;&lt;/name&gt;
          &lt;grades&gt;
              &lt;grade&gt;&lt;![CDATA[A]]&gt;&lt;/grade&gt;
              &lt;grade&gt;&lt;![CDATA[B]]&gt;&lt;/grade&gt;
          &lt;/grades&gt;
          &lt;toys&gt;
              &lt;toy&gt;&lt;![CDATA[Malibu Stacey]]&gt;&lt;/toy&gt;
              &lt;toy&gt;&lt;![CDATA[calculator]]&gt;&lt;/toy&gt;
          &lt;/toys&gt;
      &lt;/user&gt;
  &lt;/users&gt;
  </code></pre>
  
  <p>Consideration:</p>
  
  <ul>
  <li>I don't want in PHP or C# to have to first query the user table, and then per user run two additional queries for the grades and toys. Because for 1000 users, I would be be running 2001 queries.</li>
  <li>I also don't want to run a query with all joins and go through the result set in PHP or C#, because the user name would be sent as many times as the number of grades times the number of toys. Imagine having a user field containing a huge blob!</li>
  <li>I cannot simply use GROUP_CONCAT on joined tables, as the grades/toys would still appear double.</li>
  <li>And if I would use GROUP_CONCAT with DISTINCT, I will lose the grades with are the same, such as Bart's two E's.</li>
  </ul>
  
  <p>So currently I would use the following statement to get this result, involving two dependant subqueries. This works great:</p>
  
  <pre><code>SELECT
      CONCAT(
          '&lt;users&gt;',
              IFNULL(
                  GROUP_CONCAT(
                      '&lt;user&gt;',
                          '&lt;id&gt;&lt;![CDATA[',
                              REPLACE(u.id,']]&gt;',']]]]&gt;&lt;![CDATA[&gt;'),
                          ']]&gt;&lt;/id&gt;',
                          '&lt;name&gt;&lt;![CDATA[',
                              REPLACE(u.name,']]&gt;',']]]]&gt;&lt;![CDATA[&gt;'),
                          ']]&gt;&lt;/name&gt;',
                          '&lt;grades&gt;',
                              (
                                  SELECT
                                      IFNULL(
                                          GROUP_CONCAT(
                                              '&lt;grade&gt;&lt;![CDATA[',
                                                  REPLACE(g.grade,']]&gt;',']]]]&gt;&lt;![CDATA[&gt;'),
                                              ']]&gt;&lt;/grade&gt;'
                                              SEPARATOR ''
                                          ),
                                      '')
                                  FROM
                                      grades g
                                  WHERE
                                      g.userid = u.id
                              ),
                          '&lt;/grades&gt;',
                          '&lt;toys&gt;',
                              (
                                  SELECT
                                      IFNULL(
                                          GROUP_CONCAT(
                                              '&lt;toys&gt;&lt;![CDATA[',
                                                  REPLACE(t.toy,']]&gt;',']]]]&gt;&lt;![CDATA[&gt;'),
                                              ']]&gt;&lt;/toys&gt;'
                                              SEPARATOR ''
                                          ),
                                      '')
                                  FROM
                                      toys t
                                  WHERE
                                      t.userid = u.id
                              ),
                          '&lt;/toys&gt;',
                      '&lt;/user&gt;'
                      SEPARATOR ''
                  ),
                  ''
              ),
          '&lt;/users&gt;'
      )
  FROM
      users u
  WHERE
      u.name = 'Bart' or u.name = 'Lisa'
  ;
  </code></pre>
  
  <p>Now as you might notice, it is a rather big and ugly query, which hurts the eyes when reading.
  Maintaining such a query is hard.
  If I would have my functions XMLify and CDATAify, I could simply write this instead:</p>
  
  <pre><code>SELECT
      XMLify('users',(
          XMLify('user',(
              SELECT
                  CDATAify(u.id) as id,
                  CDATAify(u.name) as name,
                  XMLify('grade',(
                      SELECT
                          CDATAify(g.grade) as grade
                      FROM
                          grades g
                      where
                          g.userid = u.id
                  )) AS grades,
                  XMLify('toys',(
                      SELECT
                          CDATAify(t.toy) as toy
                      FROM
                          toys t
                      where
                          t.userid = u.id
                  )) AS grades
              FROM
                  users u
              WHERE
                  u.name = 'Bart' or u.name = 'Lisa'
          ))
      ))
  ;
  </code></pre>
  
  <h1>EDIT:</h1>
  
  <p>As mentioned in the comments by N.B., there is a <a href="https://github.com/mysqludf/lib_mysqludf_xml#readme">repository on Github</a>, possibly holding all I need. I have however spent several days now just trying to get this to work on my system, without success. Any answer that holds a step-by-step how-to on how to install this on my MySQL 5.5 64-bit server running on Windows is also acceptable.</p>
  
  <p>Please take into account that I have no experience with makes, makefiles, etc. So please be thorough in explaining.</p>